PostgreSQL教程-sql语法-值表达式-窗口函数调用

PostgreSQL教程-sql语法-值表达式-窗口函数调用

一个窗口函数调用表示在一个查询选择的行的某个部分上应用一个聚合类的函数。和非窗口聚合调用不同，这不会被约束为将被选择的行分组为一个单一的输出行 — 在查询输出中每一个行仍保持独立。 但是，根据窗口函数调用的分组规范(PARTITION BY列表)， 窗口函数可以访问将成为当前行组的一部分的所有行。 一个窗口函数调用的语法是下列之一：

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name

function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

其中window_definition的语法是

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

[ frame_clause ]

而可选的frame_clause是下列之一

{ RANGE | ROWS } frame_start

{ RANGE | ROWS } BETWEEN frame_start AND frame_end

其中frame_start和frame_end可以是下面形式中的一种

UNBOUNDED PRECEDING

value PRECEDING

CURRENT ROW

value FOLLOWING

UNBOUNDED FOLLOWING

这里，expression表示任何自身不含有窗口函数调用的值表达式。

window_name是对定义在查询的WINDOW子句中的一个命名窗口声明的引用。还可以使用在WINDOW子句中定义命名窗口的相同语法在圆括号内给定一个完整的window_definition，详见SELECT参考页。值得指出的是，OVER wname并不严格地等价于OVER (wname ...)，后者表示复制并修改窗口定义，并且在被引用窗口声明包括一个帧子句时会被拒绝。

PARTITION BY子句将查询的行分组成为分区，窗口函数会独立地处理它们。PARTITION BY工作起来类似于一个查询级别的GROUP BY子句，不过它的表达式总是只是表达式并且不能是输出列的名称或编号。如果没有PARTITION BY，该查询产生的所有行被当作一个单一分区来处理。ORDER BY子句决定被窗口函数处理的一个分区中的行的顺序。它工作起来类似于一个查询级别的ORDER BY子句，但是同样不能使用输出列的名称或编号。如果没有ORDER BY，行将被以未指定的顺序被处理。

frame_clause指定构成窗口帧的行集合，它是当前分区的一个子集，窗口函数将作用在该帧而不是整个分区。 帧可以被指定为RANGE或ROWS模式，在两种情况中它都从frame_start运行到frame_end。如果frame_end被忽略，它默认运行到CURRENT ROW。

UNBOUNDED PRECEDING的一个frame_start表示该帧开始于分区的第一行，类似地UNBOUNDED FOLLOWING的一个frame_end表示该帧结束于分区的最后一行。

在RANGE模式下， CURRENT ROW的一个frame_start表示该帧开始于当前行的第一个平级行(一个被ORDER BY认为与当前行等效的行)，而CURRENT ROW的一个frame_end表示该帧结束于最后一个等效的ORDER BY平级行。在ROWS模式下，CURRENT ROW仅表示当前行。

value PRECEDING和value FOLLOWING情况当前只在ROWS模式中被允许。它们指示帧开始或结束于当前行之前或之后的指定数量的行。value必须是一个不包含任何变量、聚合函数或窗口函数的整数表达式。该值不能为空或负，但是可以为零，零表示只选择当前行。

默认的帧选项是RANGE UNBOUNDED PRECEDING，它和RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。如果使用ORDER BY，这会把该帧设置为从分区开始一直到当前行的最后一个ORDER BY平级行的所有行。如果不使用ORDER BY，分区中所有的行都被包括在窗口帧中，因为所有行都成为了当前行的平级行。

限制是frame_start不能为UNBOUNDED FOLLOWING、frame_end不能为UNBOUNDED PRECEDING并且在上述列表中frame_end的选择不能早于frame_start的选择出现 — 例如RANGE BETWEEN CURRENT ROW AND value PRECEDING是不被允许的。

如果指定了FILTER，那么只有对filter_clause计算为真的输入行会被交给该窗口函数，其他行会被丢弃。只有是聚合的窗口函数才接受FILTER 。

内建的窗口函数在表 9.57中描述。 用户可以增加其他的窗口函数。还有，任何内建或用户定义的通用或统计聚合 函数可以被用作窗口函数。(有序聚合和假设集聚合目前不能用作窗口函数。)

使用*的语法被用来把参数较少的聚合函数当作窗口函数调用， 例如count(*) OVER (PARTITION BY x ORDER BY y)。 星号(*)通常通常不用于窗口特定的函数。窗口特定的函数不允许在函数参数列表中使用DISTINCT或ORDER BY。

只有在SELECT列表和查询的ORDER BY子句中才允许窗口函数调用。

更多关于窗口函数的信息可以在第 3.5 节、第 9.21 节以及第 7.2.5 节中找到。