窗口函数

通过使用窗口函数，您可以更高效地创建分析业务查询。窗口函数运行于分区或结果集的“窗口”上，并为该窗口中的每个行返回一个值。相比之下，非窗口函数执行与结果集中的每个行相关的计算。与聚合结果行的分组函数不同，窗口函数在表的表达式中的保留所有行。

使用该窗口中的行集中的值计算返回的值。对于表中的每一行，窗口定义一组用于计算其他属性的行。窗口使用窗口规范（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_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 子句，则必须在一个切片上按顺序运行聚合步骤，这可能对性能产生显著的负面影响，特别是对于大型集群。

AWS Clean Rooms 不支持 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 顺序首先对 NULL 值进行排序和排名。

AWS Clean Rooms 不支持 ORDER BY 子句中的字符串文字。

如果省略 ORDER BY 子句，则行的顺序是不确定的。

注意

在任何并行系统中 AWS Clean Rooms，例如，当 ORDER BY 子句不生成数据的唯一和总体顺序时，行的顺序是不确定的。也就是说，如果 ORDER BY 表达式生成重复的值（部分排序），则这些行的返回顺序可能因运行而异。 AWS Clean Rooms 反过来，窗口函数可能返回意外的或不一致的结果。有关更多信息，请参阅窗口函数的唯一数据排序。

column_name

执行分区或排序操作所依据的列的名称。

ASC | DESC

一个定义表达式的排序顺序的选项，如下所示：

ASC：升序（例如，按数值的从低到高的顺序和字符串的从 A 到 Z 的顺序）。如果未指定选项，则默认情况下将按升序对数据进行排序。
DESC：降序（按数值的从高到低的顺序和字符串的从 Z 到 A 的顺序）。

NULLS FIRST | NULLS LAST

指定是应首先对 NULL 值进行排序（非 null 值之前）还是最后对 NULL 值进行排序（非 null 值之后）的选项。默认情况下，按 ASC 顺序最后对 NULLS 进行排序和排名，按 DESC 顺序首先对 NULLS 进行排序和排名。

frame_clause

对于聚合函数，框架子句在使用 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 Rooms 支持两种类型的窗口函数：聚合和排名。

以下是支持的聚合函数：

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

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

TO_VARBYTE

AVG