MySQL开窗函数

关于MySQL8.0中开窗函数的学习

isMr_Sun

8736人浏览 · 2022-04-18 22:52:55

isMr_Sun · 2022-04-18 22:52:55 发布

一、开窗函数

1. 介绍

该函数只有MySQL8.0版本才存在

MySQL 8.0 关于开窗函数的官方文档https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

2. 需求场景

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

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

原表 Payroll_records

目标表

3. 语法介绍

（1）含义

窗口函数：窗口 + 函数

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

（2）语法结构

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

（3）实例说明

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

聚合函数使用——SUM

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

# 解释：该开窗函数是以name开窗，以dt日期排序，对cnt（每天的启动次数）加和.
# 即1+6+13+15+18+28+32

聚合函数使用——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的值来说的。与此同时，也要明白
#		加和的过程要一行一行的，因为一些情况是相对于当前行的，所以窗口大小是动态的。

内置窗口函数——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个。

内置窗口函数——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；

注意：这三种内置函数只是赋予编号并不是直接能够排列，需要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;

内置窗口函数——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；

内置窗口函数——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都是当前行的值