一、开窗函数

MySQL 8.0 关于开窗函数的官方文档

1. 介绍

窗口函数也称为OLAP函数(分析函数)，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。

2. 需求场景

当在业务中，既要显示聚合前的数据又要显示聚合后的数据，这时我们可以使用开窗函数来实现。

具体实例：下表是关于一年中每个月每个员工的工资记录表（Payroll_records），现有需求是在保留原表数据基础上，增加一列累计工资（salary_accumulation）累计的计算一年的工资。

原始表

目标表

3. 语法介绍

（1）含义

     窗口函数：窗口 + 函数
     窗口： 函数运行时 计算的数据集的范围
	 函数：运行时的函数
	 聚合函数：COUNT,SUM,MIN,MAX,AVG
	 内置窗口函数：
			   1）取值
				  FIRST_VALUE：取窗口第一个值；
				  LAST_VALUE：取窗口最后一个值；
			   2）串行
				  LEAD:窗口内 向下 第n行的值；
	              LAG：窗口内 向上 第n行的值；
			   3）排序
				  NTILE：把数据平均分配 指定 N个桶 ，如果不能平均分配 ，优先分配到 编号 小的里面；
		          RANK: 从1 开始 ， 按照顺序 相同会重复 名次会留下 空的位置 生成组内的记录编号；
	              ROW_NUMBER： 从1 开始 ， 按照顺序 生成组内的记录编号;
	              DENSE_RANK：从1 开始 ， 按照顺序 生成组内的记录编号 相同会重复 名次不会会留下空的位置；
				  CUME_DIST
				  PERCENT_RANK
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

（2）语法结构

	 函数 over([partition by xxx,...] [order by xxx,...] )
		 over() :以谁进行开窗 【table】
		 partition by： 以谁进行分组 【group by column】
		 order by: 以谁进行排序 【column】
1
2
3
4

（3）实例说明

我们以每个服务器每天的累计启动次数为例来介绍该函数，sql表如下：

1.聚合函数 sum简单使用

select 
     name,
	 dt,
	 cnt,
     sum(cnt) over (partition by name order by dt ) as cnt_all
from linux;

# 解释：该开窗函数是以linux开窗，以name进行分组,以dt排序，对cnt（每天的启动次数）加和.
# 即1+6+13+15+18+28+32
1
2
3
4
5
6
7
8
9

结果如下：

2.聚合函数 sum复杂使用

select name,
       dt,
       cnt,
       sum(cnt) over (partition by name order by dt )	as sum_all,
       sum(cnt) over (partition by name order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )	as sum_all1,
       sum(cnt) over (partition by name order by dt ROWS BETWEEN 3 PRECEDING AND CURRENT ROW )	as sum_all2,
       sum(cnt) over (partition by name order by dt ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING )	as sum_all3,
       sum(cnt) over (partition by name order by dt ROWS BETWEEN 3 PRECEDING AND UNBOUNDED FOLLOWING)	as sum_all4,
       sum(cnt) over (partition by name order by dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )	as sum_all5
from linux;
 
# 解释：
#  		BETWEEN …… AND：哪行与哪行之间；
#			UNBOUNDED：无限制；
#     PRECEDING：前面的行，当前面加数字n时,代表窗口从当前行上数n行开始；
#			CURRENT ROW ：当前行；
#			FOLLOWING：后面的行，当前面加数字n时,代表窗口从当前行下数n行开始
 
#		1、sum_all:与上一个需求相同，实现的就是从头到尾的累加；
 
#		2、sum_all1:窗口范围会随着当前行的变化而发生变化，由上面的三个词的语法可以看出：
#     当执行到第1行时，窗口只有第1行本身；当到第2行时，窗口变为1，2两行，此时sum结果就是1+5=6
#			以此类推，最后的结果如sum_all1。
 
#		3、sum_all2:窗口从当前行的前三行开始到当前行，例如当前行为第5行时，前三行是2，3，4行
#		所以是5+7+2+3=17；
 
#		4、sum_all3:窗口从当前行的前三行开始到当前行的下一行，例如当前行为第2行时，前三行只有第1行
#		所以是1+2+7=13；
 
#		5、sum_all4:窗口从当前行的前三行开始到向下无限制，例如当前行为第7行时，前三行是4，5，6
#		下面没有其余行，所以从第4加到7行：2+3+4+10+=19；
 
#		6、sum_all5:窗口上下无限制，所以都是所有数加和1+5+7+2+3+10+4=32；
 
#		注意：开窗函数的聚合函数是sum(cnt)，因此累加都是对于cnt的值来说的。与此同时，也要明白
#		加和的过程要一行一行的，因为一些情况是相对于当前行的，所以窗口大小是动态的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

结果如下：

3.内置窗口函数 NTITLE

select name,
       dt,
       cnt,
       sum(cnt) over (partition by name order by dt ) as sum_all,
       ntile(2) over (partition by name order by dt ) as n2,
       ntile(3) over (partition by name order by dt ) as n3
from linux;
 
 
# 解释：
#		ntitle: 把数据平均分配 指定 N个桶 ，如果不能平均分配 ，优先分配到 编号 小的里面；
#		Eg:n2这列是将窗口分为两类，由于不能均分所以1号有四个2号有3个。
1
2
3
4
5
6
7
8
9
10
11
12

结果如下：

4.内置窗口函数 RANK、ROW_NUMBER、DENSE_RANK

select name,
       dt,
       cnt,
       sum(cnt) over (partition by name order by dt )           as sum_all,
       RANK() over (partition by name order by cnt desc )       as rk,
       ROW_NUMBER() over (partition by name order by cnt desc)  as rw,
       DENSE_RANK() over (partition by name order by cnt desc ) as d_rk
from linux;
 
 
#	解释：
#		我又插入了一些数据来体现三者的不同。
#   RANK:  从1 开始，按照顺序相同会重复名次会留下空的位置 生成组内的记录编号，如rk列的446；
#   ROW_NUMBER： 从1 开始，按照顺序生成组内的记录编号，第456行直接顺序记录的编号；
#   DENSE_RANK：从1 开始，按照顺序生成组内的记录编号相同会重复名次不会会留下空的位置，如44；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结果如下：

注意：这三种内置函数只是赋予编号并不是直接能够排列，需要over()中的order by，当删除上述代码中的desc时，编号就会变为降序，注意下面的rk：

select name,
       dt,
       cnt,
       sum(cnt) over (partition by name order by dt )    as sum_all,
       RANK() over (partition by name order by cnt )    as rk,
       ROW_NUMBER() over (partition by name order by cnt desc)  as rw,
       DENSE_RANK() over (partition by name order by cnt desc ) as d_rk
from linux;
1
2
3
4
5
6
7
8

结果如下：

5.内置窗口函数 LEAD、LAG

select name,
       dt,
       cnt,
       sum(cnt) over (partition by name order by dt )                  as sum_all,
       LEAD(dt, 1, "9999-99-99") over (partition by name order by dt ) as lead_alias,
       LAG(dt, 1, "9999-99-99") over (partition by name order by dt )  as lag_alias
from linux;
 
#	解释:
#		LEAD:窗口内 向下 第n行的值
#		LAG：窗口内 向上 第n行的值
#		参数1：要取值的列
#		参数2：取上数或者下数第几行的值	
#		参数3：如果没有值，则用参数3的值代替，例如LAG函数，lag_alias列 第1行上面取不到，
# 	则用9999-99-99；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

6.内置窗口函数FIRST_VALUE、LAST_VALUE

select name,
       dt,
       cnt,
       sum(cnt) over (partition by name order by dt )         as sum_all,
       FIRST_VALUE(cnt) over (partition by name order by dt ) as fv,
       LAST_VALUE(cnt) over (partition by name order by dt )  as lv
from linux;
 
# 解释
# 	FIRST_VALUE：取窗口第一个值
#	LAST_VALUE：去窗口的最后一个值
#	注意：这里也是一个动态的当第n行时，如果不做特殊指定，当到达第n行时，每组的窗口范围是
#	从第1行到第n行，因此第一组的窗口的FIRST_VALUE都是第1行的值，LAST_VALUE都是当前行的值
1
2
3
4
5
6
7
8
9
10
11
12
13

结果如下：