本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 範圍函數 使用範圍函數，您可以更有效地建立分析業務查詢。範圍函數對結果集的一個分割區或「視窗」執行運算，然後針對該視窗中的每一列傳回一個值。反之，非視窗函數對結果集的每一列執行計算。不同於彙總結果列的群組函數，範圍函數會保留運算式中的所有列。 傳回的值是利用該視窗中列集的值來計算。對於資料表的每一列，視窗會定義用於計算其他屬性的列集。視窗是以視窗規格 (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\$1list* 如下。 ``` expression | column_name [, expr_list ] ``` *order\$1list* 如下。 ``` expression | column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, order_list ] ``` *frame\$1clause* 如下。 ``` 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\$1list* (選用) PARTITION BY 子句將結果集細分為分割區，很像 GROUP BY 子句。如果有分割區子句，則會對每一個分割區的列來計算函數。如果未指定分割區子句，則單一分割區包含整個資料表，且會針對這整個資料表來計算函數。 排名函數 DENSE\$1RANK、NTILE、RANK 及 ROW\$1NUMBER 需要整體比較結果集的所有列。使用 PARTITION BY 子句時，查詢最佳化工具可以根據分割區將工作負載分散至多個配量，以平行執行每一個彙總。如果沒有 PARTITION BY 子句，則必須在單一配量上循序執行彙總步驟，這可能對效能造成嚴重的負面影響，尤其對於大型叢集。 AWS Clean Rooms不支援 PARTITION BY 子句中的字串常值。 ORDER BY *order\$1list* (選用) 範圍函數會套用至每一個分割區內根據 ORDER BY 中的順序規格所排序的列。此 ORDER BY 子句不同於且完全無關於 *frame\$1clause* 中的 ORDER BY 子句。使用 ORDER BY 子句可以不搭配 PARTITION BY 子句。 對於排名函數，ORDER BY 子句可辨識排名值的量值。對於彙總函數，在為每一個窗框計算彙總函數之前，分割的列必須排序。如需範圍函數的詳細資訊，請參閱[範圍函數](#Window_functions)。 順序清單中需要欄識別碼或可評估為欄識別碼的欄表達式。常數或常數表達式都不能用來替代欄名。 NULLS 值自成一組，根據 NULLS FIRST 或 NULLS LAST 選項來排序和排名。根據預設，依 ASC 順序排序時，NULL 值排在最後面，而依 DESC 順序排序時，則排在最前面。 AWS Clean Rooms不支援 ORDER BY 子句中的字串常值。 如果省略 ORDER BY 子句，則列的順序不確定。 在任何平行系統中AWS Clean Rooms，例如，當 ORDER BY 子句未產生資料的唯一和總排序時，資料列的順序是非確定性的。也就是說，如果 ORDER BY 表達式產生重複的值 （部分排序），則這些資料列的傳回順序可能會因執行 而有所不同AWS Clean Rooms。於是，範圍函數可能傳回非預期或不一致的結果。如需詳細資訊，請參閱[範圍函數的資料唯一排序](#Examples_order_by_WF)。 *column\$1name* 分割或排序所依據的欄名。 ASC \$1 DESC 此選項會定義表達式的排序順序，如下所示： + ASC：遞增 (例如，數值從低到高，字元字串 'A' 到 'Z')。若未指定選項，資料會預設為遞增排序。 + DESC：遞減 (數值從高到低，字串 'Z' 到 'A')。 NULLS FIRST \$1 NULLS LAST 這些選項指定 NULLS 應該排序在最前 (在非 Null 值之前) 或排序在最後 (在非 Null 值之後)。根據預設，NULLS 在 ASC 排序中排序和排名最後，而在 DESC 排序中排序和排名最前。 *frame\$1clause* 對於彙總函數，使用 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 ... ``` ## 支援的函數 AWS Clean RoomsSpark SQL 支援兩種類型的視窗函數：彙總和排名。 以下是支援的彙總函數： + [CUME\$1DIST 範圍函數](WF_CUME_DIST.md) + [DENSE\$1RANK 範圍函數](WF_DENSE_RANK.md) + [FIRST 視窗函數](WF_FIRST.md) + [FIRST\$1VALUE 範圍函數](WF_first_value.md) + [LAG 範圍函數](WF_LAG.md) + [LAST 視窗函數](WF-LAST.md) + [LAST\$1VALUE 範圍函數](WF_last_value.md) + [LEAD 範圍函數](WF_LEAD.md) 以下是支援的排名函數： + [DENSE\$1RANK 範圍函數](WF_DENSE_RANK.md) + [PERCENT\$1RANK 範圍函數](WF_PERCENT_RANK.md) + [RANK 範圍函數](WF_RANK.md) + [ROW\$1NUMBER 範圍函數](WF_ROW_NUMBER.md) ## 範圍函數範例的範例資料表 您可以在每個函數說明中找到特定的範圍函數範例。有些範例使用名為 WINSALES 的資料表，其中包含 11 個資料列，如下表所示。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/clean-rooms/latest/sql-reference/Window_functions.html) # CUME\$1DIST 範圍函數 計算視窗或分割區內值的累積分佈。假定為遞增排序，使用此公式來決定累積分佈： `count of rows with values <= x / count of rows in the window or partition` 其中，*x* 等於 ORDER BY 子句所指定欄之目前列中的值。以下資料集示範此公式的使用： ``` Row# Value Calculation CUME_DIST 1 2500 (1)/(5) 0.2 2 2600 (2)/(5) 0.4 3 2800 (3)/(5) 0.6 4 2900 (4)/(5) 0.8 5 3100 (5)/(5) 1.0 ``` 傳回值範圍是 >0 至 1 (含)。 ## 語法 ``` CUME_DIST () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ``` ## 引數 OVER 用於指定視窗分割的子句。OVER 子句不能包含視窗框規格。 PARTITION BY *partition\$1expression* 選用。此表達式針對 OVER 子句中的每一個群組，設定記錄範圍。 ORDER BY *order\$1list* 要計算累積分佈的表達式。表達式必須為數值資料類型，或可隱含地轉換為數值資料類型。如果省略 ORDER BY，所有列的傳回值為 1。 如果 ORDER BY 未產生唯一排序，則列的順序不確定。如需詳細資訊，請參閱[範圍函數的資料唯一排序](Window_functions.md#Examples_order_by_WF)。 ## 傳回類型 FLOAT8 ## 範例 以下範例計算每一個賣方的數量累積分佈： ``` select sellerid, qty, cume_dist() over (partition by sellerid order by qty) from winsales; sellerid qty cume_dist -------------------------------------------------- 1 10.00 0.33 1 10.64 0.67 1 30.37 1 3 10.04 0.25 3 15.15 0.5 3 20.75 0.75 3 30.55 1 2 20.09 0.5 2 20.12 1 4 10.12 0.5 4 40.23 1 ``` 如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。 # DENSE\$1RANK 範圍函數 DENSE\$1RANK 範圍函數根據 OVER 子句中的 ORDER BY 表達式，決定一組值之中某個值的排名。如果有選用的 PARTITION BY 子句，則會重設每一組列的排名。在排名準則中有相等值的列獲得相同排名。DENSE\$1RANK 函數有一方面不同於 RANK：如果兩列以上繫結在一起，則排名值的序列中沒有間隙。例如，假設兩列都排名 1，則下一個排名為 2。 在相同查詢中，排名函數可以搭配不同的 PARTITION BY 和 ORDER BY 子句。 ## 語法 ``` DENSE_RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] ) ``` ## 引數 ( ) 此函數不接受引數，但需要空括號。 OVER DENSE\$1RANK 函數的視窗子句。 PARTITION BY *expr\$1list* 選用。一或多個用於定義視窗的表達式。 ORDER BY *order\$1list* 選用。排名值所根據的表達式。如果未指定 PARTITION BY，ORDER BY 會使用整個資料表。如果省略 ORDER BY，所有列的傳回值為 1。 如果 ORDER BY 未產生唯一排序，則列的順序不確定。如需詳細資訊，請參閱[範圍函數的資料唯一排序](Window_functions.md#Examples_order_by_WF)。 ## 傳回類型 INTEGER ## 範例 下列範例會依銷售數量排序資料表 （以遞減順序），並將密集排名和一般排名指派給每一列。套用範圍函數結果之後排序結果。 ``` select salesid, qty, dense_rank() over(order by qty desc) as d_rnk, rank() over(order by qty desc) as rnk from winsales order by 2,1; salesid | qty | d_rnk | rnk ---------+-----+-------+----- 10001 | 10 | 5 | 8 10006 | 10 | 5 | 8 30001 | 10 | 5 | 8 40005 | 10 | 5 | 8 30003 | 15 | 4 | 7 20001 | 20 | 3 | 4 20002 | 20 | 3 | 4 30004 | 20 | 3 | 4 10005 | 30 | 2 | 2 30007 | 30 | 2 | 2 40001 | 40 | 1 | 1 (11 rows) ``` 在相同查詢中同時使用 DENSE\$1RANK 和 RANK 函數時，請注意指派給相同列集的排名差異。如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。 下列範例會依 SELLERID 分割資料表，並依數量排序每個分割區 （以遞減順序），並將密集排名指派給每一列。套用範圍函數結果之後排序結果。 ``` select salesid, sellerid, qty, dense_rank() over(partition by sellerid order by qty desc) as d_rnk from winsales order by 2,3,1; salesid | sellerid | qty | d_rnk ---------+----------+-----+------- 10001 | 1 | 10 | 2 10006 | 1 | 10 | 2 10005 | 1 | 30 | 1 20001 | 2 | 20 | 1 20002 | 2 | 20 | 1 30001 | 3 | 10 | 4 30003 | 3 | 15 | 3 30004 | 3 | 20 | 2 30007 | 3 | 30 | 1 40005 | 4 | 10 | 2 40001 | 4 | 40 | 1 (11 rows) ``` 如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。 # FIRST 視窗函數 根據一組列的排序，FIRST 會傳回指定表達式相對於視窗框架中第一列的值。 如需有關選取視窗框中最後一列的資訊，請參閱[LAST 視窗函數](WF-LAST.md)。 ## 語法 ``` FIRST( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引數 *表達式* 函數運算的目標欄或表達式。 IGNORE NULLS 當此選項與 FIRST 搭配使用時，函數會傳回框架中不是 NULL 的第一個值 （如果所有值都是 NULL，則為 NULL)。 RESPECT NULLS 指出 AWS Clean Rooms應該在決定要使用的資料列時包含 null 值。如果您不指定 IGNORE NULLS，則預設支援 RESPECT NULLS。 OVER 引進函數的視窗子句。 PARTITION BY *expr\$1list* 以一或多個表達式定義函數的視窗。 ORDER BY *order\$1list* 排序每一個分割區內的列。如果未指定 PARTITION BY 子句，ORDER BY 會排序整個資料表。如果您指定 ORDER BY 子句，則還必須指定 *frame\$1clause*。 FIRST 函數的結果取決於資料的排序。在下列情況中，結果不確定： + 未指定 ORDER BY 子句，且分割區包含一個表達式的兩個不同值 + 表達式評估為不同值，而這些值對應於 ORDER BY 清單中的相同值。 *frame\$1clause* 如果彙總函數使用 ORDER BY 子句，則需要明確的窗框子句。窗框子句在排序的結果中包含或排除資料列組，以調整函數視窗中的一個列集。窗框子句包含 ROWS 關鍵字和相關的指定元。請參閱 [範圍函數語法摘要](Window_functions.md#Window_function_synopsis)。 ## 傳回類型 這些函數支援使用基本AWS Clean Rooms資料類型的表達式。傳回類型與*運算式*的資料類型相同。 ## 範例 下列範例傳回 VENUE 資料表中每個會場的座位容量，且結果依容量排序 (高到低)。FIRST 函數用於選取與影格中第一列對應的場地名稱：在此情況下，為座位數量最高的列。結果依州分割，所以當 VENUESTATE 值變更時，就會選取新的第一個值。視窗框無界限，對於每一個分割區的第一列，選取的第一個值都相同。 以加利佛尼亞來說，`Qualcomm Stadium` 的座位數最多 (`70561`)，因此，對於 `CA` 分割區中的所有列，此名稱是第一個值。 ``` select venuestate, venueseats, venuename, first(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | first -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Qualcomm Stadium CA | 69843 | Monster Park | Qualcomm Stadium CA | 63026 | McAfee Coliseum | Qualcomm Stadium CA | 56000 | Dodger Stadium | Qualcomm Stadium CA | 45050 | Angel Stadium of Anaheim | Qualcomm Stadium CA | 42445 | PETCO Park | Qualcomm Stadium CA | 41503 | AT&T Park | Qualcomm Stadium CA | 22000 | Shoreline Amphitheatre | Qualcomm Stadium CO | 76125 | INVESCO Field | INVESCO Field CO | 50445 | Coors Field | INVESCO Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Dolphin Stadium FL | 73800 | Jacksonville Municipal Stadium | Dolphin Stadium FL | 65647 | Raymond James Stadium | Dolphin Stadium FL | 36048 | Tropicana Field | Dolphin Stadium ... ``` # FIRST\$1VALUE 範圍函數 在一組已排序的列中，FIRST\$1VALUE 會針對視窗框中的第一列，傳回指定之表達式的值。 如需有關選取視窗框中最後一列的資訊，請參閱[LAST\$1VALUE 範圍函數](WF_last_value.md)。 ## 語法 ``` FIRST_VALUE( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引數 *表達式* 函數運算的目標欄或表達式。 IGNORE NULLS 此選項與 FIRST\$1VALUE 一起使用時，函數會傳回窗框中第一個非 NULL (或如果所有值都是 NULL，則為 NULL) 的值。 RESPECT NULLS 指出 AWS Clean Rooms應該在決定要使用的資料列時包含 null 值。如果您不指定 IGNORE NULLS，則預設支援 RESPECT NULLS。 OVER 引進函數的視窗子句。 PARTITION BY *expr\$1list* 以一或多個表達式定義函數的視窗。 ORDER BY *order\$1list* 排序每一個分割區內的列。如果未指定 PARTITION BY 子句，ORDER BY 會排序整個資料表。如果您指定 ORDER BY 子句，則還必須指定 *frame\$1clause*。 FIRST\$1VALUE 函數的結果取決於資料的排序。在下列情況中，結果不確定： + 未指定 ORDER BY 子句，且分割區包含一個表達式的兩個不同值 + 表達式評估為不同值，而這些值對應於 ORDER BY 清單中的相同值。 *frame\$1clause* 如果彙總函數使用 ORDER BY 子句，則需要明確的窗框子句。窗框子句在排序的結果中包含或排除資料列組，以調整函數視窗中的一個列集。窗框子句包含 ROWS 關鍵字和相關的指定元。請參閱 [範圍函數語法摘要](Window_functions.md#Window_function_synopsis)。 ## 傳回類型 這些函數支援使用基本AWS Clean Rooms資料類型的表達式。傳回類型與*運算式*的資料類型相同。 ## 範例 下列範例傳回 VENUE 資料表中每個會場的座位容量，且結果依容量排序 (高到低)。會使用 FIRST\$1VALUE 函數來選取與窗框之第一列對應的會場名稱：在此案例中，即座位數最多的那一列。結果依州分割，所以當 VENUESTATE 值變更時，就會選取新的第一個值。視窗框無界限，對於每一個分割區的第一列，選取的第一個值都相同。 以加利佛尼亞來說，`Qualcomm Stadium` 的座位數最多 (`70561`)，因此，對於 `CA` 分割區中的所有列，此名稱是第一個值。 ``` select venuestate, venueseats, venuename, first_value(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | first_value -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Qualcomm Stadium CA | 69843 | Monster Park | Qualcomm Stadium CA | 63026 | McAfee Coliseum | Qualcomm Stadium CA | 56000 | Dodger Stadium | Qualcomm Stadium CA | 45050 | Angel Stadium of Anaheim | Qualcomm Stadium CA | 42445 | PETCO Park | Qualcomm Stadium CA | 41503 | AT&T Park | Qualcomm Stadium CA | 22000 | Shoreline Amphitheatre | Qualcomm Stadium CO | 76125 | INVESCO Field | INVESCO Field CO | 50445 | Coors Field | INVESCO Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Dolphin Stadium FL | 73800 | Jacksonville Municipal Stadium | Dolphin Stadium FL | 65647 | Raymond James Stadium | Dolphin Stadium FL | 36048 | Tropicana Field | Dolphin Stadium ... ``` # LAG 範圍函數 LAG 範圍函數傳回分割區中目前列上方 (之前) 給定位移那一列的值。 ## 語法 ``` LAG (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering ) ``` ## 引數 *value\$1expr* 函數運算的目標欄或表達式。 *offset* 選擇性參數，指定在目前列之前要傳回值的列數。位移可以是常數整數，或評估為整數的表達式。如果您未指定位移， AWS Clean Rooms會使用 `1`做為預設值。位移 `0` 表示目前列。 IGNORE NULLS 選用規格，指出 AWS Clean Rooms應該在決定要使用的資料列時略過 null 值。如果未列出 IGNORE NULLS，則會包含 Null 值。 您可以使用 NVL 或 COALESCE 表達式，將 Null 值換成另一個值。 RESPECT NULLS 指出 AWS Clean Rooms應該在決定要使用的資料列時包含 null 值。如果您不指定 IGNORE NULLS，則預設支援 RESPECT NULLS。 OVER 指定視窗分割和排序。OVER 子句不能包含視窗框規格。 PARTITION BY *window\$1partition* 選擇性引數，針對 OVER 子句中的每一個群組，設定記錄範圍。 ORDER BY *window\$1ordering* 排序每一個分割區內的列。 LAG 視窗函數支援使用任何AWS Clean Rooms資料類型的表達式。傳回類型與 *value\$1expr* 的類型相同。 ## 範例 下列範例顯示銷售給買方 ID 為 3 之買方的門票數量，以及買方 3 購買門票的時間。為了比較買方 3 的每次銷售與前次銷售，查詢會傳回每次銷售的前次銷售數量。因為 2008/1/16 之前沒有購買，所以第一個先前銷售數量值為 Null： ``` select buyerid, saletime, qtysold, lag(qtysold,1) over (order by buyerid, saletime) as prev_qtysold from sales where buyerid = 3 order by buyerid, saletime; buyerid | saletime | qtysold | prev_qtysold ---------+---------------------+---------+-------------- 3 | 2008-01-16 01:06:09 | 1 | 3 | 2008-01-28 02:10:01 | 1 | 1 3 | 2008-03-12 10:39:53 | 1 | 1 3 | 2008-03-13 02:56:07 | 1 | 1 3 | 2008-03-29 08:21:39 | 2 | 1 3 | 2008-04-27 02:39:01 | 1 | 2 3 | 2008-08-16 07:04:37 | 2 | 1 3 | 2008-08-22 11:45:26 | 2 | 2 3 | 2008-09-12 09:11:25 | 1 | 2 3 | 2008-10-01 06:22:37 | 1 | 1 3 | 2008-10-20 01:55:51 | 2 | 1 3 | 2008-10-28 01:30:40 | 1 | 2 (12 rows) ``` # LAST 視窗函數 指定一組有序的資料列，LAST 函數會傳回相對於影格中最後一列的表達式值。 如需有關選取框架中第一列的資訊，請參閱[FIRST 視窗函數](WF_FIRST.md)。 ## 語法 ``` LAST( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引數 *表達式* 函數運算的目標欄或表達式。 IGNORE NULLS 函數會傳回窗框中非 NULL (或如果所有值都是 NULL，則為 NULL) 的最後一個值。 RESPECT NULLS 指出 AWS Clean Rooms應該在決定要使用的資料列時包含 null 值。如果您不指定 IGNORE NULLS，則預設支援 RESPECT NULLS。 OVER 引進函數的視窗子句。 PARTITION BY *expr\$1list* 以一或多個表達式定義函數的視窗。 ORDER BY *order\$1list* 排序每一個分割區內的列。如果未指定 PARTITION BY 子句，ORDER BY 會排序整個資料表。如果您指定 ORDER BY 子句，則還必須指定 *frame\$1clause*。 結果取決於資料的順序。在下列情況中，結果不確定： + 未指定 ORDER BY 子句，且分割區包含一個表達式的兩個不同值 + 表達式評估為不同值，而這些值對應於 ORDER BY 清單中的相同值。 *frame\$1clause* 如果彙總函數使用 ORDER BY 子句，則需要明確的窗框子句。窗框子句在排序的結果中包含或排除資料列組，以調整函數視窗中的一個列集。窗框子句包含 ROWS 關鍵字和相關的指定元。請參閱 [範圍函數語法摘要](Window_functions.md#Window_function_synopsis)。 ## 傳回類型 這些函數支援使用基本AWS Clean Rooms資料類型的表達式。傳回類型與*運算式*的資料類型相同。 ## 範例 下列範例傳回 VENUE 資料表中每個會場的座位容量，且結果依容量排序 (高到低)。LAST 函數用於選取與影格中最後一列對應的場地名稱：在此情況下，為座位數最少的資料列。結果依州分割，所以當 VENUESTATE 值變更時，就會選取新的最後一個值。視窗框無界限，對於每一個分割區的第一列，選取的最後一個值都相同。 以加利佛尼亞來說，分割區中的每一列列都傳回 `Shoreline Amphitheatre`，因為其座位數最少 (`22000`)。 ``` select venuestate, venueseats, venuename, last(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | last -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Shoreline Amphitheatre CA | 69843 | Monster Park | Shoreline Amphitheatre CA | 63026 | McAfee Coliseum | Shoreline Amphitheatre CA | 56000 | Dodger Stadium | Shoreline Amphitheatre CA | 45050 | Angel Stadium of Anaheim | Shoreline Amphitheatre CA | 42445 | PETCO Park | Shoreline Amphitheatre CA | 41503 | AT&T Park | Shoreline Amphitheatre CA | 22000 | Shoreline Amphitheatre | Shoreline Amphitheatre CO | 76125 | INVESCO Field | Coors Field CO | 50445 | Coors Field | Coors Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Tropicana Field FL | 73800 | Jacksonville Municipal Stadium | Tropicana Field FL | 65647 | Raymond James Stadium | Tropicana Field FL | 36048 | Tropicana Field | Tropicana Field ... ``` # LAST\$1VALUE 範圍函數 在一組已排序的列中，LAST\$1VALUE 函數針對窗框中的最後一列，傳回運算式的值。 如需有關選取框架中第一列的資訊，請參閱[FIRST\$1VALUE 範圍函數](WF_first_value.md)。 ## 語法 ``` LAST_VALUE( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 引數 *表達式* 函數運算的目標欄或表達式。 IGNORE NULLS 函數會傳回窗框中非 NULL (或如果所有值都是 NULL，則為 NULL) 的最後一個值。 RESPECT NULLS 指出 AWS Clean Rooms應該在決定要使用的資料列時包含 null 值。如果您不指定 IGNORE NULLS，則預設支援 RESPECT NULLS。 OVER 引進函數的視窗子句。 PARTITION BY *expr\$1list* 以一或多個表達式定義函數的視窗。 ORDER BY *order\$1list* 排序每一個分割區內的列。如果未指定 PARTITION BY 子句，ORDER BY 會排序整個資料表。如果您指定 ORDER BY 子句，則還必須指定 *frame\$1clause*。 結果取決於資料的順序。在下列情況中，結果不確定： + 未指定 ORDER BY 子句，且分割區包含一個表達式的兩個不同值 + 表達式評估為不同值，而這些值對應於 ORDER BY 清單中的相同值。 *frame\$1clause* 如果彙總函數使用 ORDER BY 子句，則需要明確的窗框子句。窗框子句在排序的結果中包含或排除資料列組，以調整函數視窗中的一個列集。窗框子句包含 ROWS 關鍵字和相關的指定元。請參閱 [範圍函數語法摘要](Window_functions.md#Window_function_synopsis)。 ## 傳回類型 這些函數支援使用基本AWS Clean Rooms資料類型的表達式。傳回類型與*運算式*的資料類型相同。 ## 範例 下列範例傳回 VENUE 資料表中每個會場的座位容量，且結果依容量排序 (高到低)。LAST\$1VALUE 函數用於選取與窗框之最後一列對應的會場名稱：在此案例中，即座位數最少的那一列。結果依州分割，所以當 VENUESTATE 值變更時，就會選取新的最後一個值。視窗框無界限，對於每一個分割區的第一列，選取的最後一個值都相同。 以加利佛尼亞來說，分割區中的每一列列都傳回 `Shoreline Amphitheatre`，因為其座位數最少 (`22000`)。 ``` select venuestate, venueseats, venuename, last_value(venuename) over(partition by venuestate order by venueseats desc rows between unbounded preceding and unbounded following) from (select * from venue where venueseats >0) order by venuestate; venuestate | venueseats | venuename | last_value -----------+------------+--------------------------------+------------------------------ CA | 70561 | Qualcomm Stadium | Shoreline Amphitheatre CA | 69843 | Monster Park | Shoreline Amphitheatre CA | 63026 | McAfee Coliseum | Shoreline Amphitheatre CA | 56000 | Dodger Stadium | Shoreline Amphitheatre CA | 45050 | Angel Stadium of Anaheim | Shoreline Amphitheatre CA | 42445 | PETCO Park | Shoreline Amphitheatre CA | 41503 | AT&T Park | Shoreline Amphitheatre CA | 22000 | Shoreline Amphitheatre | Shoreline Amphitheatre CO | 76125 | INVESCO Field | Coors Field CO | 50445 | Coors Field | Coors Field DC | 41888 | Nationals Park | Nationals Park FL | 74916 | Dolphin Stadium | Tropicana Field FL | 73800 | Jacksonville Municipal Stadium | Tropicana Field FL | 65647 | Raymond James Stadium | Tropicana Field FL | 36048 | Tropicana Field | Tropicana Field ... ``` # LEAD 範圍函數 LEAD 範圍函數傳回分割區中目前列下方 (之後) 給定位移那一列的值。 ## 語法 ``` LEAD (value_expr [, offset ]) [ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering ) ``` ## 引數 *value\$1expr* 函數運算的目標欄或表達式。 *offset* 選擇性參數，指定在目前列下方要傳回值的列數。位移可以是常數整數，或評估為整數的表達式。如果您未指定位移， AWS Clean Rooms會使用 `1`做為預設值。位移 `0` 表示目前列。 IGNORE NULLS 選用規格，指出 AWS Clean Rooms應該在決定要使用的資料列時略過 null 值。如果未列出 IGNORE NULLS，則會包含 Null 值。 您可以使用 NVL 或 COALESCE 表達式，將 Null 值換成另一個值。 RESPECT NULLS 指出 AWS Clean Rooms應該在決定要使用的資料列時包含 null 值。如果您不指定 IGNORE NULLS，則預設支援 RESPECT NULLS。 OVER 指定視窗分割和排序。OVER 子句不能包含視窗框規格。 PARTITION BY *window\$1partition* 選擇性引數，針對 OVER 子句中的每一個群組，設定記錄範圍。 ORDER BY *window\$1ordering* 排序每一個分割區內的列。 LEAD 視窗函數支援使用任何AWS Clean Rooms資料類型的表達式。傳回類型與 *value\$1expr* 的類型相同。 ## 範例 下列範例提供 SALES 資料表中於 2008 年 1 月 1 日和 2008 年 1 月 2 日售出門票之活動的佣金，以及對隨後銷售之門票銷售支付的佣金。 ``` select eventid, commission, saletime, lead(commission, 1) over (order by saletime) as next_comm from sales where saletime between '2008-01-01 00:00:00' and '2008-01-02 12:59:59' order by saletime; eventid | commission | saletime | next_comm ---------+------------+---------------------+----------- 6213 | 52.05 | 2008-01-01 01:00:19 | 106.20 7003 | 106.20 | 2008-01-01 02:30:52 | 103.20 8762 | 103.20 | 2008-01-01 03:50:02 | 70.80 1150 | 70.80 | 2008-01-01 06:06:57 | 50.55 1749 | 50.55 | 2008-01-01 07:05:02 | 125.40 8649 | 125.40 | 2008-01-01 07:26:20 | 35.10 2903 | 35.10 | 2008-01-01 09:41:06 | 259.50 6605 | 259.50 | 2008-01-01 12:50:55 | 628.80 6870 | 628.80 | 2008-01-01 12:59:34 | 74.10 6977 | 74.10 | 2008-01-02 01:11:16 | 13.50 4650 | 13.50 | 2008-01-02 01:40:59 | 26.55 4515 | 26.55 | 2008-01-02 01:52:35 | 22.80 5465 | 22.80 | 2008-01-02 02:28:01 | 45.60 5465 | 45.60 | 2008-01-02 02:28:02 | 53.10 7003 | 53.10 | 2008-01-02 02:31:12 | 70.35 4124 | 70.35 | 2008-01-02 03:12:50 | 36.15 1673 | 36.15 | 2008-01-02 03:15:00 | 1300.80 ... (39 rows) ``` # PERCENT\$1RANK 範圍函數 計算給定資料列的百分比排行。使用此公式決定百分比排名： `(x - 1) / (the number of rows in the window or partition - 1)` 其中 *x* 是目前列的排名。以下資料集示範此公式的使用： ``` Row# Value Rank Calculation PERCENT_RANK 1 15 1 (1-1)/(7-1) 0.0000 2 20 2 (2-1)/(7-1) 0.1666 3 20 2 (2-1)/(7-1) 0.1666 4 20 2 (2-1)/(7-1) 0.1666 5 30 5 (5-1)/(7-1) 0.6666 6 30 5 (5-1)/(7-1) 0.6666 7 40 7 (7-1)/(7-1) 1.0000 ``` 傳回值範圍是 0 至 1 (含)。任何集的第一列的 PERCENT\$1RANK 為 0。 ## 語法 ``` PERCENT_RANK () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ``` ## 引數 ( ) 此函數不接受引數，但需要空括號。 OVER 用於指定視窗分割的子句。OVER 子句不能包含視窗框規格。 PARTITION BY *partition\$1expression* 選用。此表達式針對 OVER 子句中的每一個群組，設定記錄範圍。 ORDER BY *order\$1list* 選用。要計算百分比排名的表達式。表達式必須為數值資料類型，或可隱含地轉換為數值資料類型。如果省略 ORDER BY，所有列的傳回值為 0。 如果 ORDER BY 未產生唯一排序，則列的順序不確定。如需詳細資訊，請參閱[範圍函數的資料唯一排序](Window_functions.md#Examples_order_by_WF)。 ## 傳回類型 FLOAT8 ## 範例 以下範例計算每一個賣方的銷售數量百分比排名： ``` select sellerid, qty, percent_rank() over (partition by sellerid order by qty) from winsales; sellerid qty percent_rank ---------------------------------------- 1 10.00 0.0 1 10.64 0.5 1 30.37 1.0 3 10.04 0.0 3 15.15 0.33 3 20.75 0.67 3 30.55 1.0 2 20.09 0.0 2 20.12 1.0 4 10.12 0.0 4 40.23 1.0 ``` 如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。 # RANK 範圍函數 RANK 範圍函數根據 OVER 子句中的 ORDER BY 表達式，決定一組值之中某個值的排名。如果有選用的 PARTITION BY 子句，則會重設每一組列的排名。排名條件具有相同值的資料列會收到相同的排名。 會將繫結的資料列數目AWS Clean Rooms新增至繫結的排名，以計算下一個排名，因此排名可能不是連續的數字。例如，假設兩列都排名 1，則下一個排名為 3。 RANK 函數有一方面不同於 [DENSE\$1RANK 範圍函數](WF_DENSE_RANK.md)：對於 DENSE\$1RANK，如果兩列以上繫結在一起，則排名值的序列中沒有間隙。例如，假設兩列都排名 1，則下一個排名為 2。 在相同查詢中，排名函數可以搭配不同的 PARTITION BY 和 ORDER BY 子句。 ## 語法 ``` RANK () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] ) ``` ## 引數 ( ) 此函數不接受引數，但需要空括號。 OVER RANK 函數的視窗子句。 PARTITION BY *expr\$1list* 選用。一或多個用於定義視窗的表達式。 ORDER BY *order\$1list* 選用。定義排名值所根據的欄。如果未指定 PARTITION BY，ORDER BY 會使用整個資料表。如果省略 ORDER BY，所有列的傳回值為 1。 如果 ORDER BY 未產生唯一排序，則列的順序不確定。如需詳細資訊，請參閱[範圍函數的資料唯一排序](Window_functions.md#Examples_order_by_WF)。 ## 傳回類型 INTEGER ## 範例 下列範例會依銷售數量排序資料表 (預設為遞增)，並將排名指派給每一列。最高排名的值為 1。套用範圍函數結果之後排序結果： ``` select salesid, qty, rank() over (order by qty) as rnk from winsales order by 2,1; salesid | qty | rnk --------+-----+----- 10001 | 10 | 1 10006 | 10 | 1 30001 | 10 | 1 40005 | 10 | 1 30003 | 15 | 5 20001 | 20 | 6 20002 | 20 | 6 30004 | 20 | 6 10005 | 30 | 9 30007 | 30 | 9 40001 | 40 | 11 (11 rows) ``` 請注意，此範例中的外部 ORDER BY 子句包含資料欄 2 和 1，以確保每次執行此查詢時都會AWS Clean Rooms傳回一致排序的結果。例如，銷售 ID 為 10001 和 10006 的列有相同的 QTY 和 RNK 值。依第 1 欄排序最終結果集可確保列 10001 一定位於 10006 之前。如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。 在以下範例中，範圍函數反向排序 (`order by qty desc`)。現在，最高排名值套用至最大 QTY 值。 ``` select salesid, qty, rank() over (order by qty desc) as rank from winsales order by 2,1; salesid | qty | rank ---------+-----+----- 10001 | 10 | 8 10006 | 10 | 8 30001 | 10 | 8 40005 | 10 | 8 30003 | 15 | 7 20001 | 20 | 4 20002 | 20 | 4 30004 | 20 | 4 10005 | 30 | 2 30007 | 30 | 2 40001 | 40 | 1 (11 rows) ``` 如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。 下列範例會依 SELLERID 分割資料表，並依數量排序每一個分割區 (以遞減順序)，然後指派排名給每一列。套用範圍函數結果之後排序結果。 ``` select salesid, sellerid, qty, rank() over (partition by sellerid order by qty desc) as rank from winsales order by 2,3,1; salesid | sellerid | qty | rank --------+----------+-----+----- 10001 | 1 | 10 | 2 10006 | 1 | 10 | 2 10005 | 1 | 30 | 1 20001 | 2 | 20 | 1 20002 | 2 | 20 | 1 30001 | 3 | 10 | 4 30003 | 3 | 15 | 3 30004 | 3 | 20 | 2 30007 | 3 | 30 | 1 40005 | 4 | 10 | 2 40001 | 4 | 40 | 1 (11 rows) ``` # ROW\$1NUMBER 範圍函數 根據 OVER 子句中的 ORDER BY 表達式，決定一組列之內目前列的序數 (從 1 起算)。如果有選用的 PARTITION BY 子句，則會重設每一組列的序數。對於 ORDER BY 表達式，具有相等值的列會獲得非決定性的不同列號。 ## 語法 ``` ROW_NUMBER () OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list ] ) ``` ## 引數 ( ) 此函數不接受引數，但需要空括號。 OVER ROW\$1NUMBER 函數的視窗子句。 PARTITION BY *expr\$1list* 選用。一或多個用於定義 ROW\$1NUMBER 函數的表達式。 ORDER BY *order\$1list* 選用。此表達式定義列號所根據的欄。如果未指定 PARTITION BY，ORDER BY 會使用整個資料表。 如果 ORDER BY 未產生唯一排序或被省略，則列的順序不確定。如需詳細資訊，請參閱[範圍函數的資料唯一排序](Window_functions.md#Examples_order_by_WF)。 ## 傳回類型 BIGINT ## 範例 下列範例依 SELLERID 分割資料表，並依 QTY 排序每一個分割區 (以遞增順序)，然後將列號指派給每一列。套用範圍函數結果之後排序結果。 ``` select salesid, sellerid, qty, row_number() over (partition by sellerid order by qty asc) as row from winsales order by 2,4; salesid | sellerid | qty | row ---------+----------+-----+----- 10006 | 1 | 10 | 1 10001 | 1 | 10 | 2 10005 | 1 | 30 | 3 20001 | 2 | 20 | 1 20002 | 2 | 20 | 2 30001 | 3 | 10 | 1 30003 | 3 | 15 | 2 30004 | 3 | 20 | 3 30007 | 3 | 30 | 4 40005 | 4 | 10 | 1 40001 | 4 | 40 | 2 (11 rows) ``` 如需 WINSALES 資料表的描述，請參閱[範圍函數範例的範例資料表](Window_functions.md#Window_function_example)。