本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
範圍函數
使用範圍函數，您可以更有效地建立分析業務查詢。範圍函數對結果集的一個分割區或「視窗」執行運算，然後針對該視窗中的每一列傳回一個值。反之，非視窗函數對結果集的每一列執行計算。不同於彙總結果列的群組函數，範圍函數會保留運算式中的所有列。
傳回的值是利用該視窗中列集的值來計算。對於資料表的每一列，視窗會定義用於計算其他屬性的列集。視窗是以視窗規格 (OVER 子句) 並根據三個主要概念來定義：
-
視窗分割，其會形成列群組 (PARTITION 子句)
-
視窗排序，定義每一個分割區內列的順序或序列 (ORDER BY 子句)
-
視窗框，相對於每一列來定義，以進一步限制列組 (ROWS 規格)
範圍函數是查詢中最後執行的一組運算 (最後的 ORDER BY 子句除外)。所有聯結和所有 WHERE、GROUP BY 及 HAVING 子句都在範圍函數處理之前完成。因此，範圍函數只能出現在 select 清單或 ORDER BY 子句中。您可以在具有不同窗框子句的單一查詢中使用多個範圍函數。您也可以在其他純量運算式 (例如 CASE) 中使用範圍函數。
範圍函數語法摘要
範圍函數遵循標準語法，如下所示。
function (expression) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list [ frame_clause ] ] )
其中，function 是本節所述其中一個函數。
expr_list 如下。
expression | column_name [, expr_list ]
order_list 如下。
expression | column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, order_list ]
frame_clause 如下。
ROWS { UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } | { BETWEEN { UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW} AND { UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}
引數
- 函數
-
範圍函數。如需詳細資訊，請參閱個別函數描述。
- OVER
-
此子句定義視窗規格。OVER 是範圍函數的必要子句，用於區分範圍函數和其他 SQL 函數。
- PARTITION BY expr_list
-
(選用) PARTITION BY 子句將結果集細分為分割區，很像 GROUP BY 子句。如果有分割區子句，則會對每一個分割區的列來計算函數。如果未指定分割區子句，則單一分割區包含整個資料表，且會針對這整個資料表來計算函數。
排名函數 DENSE_RANK、NTILE、RANK 及 ROW_NUMBER 需要整體比較結果集的所有列。使用 PARTITION BY 子句時，查詢最佳化工具可以根據分割區將工作負載分散至多個配量，以平行執行每一個彙總。如果沒有 PARTITION BY 子句，則必須在單一配量上循序執行彙總步驟，這可能對效能造成嚴重的負面影響，尤其對於大型叢集。
Amazon Redshift 不支援 PARTITION BY 子句中的字串常值。
- ORDER BY order_list
-
(選用) 範圍函數會套用至每一個分割區內根據 ORDER BY 中的順序規格所排序的列。此 ORDER BY 子句不同於且完全無關於 frame_clause 中的 ORDER BY 子句。使用 ORDER BY 子句可以不搭配 PARTITION BY 子句。
對於排名函數，ORDER BY 子句可辨識排名值的量值。對於彙總函數，在為每一個窗框計算彙總函數之前，分割的列必須排序。如需範圍函數的詳細資訊，請參閱範圍函數。
順序清單中需要欄識別碼或可評估為欄識別碼的欄表達式。常數或常數表達式都不能用來替代欄名。
NULLS 值自成一組，根據 NULLS FIRST 或 NULLS LAST 選項來排序和排名。根據預設，依 ASC 順序排序時，NULL 值排在最後面，而依 DESC 順序排序時，則排在最前面。
Amazon Redshift 不支援 ORDER BY 子句中的字串常值。
如果省略 ORDER BY 子句，則列的順序不確定。
注意
在任何平行系統中，例如 Amazon Redshift，當 ORDER BY 子句無法產生唯一且完全的資料排序時，列的順序不確定。也就是說，如果 ORDER BY 運算式產生重複值 (局部排序)，則那些列的傳回順序可能隨著每一次執行 Amazon Redshift 而有所不同。於是，範圍函數可能傳回非預期或不一致的結果。如需詳細資訊，請參閱 範圍函數的資料唯一排序。
- column_name
-
分割或排序所依據的欄名。
- ASC | DESC
-
此選項會定義表達式的排序順序，如下所示：
-
ASC：遞增 (例如，數值從低到高，字元字串 'A' 到 'Z')。若未指定選項，資料會預設為遞增排序。
-
DESC：遞減 (數值從高到低，字串 'Z' 到 'A')。
-
- NULLS FIRST | NULLS LAST
-
這些選項指定 NULLS 應該排序在最前 (在非 Null 值之前) 或排序在最後 (在非 Null 值之後)。根據預設，NULLS 在 ASC 排序中排序和排名最後，而在 DESC 排序中排序和排名最前。
- frame_clause
-
對於彙總函數，使用 ORDER BY 時，窗框子句會進一步調整函數視窗中的一個列集。它可讓您在排序的結果內包含或排除資料列組。窗框子句包含 ROWS 關鍵字和相關的指定元。
窗框子句不適用於排名函數。此外，當彙總函數的 OVER 子句中未使用 ORDER BY 子句時，不需要使用窗框子句。如果彙總函數使用 ORDER BY 子句，則需要明確的窗框子句。
未指定 ORDER BY 子句時，隱含的窗框無邊界：相當於 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。
- ROWS
-
此子句定義視窗框作法是指定相對於目前列的實體位移。
此子句指定目前視窗或分割區中的列，以便與目前列的值結合。此子句使用引數來指定列位置，可能在目前列之前或之後。所有視窗框都以目前列為參考點。隨著視窗框在分割區中向前滑動，每一列會輪流變成目前列。
窗框可能是一組簡單的列，最遠到達且包含目前列。
{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}也可能是兩個邊界之間的一個列集。
BETWEEN { UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW } AND { UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }UNBOUNDED PRECEDING 表示視窗從分割區的第一列開始；offset PRECEDING 表示視窗從目前列之前相當於 offset 值的列數開始。UNBOUNDED PRECEDING 是預設值。
CURRENT ROW 表示視窗在目前列開始或結束。
UNBOUNDED FOLLOWING 表示視窗在分割區的最後一列結束；offset FOLLOWING 表示視窗在目前列之後相當於 offset 值的列數結束。
offset 表示目前列之前或之後的實體列數。在此案例中，offset 必須是評估為正數值的常數。例如，5 FOLLOWING 會在目前列之後的 5 列結束窗框。
未指定 BETWEEN 時，窗框會隱含地以目前列為邊界。例如，
ROWS 5 PRECEDING等於ROWS BETWEEN 5 PRECEDING AND CURRENT ROW。此外，ROWS UNBOUNDED FOLLOWING等於ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING。注意
您不能指定開始邊界大於結束邊界的窗框。例如，您不能指定下列任何窗框：
between 5 following and 5 preceding between current row and 2 preceding between 3 following and current row
範圍函數的資料唯一排序
如果範圍函數的 ORDER BY 子句無法產生唯一且完全的資料排序時，列的順序不確定。如果 ORDER BY 運算式產生重複值 (局部排序)，則這些行的傳回順序在多次執行中可能會有所不同。在這種情況下，範圍函數也可能傳回非預期或不一致的結果。
例如，以下查詢會在多次執行中傳回不同的結果。發生這些不同的結果是因為 order by dateid 不會為 SUM 範圍函數產生唯一的資料排序。
select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 1730.00 | 1730.00 1827 | 708.00 | 2438.00 1827 | 234.00 | 2672.00 ... select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 234.00 | 234.00 1827 | 472.00 | 706.00 1827 | 347.00 | 1053.00 ...
在此情況下，將第二個 ORDER BY 欄新增至範圍函數可能會解決問題。
select dateid, pricepaid, sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+--------- 1827 | 234.00 | 234.00 1827 | 337.00 | 571.00 1827 | 347.00 | 918.00 ...
支援的函數
Amazon Redshift 支援兩種範圍函數：彙總和排名。
以下是支援的彙總函數：
-
STDDEV_SAMP 和 STDDEV_POP 範圍函數 (STDDEV_SAMP 和 STDDEV 是同義詞)
-
VAR_SAMP 和 VAR_POP 範圍函數 (VAR_SAMP 和 VARIANCE 是同義詞)
以下是支援的排名函數：
範圍函數範例的範例資料表
您可以在每個函數說明中找到特定的範圍函數範例。部分範例會使用名為 WINSALES 的資料表，其中包含 11 個資料列，如下所示。
| SALESID | DATEID | SELLERID | BUYERID | QTY | QTY_SHIPPED |
|---|---|---|---|---|---|
| 30001 | 8/2/2003 | 3 | B | 10 | 10 |
| 10001 | 12/24/2003 | 1 | C | 10 | 10 |
| 10005 | 12/24/2003 | 1 | A | 30 | |
| 40001 | 1/9/2004 | 4 | A | 40 | |
| 10006 | 1/18/2004 | 1 | C | 10 | |
| 20001 | 2/12/2004 | 2 | B | 20 | 20 |
| 40005 | 2/12/2004 | 4 | A | 10 | 10 |
| 20002 | 2/16/2004 | 2 | C | 20 | 20 |
| 30003 | 4/18/2004 | 3 | B | 15 | |
| 30004 | 4/18/2004 | 3 | B | 20 | |
| 30007 | 9/7/2004 | 3 | C | 30 |
下列指令碼建立並填入範例 WINSALES 資料表。
CREATE TABLE winsales( salesid int, dateid date, sellerid int, buyerid char(10), qty int, qty_shipped int); INSERT INTO winsales VALUES (30001, '8/2/2003', 3, 'b', 10, 10), (10001, '12/24/2003', 1, 'c', 10, 10), (10005, '12/24/2003', 1, 'a', 30, null), (40001, '1/9/2004', 4, 'a', 40, null), (10006, '1/18/2004', 1, 'c', 10, null), (20001, '2/12/2004', 2, 'b', 20, 20), (40005, '2/12/2004', 4, 'a', 10, 10), (20002, '2/16/2004', 2, 'c', 20, 20), (30003, '4/18/2004', 3, 'b', 15, null), (30004, '4/18/2004', 3, 'b', 20, null), (30007, '9/7/2004', 3, 'c', 30, null);
