9.21. 窗口函数

窗口函数提供在与当前查询行相关的行集合上执行计算的能力。有关这个特性的介绍请见Section 3.5

Table 9-48列出了内建的窗口函数。注意必须使用窗口函数的语法调用这些函数; 一个OVER子句是必需的。

除了这些函数外,任何内建的或用户定义的聚集函数都可以作为窗口函数(内建聚集函数的列表见Section 9.20)。仅当调用跟着OVER子句时,聚集函数才会作为窗口函数;否则它们作为常规的聚集。

Table 9-48. 通用窗口函数

函数返回类型描述
row_number() bigint 当前行在其分区中的行号,从1计
rank() bigint 带间隙的当前行排名; 与该行的第一个同等行的row_number相同
dense_rank() bigint 不带间隙的当前行排名; 这个函数计数同等组
percent_rank() double precision 当前行的相对排名: (rank- 1) / (总行数 - 1)
cume_dist() double precision 当前行的相对排名: (当前行前面的行数 或 与当前行同等的行的行数)/(总行数)
ntile(num_buckets integer) integer 从1到参数值的整数范围,尽可能等分分区
lag(value any [, offset integer [, default any ]]) value的类型相同 返回value,它在分区内当前行的之前offset个位置的行上计算;如果没有这样的行,返回default替代。offsetdefault都是根据当前行计算的结果。如果忽略它们,则offset默认是1,default默认是空值
lead(value any [, offset integer [, default any ]]) value类型相同 返回value,它在分区内当前行的之后offset个位置的行上计算;如果没有这样的行,返回default替代。offsetdefault都是根据当前行计算的结果。如果忽略它们,则offset默认是1,default默认是空值
first_value(value any) same type as value 返回在窗口帧中第一行上计算的value
last_value(value any) value类型相同 返回在窗口帧中最后一行上计算的value
nth_value(value any, nth integer) value类型相同 返回在窗口帧中第nth行(行从1计数)上计算的value;没有这样的行则返回空值

Table 9-48中列出的所有函数都依赖于相关窗口定义的ORDER BY子句指定的排序顺序。 在ORDER BY排序中不能区分的行被称为是同等行。定义的这四个排名函数,对于任何两个同等行的答案相同。

注意first_valuelast_valuenth_value只考虑"窗口帧"内的行,它默认情况下包含从分区的开始行直到当前行的最后一个同等行。这对last_value可能不会给出有用的结果,有时对nth_value也一样。你可以通过向OVER子句增加一个合适的帧声明(RANGEROWS)来重定义帧。关于帧声明的更多信息请参考Section 4.2.8

当一个聚集函数被用作窗口函数时,它将在当前行的窗口帧内的行上聚集。 一个使用ORDER BY和默认窗口帧定义的聚集产生一种"运行时求和"类型的行为,这可能是或者不是想要的结果。为了获取在整个分区上的聚集,忽略ORDER BY或者使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。 其它窗口帧声明可以用来获得其它的效果。

Note: SQL 标准为leadlagfirst_valuelast_valuenth_value定义了 一个RESPECT NULLSIGNORE NULLS选项。 这在PostgreSQL中没有实现:行为总是与标准的默认相同,即RESPECT NULLS。 同样,标准中用于nth_valueFROM FIRSTFROM LAST选项没有实现: 只有支持默认的FROM FIRST行为(你可以通过反转ORDER BY的排序达到FROM LAST的结果)。