详解MySQL窗口函数（MySQL高级）

1.什么是窗口函数

        窗口函数又被称为开窗函数，与Oracle 窗口函数类似，属于MySQL的一大特点. 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

 1.1序号函数

        序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。可以使用partition by 进行分组，使用order by 进行排序，语法格式如下：

row_number()\rank()\dense_rank() over () 
  partition by ... 
  order by ... 

        比如现如今我想要对每个部门的员工按照薪资排序，并给出排名。具体语法如下：

select 
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn 
from employee;

在这里dname, ename, salary都是参数名 row_number() over(partition by dname order by salary desc) as rn （别名）from employee（表名）;这里row_number排序数值重复时，也会按照1，2，3的顺序来排序，两者的工资数相同也会有一个人排在后一位。

        如果想要将数值相同的人排名相同并且将后面序号省略即为1，2，2，4方式排列，可以使用rank()来解决，具体语法如下所示：

select 
dname,
ename,
salary,
rank() over(partition by dname order by salary desc) as rn 
from employee;

在这里dname, ename, salary都是参数名 、rank() over(partition by dname order by salary desc) as rn （别名）from employee（表名）;这里rank排序数值重复时，会按照1，2，2，4的顺序来排序，两者的工资数相同时会拥有同一个排名，并且隔开相应的排名。

         如果想要将数值相同的人排名相同且将后面序号不省略即为1，2，2，3，4方式排列，可以使用dense_rank()来解决，具体语法如下所示：

select 
dname,
ename,
salary,
dense_rank() over(partition by dname order by salary desc) as rn 
from employee;

在这里dname, ename, salary都是参数名 、dense_rank() over(partition by dname order by salary desc) as rn （别名）from employee（表名）;这里dense_rank排序数值重复时，会按照1，2，2，3，4的顺序来排序，两者的工资数相同时会拥有同一个排名，并且不会隔开相应的排名。

1.2开窗聚合函数

        在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值。

select  
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate) as pv1 
from employee;

1.3分布函数

        分布函数- CUME_DIST（累积分布值）用途：分组内小于、等于当前rank值的行数 / 分组内总行数  应用场景：查询小于等于当前某项参数的比例

select  
 dname,
 ename,
 salary,
 cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
 cume_dist() over(partition by dname order by salary) as rn2 
from employee;

下面我来详细讲解一下此代码。

1. 第一个cume_dist()函数（别名rn1）的窗口定义是OVER(ORDER BY salary)，这意味着它不考虑部门(dname)的划分，而是将所有员工的薪资进行排序，并计算每个员工薪资在整个薪资列表中的累积分布值。累积分布值（Cumulative Distribution）是指当前行之前的行（包括当前行）占整个结果集的比例。因此，rn1的值会在整个员工薪资范围内从1（薪资最低的员工）到某个值（最大薪资的员工可能不是1，因为可能存在薪资相同的员工）递增。

2. 第二个cume_dist()函数（别名rn2）的窗口定义是OVER(PARTITION BY dname ORDER BY salary)，这意味着首先按照部门(dname)对员工进行分组，然后在每个部门内部按照薪资(salary)进行排序，最后计算每个员工薪资在其所在部门薪资列表中的累积分布值。这样，rn2的值会在每个部门内部独立计算，每个部门的最低薪资员工都会有一个rn2值为1，而最高薪资员工（或薪资最高的几个员工，如果薪资相同）的rn2值则取决于该部门内薪资的分布情况。

1.4前后函数-LAG和LEAD

        用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值 应用场景：查询前1名同学的成绩和当前同学成绩的差值

lag用法：

select 
 dname,
 ename,
 hiredate,
 salary,
 lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
from employee;

指定了往上第1行的值，default(默认值)为'2000-01-01'                          

第一行，往上1行为null,因此取默认值 '2000-01-01'                          

第二行，往上1行值为第一行值，2021-11-01                          

第三行，往上1行值为第二行值，2021-11-02  

lead用法：

select 
 dname,
 ename,
 hiredate,
 salary,
 lead(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
from employee;

这与上面类似，只不过是向后一位比较。