PostgreSQL窗口函数

一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的`OVER`子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理

留围冰

4988人浏览 · 2022-02-18 16:55:33

留围冰 · 2022-02-18 16:55:33 发布

1. 需求背景

假设有如下简单的公司员工工资表（dep_salary）

id	employee_name	department	salary
1	张三	HR部	12000.00
2	李四	HR部	15000.00
3	王五	HR部	18000.00
4	刘六	IT部	20000.00
5	赵七	IT部	15000.00
6	林八	IT部	16000.00
7	马九	IM部	20000.00
8	朱十	IM部	30000.00

如何查询各部门员工的工资排名（rank）？即查询出如下结果：

id	employee_name	department	salary	rank
3	王五	HR部	18000.00	1
2	李四	HR部	15000.00	2
1	张三	HR部	12000.00	3
8	朱十	IM部	30000.00	1
7	马九	IM部	20000.00	2
4	刘六	IT部	20000.00	1
5	赵七	IT部	15000.00	2
6	林八	IT部	15000.00	2

上述结果在查询结果后面加了一列，按各部门内员工的工资顺序排名，并且注意到同部门内工资相同的员工排名相同（例如 IT部门的赵七和林八）
要在查询每一行记录的同时查这些记录的汇总或其他处理结果，单纯使用聚集函数很难实现，但使用所谓窗口函数则很容易。例如上面按部门查询员工工资排名的SQL为：

SELECT
	*,
	rank() OVER (PARTITION BY department ORDER BY salary DESC)
FROM
	dep_salary

该查询中 rank()即为窗口函数，后跟OVER子句，表示在哪些查询结果行上应用该函数。下面具体来看窗口函数的定义和用法。

2. 概述

窗口函数（Window Function） 本身是聚集类函数，具有对特定结果行聚合处理的功能，但是窗口函数并不会使多行被聚集成一个单独的输出行，而是在函数计算的每一行都输出相应的结果，这与通常的非窗口聚集函数不同 $^{[1]}$ ；此外，窗口函数作用在由关键字 OVER 划定（“划定”一词引用自参考文章[4]，个人认为使用很恰当）的所谓 窗口 行上，这些行就是窗口函数的作用域 $^{[4]}$ 。如下图，这些行是查询结果的整体或一部分，就像是将原本完整的查询的结果行按规则划分为多块，每一块就像是一个窗口，窗口函数分别针对每个窗口的行进行计算。
窗口示意图
下面先通过比较规范的定义确定使用窗口函数的语法。

3. 语法

一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理 $^{[1]}$ 。根据窗口定义的方式可将窗口函数调用语法分为直接定义和先定义后引用两类。窗口定义的具体实现在第4节窗口定义中讨论，下面先从整体看窗口函数调用的语法。

3.1 直接定义

直接定义（个人理解的一种说法，非普遍术语）指的是将窗口的定义语句直接放在OVER括号里面，语法形如下：

窗口函数名(函数参数) OVER (窗口定义)

规范语法如下：

function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
或
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

方括号内FILTER条件部分是可选的，在此不讨论。OVER后面的window_definition就是窗口定义语句。例如在上面查询各部门员工工资的SQL中，rankk()是窗口函数，其不含任何参数，窗口定义由 PARTITION BY和ORDER BY组成。

3.2 先定义后引用

另一种方式是将窗口定义和使用分开了，即用WINDOW关键字定义窗口，然后再在SELECT查询的窗口函数中引用该窗口。
窗口定义语法形为：

WINDOW 窗口名 AS (窗口定义) [,…]

窗口使用语法为：

窗口函数名(函数参数) OVER (窗口名)

OVER子句的括号是可选的，去掉后语法为：

窗口函数名(函数参数) OVER 窗口名

完整的定义和使用语法如下：

SELECT 窗口函数名(函数参数) OVER 窗口名 FROM 表名 WINDOW 窗口名 AS (窗口定义) [,…]

可以用一个WINDOW关键字同时定义多个窗口，这时每个窗口用逗号分隔。如上面语法后面的方括号所示，假如要定义两个窗口则为：

SELECT 窗口函数名1(函数参数) OVER 窗口名1, 窗口函数名2(函数参数) OVER 窗口名2 FROM 表名
WINDOW 窗口名1 AS (窗口定义1), 窗口名2 AS (窗口定义2)

规范语法如下：

SELECT function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name FROM from_item WINDOW window_name AS ( window_definition ) [, …]
或
SELECT function_name ( * ) [ FILTER ( WHERE filter_clause ) ] FROM from_item OVER window_name

更加完整的语法可参考 PostgreSQL 13 中文文档: SELECT语法。
例如在上面查询各部门员工工资的SQL可修改为

SELECT
	*,
	rank() OVER w1
FROM
	dep_salary
WINDOW w1 AS (PARTITION BY department ORDER BY salary DESC)

当窗口函数多次作用在相同的窗口定义上时，这种方式能避免窗口重复定义。假如我们不仅要查各部门员工的工资排名，还要查各部门工资的平均值、最大值和最小值，则可以写为：

SELECT
	*,
	avg(salary) OVER dep,
	max(salary) OVER dep,
	min(salary) OVER dep,
	rank() OVER(PARTITION BY department ORDER BY salary DESC)
FROM
	dep_salary
WINDOW dep AS (PARTITION BY department)

结果如下：

id	employee_name	department	salary	avg	max	min	rank
3	王五	HR部	18000.00	15000.000000000000	18000.00	12000.00	1
2	李四	HR部	15000.00	15000.000000000000	18000.00	12000.00	2
1	张三	HR部	12000.00	15000.000000000000	18000.00	12000.00	3
8	朱十	IM部	30000.00	25000.000000000000	30000.00	20000.00	1
7	马九	IM部	20000.00	25000.000000000000	30000.00	20000.00	2
4	刘六	IT部	20000.00	16666.666666666667	30000.00	20000.00	1
5	赵七	IT部	15000.00	16666.666666666667	20000.00	15000.00	2
6	林八	IT部	15000.00	16666.666666666667	20000.00	15000.00	2

从语法上看，窗口函数的使用包括 窗口定义 和 窗口函数选用 两部分。窗口函数包括内置窗口函数和聚集函数，在第5节讨论，先来看更重要的如何定义窗口。

4. 窗口定义

窗口定义确定了查询结果的哪些行被分组到同一个窗口中进行计算，要定义窗口，首先介绍两个概念：

当前行
窗口定义要按行确定窗口，则每一个要确定窗口的行可以称为当前行。
窗口帧
对于每一行，在它的分区中的行集被称为这一行的窗口帧。要理解这个概念，首先理解“帧”。“帧”是一个量词，表示一个单元，比如常说的“一帧一帧播放”。窗口帧中“帧”单元就是“行”，所以窗口帧就是“窗口行”，和当前行在同一个分区的每一行可看作“一帧”。例外的是，分区中的行还可以通过下述的帧子句进一步筛选，筛选后的结果可能少于分区行数，因此一行实际窗口帧的行数小于等于分区行数，也即是分区行的一个子集 $^{[6]}$ 。

下面说明窗口定义的三种元素分区、排序和帧子句，语法如下 $^{[2]}$ ：

分区：[ PARTITION BY expression [, …] ]
排序：[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, …] ]
帧子句：[ frame_clause ]

4.1 分区

PARTITION BY选项将查询的结果行分组成为分区，一个分区即一个窗口。对每一行来说，分区字段的值与当前行相同的行就是当前行的窗口帧，会被分组到同一个分区。所有的窗口函数（例如下述内置窗口函数）都支持分区，它们会独立地处理每一个分区。PARTITION BY作用可类比于 GROUP BY，但不同的是PARTITION BY对分区内的每一行都有一个计算结果，而GROUP BY对每个分组汇总后只输出一行。
上面查询各部门员工工资的平均值、最大值和最小值的，就需要按部门分区（PARTITION BY department），每个部门的员工被分组到同一个分区，聚集函数avg()、max()、min()作为窗口函数，对分区内员工的工资分别求平均值、最大值和最小值。

SELECT
	*,
	avg(salary) OVER dep,
	max(salary) OVER dep,
	min(salary) OVER dep
FROM
	dep_salary
WINDOW dep AS (PARTITION BY department)

结果如下：

id	employee_name	department	salary	avg	max	min
2	李四	HR部	15000.00	15000.000000000000	18000.00	12000.00
1	张三	HR部	12000.00	15000.000000000000	18000.00	12000.00
3	王五	HR部	18000.00	15000.000000000000	18000.00	12000.00
7	马九	IM部	20000.00	25000.000000000000	30000.00	20000.00
8	朱十	IM部	30000.00	25000.000000000000	30000.00	20000.00
5	赵七	IT部	15000.00	16666.666666666667	20000.00	15000.00
4	刘六	IT部	20000.00	16666.666666666667	20000.00	15000.00
6	林八	IT部	15000.00	16666.666666666667	20000.00	15000.00

4.2 排序

假如不区分部门，求全公司员工的公司排名，就不需要按部门分区，只需要按工资排名即可。为了得到连续的排名，这里我们使用另一个排序函数dense_rank()（解释参见下述内置窗口函数），否则由于相同名词的存在会出现排名间隔的情况：

SELECT
	*,
	dense_rank() OVER(ORDER BY salary DESC) 
FROM
	dep_salary

结果如下：

id	employee_name	department	salary	rank
8	朱十	IM部	30000.00	1
7	马九	IM部	20000.00	2
4	刘六	IT部	20000.00	2
3	王五	HR部	18000.00	3
6	林八	IT部	15000.00	4
5	赵七	IT部	15000.00	4
2	李四	HR部	15000.00	4
1	张三	HR部	12000.00	5

由ORDER BY定义的窗口，窗口帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行 $^{[1]}$ 。排名函数效果不明显，我们换成求和函数sum()可以明显看出ORDER BY的窗口帧表现出的这两条规律：

SELECT
	*,
	sum(salary) OVER(ORDER BY salary DESC) 
FROM
	dep_salary

结果如下：

id	employee_name	department	salary	sum
8	朱十	IM部	30000.00	30000.00
7	马九	IM部	20000.00	70000.00
4	刘六	IT部	20000.00	70000.00
3	王五	HR部	18000.00	88000.00
6	林八	IT部	15000.00	133000.00
5	赵七	IT部	15000.00	133000.00
2	李四	HR部	15000.00	133000.00
1	张三	HR部	12000.00	145000.00

由于没有指定分区，全部结果行就是分区，因此sum()在整个结果行上起作用。可以看出

rank列的每个值都是前一个值加上当前行的值，如上所述“窗口帧包括从分区开始到当前行的所有行”
salary相同的行的值相同，亦如上所述“以及后续任何与当前行在ORDER BY子句上相等的行”。

如果ORDER BY被忽略，则默认帧包含整个分区中所有的行：

SELECT
	*,
	sum(salary) OVER() 
FROM
	dep_salary

结果如下：

id	employee_name	department	salary	sum
7	马九	IM部	20000.00	145000.00
8	朱十	IM部	30000.00	145000.00
4	刘六	IT部	20000.00	145000.00
5	赵七	IT部	15000.00	145000.00
1	张三	HR部	12000.00	145000.00
2	李四	HR部	15000.00	145000.00
3	王五	HR部	18000.00	145000.00
6	林八	IT部	15000.00	145000.00

此时，每一行的窗口帧都相同，即包括所有的行，则窗口函数计算的每一行结果都相同，如rank列的值。

4.3 帧子句

待定

5. 内置窗口函数

函数描述
`row_number` () → `bigint`返回其分区内的当前行数，从1开始计数。
`rank` () → `bigint`返回当前行的排名，包含间隔;即对等组中第一行的`row_number`。
`dense_rank` () → `bigint`返回当前行的排名，不包括间隔;这个功能有效地计数对等组。
`percent_rank` () → `double precision`返回当前行的相对排名，即(`rank` - 1) / (总的分区行数 - 1)。因此，该值的范围从0到1(包含在内)。
`cume_dist` () → `double precision`返回累积分布，也就是(当前行之前或对等的分区行数)/(总的分区行数)。取值范围为1/`N` 到 1。
`ntile` ( `num_buckets` `integer` ) → `integer`返回一个从1到参数值的整数，并将分区划分为尽可能相等的值。
`lag` ( `value` `anyelement` [, `offset` `integer` [, `default` `anyelement` ]] ) → `anyelement`返回分区中在当前行之前 `offset` 行的 `value` ;如果没有这样的行，则返回 `default` (必须与 `value` 具有相同的类型)。 `offset` 和 `default` 都是针对当前行求值的。如果省略， `offset` 默认为1， `default` 为`NULL`。
`lead` ( `value` `anyelement` [, `offset` `integer` [, `default` `anyelement` ]] ) → `anyelement`返回分区中在当前行之后 `offset`行的`value`；如果没有这样的行，则返回 `default` (必须与 `value` 具有相同的类型)。 `offset` 和 `default` 都是针对当前行求值的。如果省略， `offset` 默认为1， `default` 为`NULL`。
`first_value` ( `value` `anyelement` ) → `anyelement`返回在窗口框架的第一行求得的 `value`。
`last_value` ( `value` `anyelement` ) → `anyelement`返回在窗口框架的最后一行求得的 `value`。
`nth_value` ( `value` `anyelement`, `n` `integer` ) → `anyelement`返回在窗口框架的第 `n` 行求得的 `value` (从1开始计数);如果没有这样的行，则返回`NULL`。