本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。 # 窗口函数 通过使用窗口函数，您可以更高效地创建分析业务查询。窗口函数运行于分区或结果集的“窗口”上，并为该窗口中的每个行返回一个值。相比之下，非窗口函数执行与结果集中的每个行相关的计算。与聚合结果行的分组函数不同，窗口函数在表的表达式中的保留所有行。 使用该窗口中的行集中的值计算返回的值。对于表中的每一行，窗口定义一组用于计算其他属性的行。窗口使用窗口规范（OVER 子句）进行定义并基于以下三个主要概念： + *窗口分区*，构成了行组（PARTITION 子句） + *窗口排序*，定义了每个分区中行的顺序或序列（ORDER BY 子句） + *窗口框架*，相对于每个行进行定义以进一步限制行集（ROWS 规范） 窗口函数是在查询中执行的最后一组操作（最后的 ORDER BY 子句除外）。所有联接和所有 WHERE、GROUP BY 和 HAVING 子句均在处理窗口函数前完成。因此，窗口函数只能显示在选择列表或 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 顺序首先对 NULL 值进行排序和排名。 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 指定是应首先对 NULL 值进行排序（非 null 值之前）还是最后对 NULL 值进行排序（非 null 值之后）的选项。默认情况下，按 ASC 顺序最后对 NULLS 进行排序和排名，按 DESC 顺序首先对 NULLS 进行排序和排名。 *frame\$1clause* 对于聚合函数，框架子句在使用 ORDER BY 时进一步优化函数窗口中的行集。它使您可以包含或排除已排序结果中的行集。框架子句包括 ROWS 关键字和关联的说明符。 frame 子句不适用于排名函数。同时，在聚合函数的 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 指示窗口开始于等同于当前行之前的偏移值的行数。UNBOUNDED PRECEDING 是默认值。 CURRENT ROW 指示窗口在当前行开始或结束。 UNBOUNDED FOLLOWING 指示窗口在分区的最后一行结束；*offset* 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 ... ``` ## 支持的函数 AWS Clean RoomsSpark SQL 支持两种类型的窗口函数：聚合和排名。 以下是支持的聚合函数： + [CUME\$1DIST 开窗函数](WF_CUME_DIST.md) + [DENSE\$1RANK 窗口函数](WF_DENSE_RANK.md) + [第一个窗口功能](WF_FIRST.md) + [FIRST\$1VALUE 窗口函数](WF_first_value.md) + [LAG 窗口函数](WF_LAG.md) + [最后一个窗口函数](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_cn/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（含 1）之间。 ## 语法 ``` CUME_DIST () OVER ( [ PARTITION BY partition_expression ] [ ORDER BY order_list ] ) ``` ## 参数 OVER 一个指定窗口分区的子句。OVER 子句不能包含窗口框架规范。 PARTITION BY *partition\$1expression* 可选。一个设置 OVER 子句中每个组的记录范围的表达式。 ORDER BY *order\$1list* 用于计算累积分布的表达式。该表达式必须具有数字数据类型或可隐式转换为 1。如果省略 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 返回指定表达式相对于窗口框架中第一行的值。 有关选择框架中最后一行的信息，请参阅 [最后一个窗口函数](WF-LAST.md)。 ## 语法 ``` FIRST( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 参数 *expression* 对其执行函数的目标列或表达式。 IGNORE NULLS 当此选项与 FIRST 一起使用时，该函数返回框架中第一个不是 NULL 的值（如果所有值都为 NULL，则返回 NULL）。 RESPECT NULLS 表示在确定AWS Clean Rooms要使用哪一行时应包含空值。如果您未指定 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数据类型的表达式。返回类型与 *expression* 的数据类型相同。 ## 示例 以下示例返回 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 ] ) ``` ## 参数 *expression* 对其执行函数的目标列或表达式。 IGNORE NULLS 将此选项与 FIRST\$1VALUE 结合使用时，该函数返回不为 NULL 的框架中的第一个值（如果所有值为 NULL，则返回 NULL）。 RESPECT NULLS 表示在确定AWS Clean Rooms要使用哪一行时应包含空值。如果您未指定 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数据类型的表达式。返回类型与 *expression* 的数据类型相同。 ## 示例 以下示例返回 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应跳过空值。如果未列出 IGNORE NULLS，则包含 Null 值。 您可以使用 NVL 或 COALESCE 表达式将 null 值替换为另一个值。 RESPECT NULLS 表示在确定AWS Clean Rooms要使用哪一行时应包含空值。如果您未指定 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 的上一销售进行比较，查询要返回每个销售的上一销量。由于 1/16/2008 之前未进行购买，则第一个上一销量值为 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 函数返回相对于帧中最后一行的表达式值。 有关选择框架中第一行的信息，请参阅 [第一个窗口功能](WF_FIRST.md)。 ## 语法 ``` LAST( expression )[ IGNORE NULLS | RESPECT NULLS ] OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] ) ``` ## 参数 *expression* 对其执行函数的目标列或表达式。 IGNORE NULLS 该函数返回不为 NULL 的框架中的最后一个值（如果所有值为 NULL，则返回 NULL）。 RESPECT NULLS 表示在确定AWS Clean Rooms要使用哪一行时应包含空值。如果您未指定 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数据类型的表达式。返回类型与 *expression* 的数据类型相同。 ## 示例 以下示例返回 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 ] ) ``` ## 参数 *expression* 对其执行函数的目标列或表达式。 IGNORE NULLS 该函数返回不为 NULL 的框架中的最后一个值（如果所有值为 NULL，则返回 NULL）。 RESPECT NULLS 表示在确定AWS Clean Rooms要使用哪一行时应包含空值。如果您未指定 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数据类型的表达式。返回类型与 *expression* 的数据类型相同。 ## 示例 以下示例返回 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应跳过空值。如果未列出 IGNORE NULLS，则包含 Null 值。 您可以使用 NVL 或 COALESCE 表达式将 null 值替换为另一个值。 RESPECT NULLS 表示在确定AWS Clean Rooms要使用哪一行时应包含空值。如果您未指定 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 日与 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（含 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* 可选。用于计算百分比排名的表达式。该表达式必须具有数字数据类型或可隐式转换为 1。如果省略 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返回排序一致的结果。例如，销售额为 IDs 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)。