MySQL（五）窗口函数

概念

• 将数据进行分组，每个分组即是一个窗口，这和使用聚合函数时的group by分组类似
• 窗口函数会逐行计算，其重点是计算当前行与窗口内其他成员之间的关系，例如：组内排序，累积分布等
• 判断一个函数是不是窗口函数只需要看其是否有over关键字即可

窗口函数和聚合函数的区别

• 聚合函数（例如：sum/avg/min/max）会针对每个分组（窗口）聚合出一个结果（每一组返回一个结果）
• 窗口函数会对每一条数据进行计算，并不会使返回的数据变少（每一行返回一个结果）

窗口函数的分类

• 一类既可以做为聚合函数，也可以作为窗口函数，当函数单独使用时是聚合函数，当与over关键字同时使用时作为窗口函数
• 另一类是专用窗口函数，他们必须与 over 关键字同时使用

基础语法

• 窗口函数在使用时需要定义一个窗口（分组），然后对每一行应用窗口函数，正在计算的这行叫做“当前行”

1、定义

• 窗口函数通过over关键字和partition by关键字来定义分组，这里的partition by是分组的意思，和分区表没有任何关系

[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS <窗口滑动的数据范围> )

• <窗口滑动的数据范围> 用来限定[ 你要的操作] 所运用的数据的范围，具体有如下这些

当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

• 举例

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行

实例

• 现在有一张表wf_example

id	wind	val
1	win_A	1
2	win_A	2
3	win_A	2
4	win_A	3
5	win_A	3
6	win_A	3
7	win_B	100
8	win_B	200
9	win_B	300
10	win_B	400
11	win_B	500

（1）聚合函数

select wind,sum(val) from wf_example group by wind;

wind	sum(val)
win_A	14
win_B	1500

• sum按wind列对数据分组后求和，每组分别返回一条数据

（2）将sum()作为窗口函数使用

select wind,
sum(val) over() total_sum,
sum(val) over(partition by wind) group_sum
from wf_example;

wind	total_sum	group_sum
win_A	1514	14
win_A	1514	14
win_A	1514	14
win_A	1514	14
win_A	1514	14
win_A	1514	14
win_B	1514	1500
win_B	1514	1500
win_B	1514	1500
win_B	1514	1500
win_B	1514	1500

• sum后增加了一个over关键字，代表sum作为窗口函数，over关键字后面括号中即是窗口定义
• total_sum中，在over后面使用了一个空括号，没有明确的窗口划分，代表所有数据作为一组
• group_sum中，通过partition by wind表示通过wind列进行分组

2、命名窗口

• 当一个窗口被多次引用的时候，在每个over后面都写一遍定义就显得有些繁琐了，此场景可以通过命名窗口优化：一次定义，多次引用
• 命名窗口的定义是通过window wind_name as ()来进行定义的，括号内的部分就是原over子句后的窗口定义，在用over关键字调用窗口时，直接引用窗口名wind_name即可

select wind,
sum(val) over w group_sum   -- 通过名称 w 引用窗口
from wf_example
window w as (partition by wind);  -- 命名窗口定义

• 通常情况下使用时只需要直接引用窗口名称即可，有时需要对窗口进一步加工，例如排序等，可以用括号将窗口名括起来，后面跟上order by子句

专用窗口函数

• sum、count、max、min、avg等聚合函数都可以作为窗口函数使用，但专用窗口函数更常用
• 专用窗口函数必须搭配over关键字

函数	描述	语法
row_number()	没有参数，返回当前行在组内的位置编号，从1开始，order by子句会影响行的编号顺序，如果没有order by，那么行的编号是不确定的。另外，即使行完全相同，它们的编号也是不同的	row_number() over window
rank()	没有参数，返回当前行在组内的排序，排序带间隙（排名数字不连续，例如1,2,2,4,4），在partition by 后面可以跟上order by 子句来指定按某列排序	rank() over window
dense_rank()	没有参数，返回当前行在组内的排序，排序不带间隙（排名是连续的，例如1,2,2,3,3）	rank() over window
lag()	返回在当前窗口内，当前行的“前面N行”计算出的expr结果，如果没有满足条件的行，则返回default，其中参数N和default可以省略，如果省略了则默认N为1，default为null	lag(expr [, N [, default]])
lead()	返回在当前窗口内，当前行的“后面N行”计算出的expr结果，如果没有满足条件的行，则返回default，其中参数N和default可以省略，如果省略了则默认N为1，default为null	lead(expr [N [, default]])

lag()函数

lag(expr,N,default)

• expr：可以是列或者任何内置函数
• N：正数值，确定当前行之前的行数，如果省略则默认为1
• default：如果在当前行之前没有行N行的情况下，它是函数返回的默认值，如果缺少则默认为NULL

id	score
1	77
2	88
3	90
4	87

select *,lag(score,1,0) over(order by id) as score1
from Scores

id	score	score1
1	77	0
2	88	77
3	90	88
4	87	90

lead()函数

select *,lead(score,1,0) over(order by id) as score2
from Scores

id	score	score1	score2
1	77	0	88
2	88	77	90
3	90	88	87
4	87	90	0