用MySQL实现窗口函数操作详解
最编程
2024-07-23 07:14:53
...
一、开窗函数
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都是当前行的值
SQL文件在这里,可以自己下载试一下https://download.****.net/download/Mr__Sun__/85172998