MySQL开窗函数_isMr_Sun_mysql 开窗函数

irpas 02-07 6033

一、开窗函数 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_DISTPERCENT_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.csdn.net/download/Mr__Sun__/85172998