postgres之窗口函数
本博客内容来自于官方文档:http://www.postgres.cn/docs/10/functions-window.html#FUNCTIONS-WINDOW-TABLE
一、窗口函数介绍
一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行,这与通常的非窗口聚集函数不同。取而代之,行保留它们独立的标识。在这些现象背后,窗口函数可以访问的不仅仅是查询结果的当前行。
下面是一个例子用于展示如何将每一个员工的薪水与他/她所在部门的平均薪水进行比较:SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname | empno | salary | avg
-----------+-------+--------+-----------------------
develop | 11 | 5200 | 5020.0000000000000000
develop | 7 | 4200 | 5020.0000000000000000
develop | 9 | 4500 | 5020.0000000000000000
develop | 8 | 6000 | 5020.0000000000000000
develop | 10 | 5200 | 5020.0000000000000000
personnel | 5 | 3500 | 3700.0000000000000000
personnel | 2 | 3900 | 3700.0000000000000000
sales | 3 | 4800 | 4866.6666666666666667
sales | 1 | 5000 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
最开始的三个输出列直接来自于表empsalary
,并且表中每一行都有一个输出行。第四列表示对与当前行具有相同depname
值的所有表行取得平均值(这实际和非窗口avg
聚集函数是相同的函数,但是OVER
子句使得它被当做一个窗口函数处理并在一个合适的窗口帧上计算。)。
一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER
子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。OVER
子句决定究竟查询中的哪些行被分离出来由窗口函数处理。OVER
子句中的PARTITION BY
子句指定了将具有相同PARTITION BY
表达式值的行分到组或者分区。对于每一行,窗口函数都会在当前行同一分区的行上进行计算。
我们可以通过OVER
上的ORDER BY
控制窗口函数处理行的顺序(窗口的ORDER BY
并不一定要符合行输出的顺序。)。下面是一个例子:SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
epname | empno | salary | rank
-----------+-------+--------+------
develop | 8 | 6000 | 1
develop | 10 | 5200 | 2
develop | 11 | 5200 | 2
develop | 9 | 4500 | 4
develop | 7 | 4200 | 5
personnel | 2 | 3900 | 1
personnel | 5 | 3500 | 2
sales | 1 | 5000 | 1
sales | 4 | 4800 | 2
sales | 3 | 4800 | 2
(10 rows)
如上所示,rank
函数在当前行的分区内按照ORDER BY
子句的顺序为每一个可区分的ORDER BY
值产生了一个数字等级。rank
不需要显式的参数,因为它的行为完全决定于OVER
子句。
一个窗口函数所考虑的行属于那些通过查询的FROM
子句产生并通过WHERE
、GROUP BY
、HAVING
过滤的“虚拟表”。例如,一个由于不满足WHERE
条件被删除的行是不会被任何窗口函数所见的。在一个查询中可以包含多个窗口函数,每个窗口函数都可以用不同的OVER
子句来按不同方式划分数据,但是它们都作用在由虚拟表定义的同一个行集上。
我们已经看到如果行的顺序不重要时ORDER BY
可以忽略。PARTITION BY
同样也可以被忽略,在这种情况下会产生一个包含所有行的分区。
这里有一个与窗口函数相关的重要概念:对于每一行,在它的分区中的行集被称为它的窗口帧。 一些窗口函数只作用在窗口帧中的行上,而不是整个分区。默认情况下,如果使用ORDER BY
,则帧包括从分区开始到当前行的所有行,以及后续任何与当前行在ORDER BY
子句上相等的行。如果ORDER BY
被忽略,则默认帧包含整个分区中所有的行。 [4] 下面是使用sum
的例子:SELECT salary, sum(salary) OVER () FROM empsalary;
salary | sum
--------+-------
5200 | 47100
5000 | 47100
3500 | 47100
4800 | 47100
3900 | 47100
4200 | 47100
4500 | 47100
4800 | 47100
6000 | 47100
5200 | 47100
(10 rows)
如上所示,由于在OVER
子句中没有ORDER BY
,窗口帧和分区一样,而如果缺少PARTITION BY
则和整个表一样。换句话说,每个合计都会在整个表上进行,这样我们为每一个输出行得到的都是相同的结果。但是如果我们加上一个ORDER BY
子句,我们会得到非常不同的结果:SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
salary | sum
--------+-------
3500 | 3500
3900 | 7400
4200 | 11600
4500 | 16100
4800 | 25700
4800 | 25700
5000 | 30700
5200 | 41100
5200 | 41100
6000 | 47100
(10 rows)
这里的合计是从第一个(最低的)薪水一直到当前行,包括任何与当前行相同的行(注意相同薪水行的结果)。
窗口函数只允许出现在查询的SELECT
列表和ORDER BY
子句中。它们不允许出现在其他地方,例如GROUP BY
、HAVING
和WHERE
子句中。这是因为窗口函数的执行逻辑是在处理完这些子句之后。另外,窗口函数在非窗口聚集函数之后执行。这意味着可以在窗口函数的参数中包括一个聚集函数,但反过来不行。
如果需要在窗口计算执行后进行过滤或者分组,我们可以使用子查询。例如:
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
上述查询仅仅显示了内层查询中rank
低于3的结果。
当一个查询涉及到多个窗口函数时,可以将每一个分别写在一个独立的OVER
子句中。但如果多个函数要求同一个窗口行为时,这种做法是冗余的而且容易出错的。替代方案是,每一个窗口行为可以被放在一个命名的WINDOW
子句中,然后在OVER
中引用它。
例如:SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
二、窗口函数调用
一个窗口函数调用表示在一个查询选择的行的某个部分上应用一个聚合类的函数。和非窗口聚合调用不同,这不会被约束为将被选择的行分组为一个单一的输出行 — 在查询输出中每一个行仍保持独立。 但是,根据窗口函数调用的分组规范(PARTITION BY
列表), 窗口函数可以访问将成为当前行组的一部分的所有行。 一个窗口函数调用的语法是下列之一:
function_name
([expression
[,expression
... ]]) [ FILTER ( WHEREfilter_clause
) ] OVERwindow_name
function_name
([expression
[,expression
... ]]) [ FILTER ( WHEREfilter_clause
) ] OVER (window_definition
)function_name
( * ) [ FILTER ( WHEREfilter_clause
) ] OVERwindow_name
function_name
( * ) [ FILTER ( WHEREfilter_clause
) ] OVER (window_definition
)
其中window_definition
的语法是
[existing_window_name
] [ PARTITION BYexpression
[, ...] ] [ ORDER BYexpression
[ ASC | DESC | USINGoperator
] [ NULLS { FIRST | LAST } ] [, ...] ] [frame_clause
]
而可选的frame_clause
是下列之一
{ RANGE | ROWS }frame_start
{ RANGE | ROWS } BETWEENframe_start
ANDframe_end
其中frame_start
和frame_end
可以是下面形式中的一种
UNBOUNDED PRECEDINGvalue
PRECEDING CURRENT ROWvalue
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 节和第 4.2.8 节),这些函数将在任何分组、聚集和HAVING
过滤被执行之后被计算。也就是说如果查询使用了任何聚集、GROUP BY
或HAVING
,则窗口函数看到的行是分组行而不是来自于FROM
/WHERE
的原始表行。
当多个窗口函数被使用,所有在窗口定义中有句法上等效的PARTITION BY
和ORDER BY
子句的窗口函数被保证在数据上的同一趟扫描中计算。因此它们将会看到相同的排序顺序,即使ORDER BY
没有唯一地决定一个顺序。但是,对于具有不同PARTITION BY
或ORDER BY
定义的函数的计算没有这种保证(在这种情况中,在多个窗口函数计算之间通常要求一个排序步骤,并且并不保证保留行的顺序,即使它的ORDER BY
把这些行视为等效的)。
目前,窗口函数总是要求排序好的数据,并且这样查询的输出总是被根据窗口函数的PARTITION BY
/ORDER BY
子句的一个或者另一个排序。但是,我们不推荐依赖于此。如果你希望确保结果以特定的方式排序,请显式使用顶层的ORDER BY
子句。
四、通用窗口函数
在表 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
。 其它窗口帧声明可以用来获得其它的效果。