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
]
^{[1]}
[1];此外,窗口函数作用在由关键字 OVER
划定(“划定”一词引用自参考文章[4],个人认为使用很恰当)的所谓 窗口
行上,这些行就是窗口函数的作用域
[
4
]
^{[4]}
[4]。如下图,这些行是查询结果的整体或一部分,就像是将原本完整的查询的结果行按规则划分为多块,每一块就像是一个窗口,窗口函数分别针对每个窗口的行进行计算。
下面先通过比较规范的定义确定使用窗口函数的语法。
3. 语法
一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER
子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。OVER
子句决定究竟查询中的哪些行被分离出来由窗口函数处理
[
1
]
^{[1]}
[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中,rank()
是窗口函数,其不含任何参数,窗口定义由 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 ] ^{[6]} [6]。
下面说明窗口定义的三种元素分区、排序和帧子句,语法如下 [ 2 ] ^{[2]} [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
]
^{[1]}
[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()
在整个结果行上起作用。可以看出
sum
列的每个值都是前一个值加上当前行的值,如上所述“窗口帧包括从分区开始到当前行的所有行”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 。 |
参考
[1] PostgreSQL 13 中文文档 3.5. 窗口函数
[2] PostgreSQL 13 中文文档 4.2.8. 窗口函数调用
[3] PostgreSQL 13 中文文档 9.22. 窗口函数
[4] CSDN 寒冰屋 博客:SQL ——窗口函数简介
[5] PostgreSQL 13 中文文档 SQL命令: SELECT
[6] CSDN 独孤尚良dugushangliang 博客:窗口函数相关的窗口帧(window frame)的概念及与over句中order by语句对窗口帧的影响