範圍函數

使用範圍函數，您可以更有效地建立分析業務查詢。範圍函數對結果集的一個分割區或「視窗」執行運算，然後針對該視窗中的每一列傳回一個值。反之，非視窗函數對結果集的每一列執行計算。不同於彙總結果列的群組函數，範圍函數會保留運算式中的所有列。

傳回的值是利用該視窗中列集的值來計算。對於資料表的每一列，視窗會定義用於計算其他屬性的列集。使用視窗規格（OVER子句）定義視窗，並以三個主要概念為基礎：

視窗分割，形成資料列群組（PARTITION 子句）
視窗排序 ，定義每個分割區內的資料列順序或序列（ORDER BY 子句）
相對於每一列定義的視窗影格 ，以進一步限制資料列集（ROWS 規格）

除最終 ORDER BY 子句外，視窗函數是查詢中執行的最後一組操作。所有聯結和所有 WHERE、GROUPBY 和HAVING子句都會在處理視窗函數之前完成。因此，視窗函數只能出現在選取清單或 ORDER BY 子句中。您可以在具有不同窗框子句的單一查詢中使用多個範圍函數。您也可以在其他純量表達式中使用視窗函數，例如 CASE。

無法巢狀視窗函數。例如，彙總函數SUM可以出現在視窗函數內SUM，但視窗函數SUM無法出現在另一個視窗函數內SUM。由於視窗函數巢狀在另一個視窗函數中，因此不支援下列項目。


SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

範圍函數語法摘要

範圍函數遵循標準語法，如下所示。


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 BY 中的順序規格排序的每個分割區中的資料列。此 ORDER BY 子句與 frame_clause 中的 ORDER BY 子句不同且完全無關。ORDER BY 子句可以在沒有 PARTITION BY 子句的情況下使用。

對於排名函數，ORDERBY 子句會識別排名值的量值。對於彙總函數，在為每一個窗框計算彙總函數之前，分割的列必須排序。如需範圍函數的詳細資訊，請參閱範圍函數。

順序清單中需要欄識別碼或可評估為欄識別碼的欄表達式。常數或常數表達式都不能用來替代欄名。

NULLS 值會視為自己的群組，並根據 NULLSFIRST或 NULLSLAST選項進行排序和排序。根據預設，NULL值會ASC依順序排序和排序，並依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是否應該先排序、在非空值之前排序，還是在非空值之後最後排序的選項。根據預設， NULLS 會ASC按順序排序和排序最後，並按DESC順序排序和排序。

frame_clause

對於彙總函數，當使用 ORDER BY 時，影格子句會進一步精簡函數視窗中的資料列集。它可讓您在排序的結果內包含或排除資料列組。框架子句由ROWS關鍵字和相關聯的規格符組成。

窗框子句不適用於排名函數。此外，當彙總函數的子句中未使用 ORDER BY 子句時，就不需要影格子OVER句。如果 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 表示該視窗從分割區的第一列開始；偏移PRECEDING表示該視窗在目前資料列之前啟動了相當於偏移值的數個資料列。UNBOUNDED PRECEDING 為預設值。

CURRENT ROW 表示視窗在目前資料列開始或結束。

UNBOUNDED FOLLOWING 表示該視窗結束於分割區的最後一列；偏移FOLLOWING表示該視窗結束的列數相當於目前列之後的偏移值。

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 支援兩種範圍函數：彙總和排名。

以下是支援的彙總函數：

AVG 視窗函數
COUNT 視窗函數
CUME_DIST 視窗函數
DENSE_RANK 視窗函數
FIRST_VALUE 視窗函數
LAG 視窗函數
LAST_VALUE 視窗函數
LEAD 視窗函數
LISTAGG 視窗函數
MAX 視窗函數
MEDIAN 視窗函數
MIN 視窗函數
NTH_VALUE 視窗函數
PERCENTILE_CONT 視窗函數
PERCENTILE_DISC 視窗函數
RATIO_TO_REPORT 視窗函數
STDDEV_SAMP 和 STDDEV_POP 視窗函數（STDDEV_SAMP 和 STDDEV是同義詞）
SUM 視窗函數
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);

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

TO_VARBYTE

AVG