窗口函数#

窗口函数

窗口函数提供在与当前查询行相关的行集合上执行计算的能力。

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

在这些函数之外，任何内建的或者用户定义的通用或者统计性聚集（即非有序集和假想集聚集）都可以被用作一个窗口函数，内建聚集的列表请见聚合函数。仅当聚集函数调用后面跟着一个OVER子句时，聚集函数才会像窗口函数那样工作，否则它们会按非窗口聚集的方式运行并且为整个集合返回一个单一行。

通用窗口函数#

函数

返回类型

描述

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)

同 value

返回在窗口帧中第一行上计算的 value

last_value(value any)

同 value

返回在窗口帧中最后一行上计算的 value

nth_value(value any, nth integer)

同 value

返回在窗口帧中第 nth 行（行从 1 计）上计算的 value；没有这样的行则返回空值

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

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

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

备注

注意

SQL 标准为lead、lag、first_value、last_value和nth_value定义了一个RESPECT NULLS或IGNORE NULLS选项。 这在 OushuDB 中没有实现：行为总是与标准的默认相同，即RESPECT NULLS。 同样，标准中用于nth_value的FROM FIRST或FROM LAST选项没有实现： 只有支持默认的FROM FIRST行为（你可以通过反转ORDER BY的排序达到FROM LAST的结果）。

cume_dist计算小于等于当前行及其平级行的分区行所占的分数，而percent_rank计算小于当前行的分区行所占的分数，假定当前行不存在于该分区中。