9.22. 窗口函数#

窗口函数提供了对与当前查询行相关的行集执行计算的能力。有关此功能的介绍，请参见第 3.5 节，有关语法详细信息，请参见第 4.2.8 节。

内置窗口函数列在表 9.64中。请注意，这些函数必须使用窗口函数语法调用，即需要一个OVER子句。

除了这些函数之外，任何内置或用户定义的普通聚合（即非有序集或假设集聚合）都可以用作窗口函数；有关内置聚合的列表，请参阅第 9.21 节。只有在调用后跟有OVER子句时，聚合函数才作为窗口函数；否则，它们作为普通聚合并为整个集合返回单行。

表 9.64. 通用窗口函数

函数 说明
row_number () → bigint 返回当前行在其分区内的行号，从 1 开始计数。
rank () → bigint 返回当前行的排名，有间隙；即，其对等组中第一行的 row_number。
dense_rank () → bigint 返回当前行的排名，无间隙；此函数有效地计算对等组。
percent_rank () → double precision 返回当前行的相对排名，即 (rank - 1) / (分区总行数 - 1)。因此，值范围从 0 到 1（包括）。
cume_dist () → double precision 返回累积分布，即（当前行之前或与当前行对等的分区行数）/（分区总行数）。因此，值范围从 1/N 到 1。
ntile ( num_buckets integer ) → integer 返回一个介于 1 到参数值之间的整数，尽可能平均地划分分区。
lag ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible 返回在分区内当前行前 offset 行处评估的 value；如果没有这样的行，则返回 default（其类型必须与 value 兼容）。offset 和 default 均相对于当前行进行评估。如果省略，offset 的默认值为 1，default 的默认值为 NULL。
lead ( value anycompatible [, offset integer [, default anycompatible ]] ) → anycompatible 返回在分区内当前行后 offset 行处评估的 value；如果没有这样的行，则返回 default（其类型必须与 value 兼容）。offset 和 default 均相对于当前行进行评估。如果省略，offset 的默认值为 1，default 的默认值为 NULL。
first_value ( value anyelement ) → anyelement 返回窗口框架第一行的 value 评估值。
last_value ( value anyelement ) → anyelement 返回窗口框架最后一行 value 的评估值。
nth_value ( value anyelement, n integer ) → anyelement 返回窗口框架第 n 行（从 1 开始计数）处评估的 value；如果没有这样的行，则返回 NULL。

表 9.64 中列出的所有函数都取决于相关窗口定义的ORDER BY子句指定的排序顺序。仅考虑ORDER BY列时不不同的行称为对等行。四个排名函数（包括cume_dist）的定义使其对对等组中的所有行给出相同的答案。

请注意，first_value、last_value和nth_value仅考虑“窗口框架”中的行，默认情况下，该框架包含从分区开始到当前行的最后一个对等行的行。这可能会对last_value产生无用的结果，有时也会对nth_value产生无用的结果。您可以通过向OVER子句添加合适的框架规范（RANGE、ROWS或GROUPS）来重新定义框架。有关框架规范的更多信息，请参见第 4.2.8 节。

当聚合函数用作窗口函数时，它将对当前行的窗口框架内的行进行聚合。与ORDER BY和默认窗口框架定义一起使用的聚合会产生“运行总和”类型的行为，这可能不是想要的。若要对整个分区进行聚合，请省略ORDER BY或使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。可以使用其他框架规范来获得其他效果。

注意

SQL 标准为lead、lag、first_value、last_value和nth_value定义了RESPECT NULLS或IGNORE NULLS选项。这在PostgreSQL中未实现：行为始终与标准的默认值相同，即RESPECT NULLS。同样，标准的nth_value的FROM FIRST或FROM LAST选项未实现：仅支持默认的FROM FIRST行为。（您可以通过反转ORDER BY排序来实现FROM LAST的结果。）