9.21. 窗口函数

9.21. 窗口函数
上一页	上一级	第 9 章函数和操作符	起始页	下一页

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

表 9.57列出了内建的窗口函数。注意必须使用窗口函数的语法调用这些函数，也就是，一个OVER子句是必需的。

除了这些函数外，任何内建的或用户定义的通用或统计聚集（也就是有序集或假想集聚集除外）都可以作为窗口函数（内建聚集函数的列表见第 9.20 节）。仅当调用跟着OVER子句时，聚集函数才会作为窗口函数；否则它们作为非窗口的聚集并为剩余的集合返回单行。

表 9.57. 通用窗口函数

函数	返回类型	描述
`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 anyelement [, offset integer [, default anyelement ]])`	`和value的类型相同`	返回`value`，它在分区内当前行的之前`offset`个位置的行上计算；如果没有这样的行，返回`default`替代。 (作为`value`必须是相同类型)。 `offset`和`default`都是根据当前行计算的结果。如果忽略它们，则`offset`默认是1，`default`默认是空值
`lead(value anyelement [, offset integer [, default anyelement ]])`	`和value类型相同`	返回`value`，它在分区内当前行的之后`offset`个位置的行上计算；如果没有这样的行，返回`default`替代。(作为`value`必须是相同类型)。`offset`和`default`都是根据当前行计算的结果。如果忽略它们，则`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`；没有这样的行则返回空值

在表 9.57中列出的所有函数都依赖于相关窗口定义的ORDER BY子句指定的排序顺序。当仅考虑ORDER BY列时，不能区分的行被称为是同等行。定义的这四个排名函数(包括cume_dist) ，对于所有同等行的答案相同。

注意first_value、last_value和nth_value只考虑“窗口帧”内的行，它默认情况下包含从分区的开始行直到当前行的最后一个同等行。这对last_value可能不会给出有用的结果，有时对nth_value也一样。你可以通过向OVER子句增加一个合适的帧声明（RANGE或ROWS）来重定义帧。关于帧声明的更多信息请参考第 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的结果）。

cume_dist计算小于或等于当前行及其同级的分区行的比例，而percent_rank计算小于当前行的分区行的比例，假设当前行在分区中不存在。

上一页	上一级	下一页
9.20. 聚集函数	起始页	9.22. 子查询表达式