mysql8.0 中的窗口函数

1. 窗口函数概述

窗口函数又被称为开窗函数，是MySQL 8.0 新增窗口函数

聚合函数处理后多行变成一行；窗口函数处理后，多行还是多行。如下图所示：

所有的窗口函数语法格式都是一致的，如下：

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
)

-- 1. window_function：是窗口函数的名称

-- 2. expr 是参数，有些函数不需要参数；

-- 3. OVER子句包含三个选项
-- 3.1 分区（PARTITION BY）：PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
-- 3.2 排序（ORDER BY）：OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似 

假设有一张表，数据如下：

2. 序号函数

序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，都可以用来实现分组排序，并添加序号。

select 
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn_row_number, 
rank() over(partition by dname order by salary desc) as rn_rank, 
dense_rank() over(partition by dname order by salary desc) as rn_dense_rank
from employee;

执行结果如下：

从执行结果看，三者的排序方式有差异，大家可以根据自己的需求选择不同的窗口函数

当不加partition by 表示不进行分组，全局排序

3. 窗口聚合函数

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

select  
 dname,
 ename,
 salary,
 -- 带有order by
 sum(salary) over(partition by dname order by hiredate) as pv1, 
 -- 不带order by
 sum(salary) over(partition by dname) as pv2
from employee;

执行结果如下：

从执行结果来看，当窗口函数中有order by排序语句会进行累加操作，没有order by排序语句，默认把分组内的所有数据进行加和操作

执行sum加和时，我们还可以控制相加范围：

select  
 dname,
 ename,
 hiredate,
 salary,
-- 在进行sum相加的时候，从开头加到当前行
sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c1,
-- 在进行sum相加的时候，从前3行开始加到当前行
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row) as c2,
-- 在进行sum相加的时候，从前3行开始加到向后1行，也包含当前行
sum(salary) over(partition by dname order by hiredate rows between 3 preceding and 1 following) as c3,
-- 在进行sum相加的时候，从当前行到加到最后
sum(salary) over(partition by dname order by hiredate rows between current row and unbounded following) as c4
from employee;

-- unbounded preceding：开头
-- current row：当前行
-- 3 preceding：前3行
-- 1 following：向后1行
-- unbounded following：最后

执行结果如下：

以上操作，我们也可以用在avg、max、min等聚合函数

4. 分布函数 CUME_DIST

说明：分组内小于、等于当前rank值的行数 / 分组内总行数

-- 查询小于等于当前薪资（salary）的比例
select  
 dname,
 ename,
 salary,
 -- 没有partition语句 所有的数据位于一组
 cume_dist() over(order by salary) as rn1, 
 cume_dist() over(partition by dname order by salary) as rn2 
from employee;

/*
rn1: 没有partition,所有数据均为1组，总行数为12，
     第一行：小于等于3000的行数为3，因此，3/12=0.25
     第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
     第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/

执行结果如下：

5. 前后函数 LAG和LEAD

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值

-- 查询前1名同学的成绩和当前同学成绩的差值
select 
 dname,
 ename,
 hiredate,
 salary,
 lag(hiredate,1,'2000-01-01') over(partition by dname order by hiredate) as last_1_time,
 lag(hiredate,2) over(partition by dname order by hiredate) as last_2_time 
from employee;

/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'  
      第一行，往上1行为null,因此取默认值 '2000-01-01'
      第二行，往上1行值为第一行值，2021-11-01 
      第三行，往上1行值为第二行值，2021-11-02 
last_2_time: 指定了往上第2行的值，为指定默认值
       第一行，往上2行为null
       第二行，往上2行为null
       第四行，往上2行为第二行值，2021-11-01 
       第七行，往上2行为第五行值，2021-11-02 
*/

执行结果如下：

LEAD函数用法同LAG函数，表示返回位于当前行的后n行的expr值

6. 头尾函数 FIRST_VALUE和LAST_VALUE

描述：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值

select
  dname,
  ename,
  hiredate,
  salary,
  -- 截止到当前，按照日期排序查询第1个入职员工的薪资
  first_value(salary) over(partition by dname order by hiredate) as first,
  -- 截止到当前，按照日期排序查询最后1个入职员工的薪资
  last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;

注意：如果不指定ORDER BY，则进行排序混乱，会出现错误的结果

执行结果如下：

7. NTH_VALUE(expr, n)函数

描述：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

select 
  dname,
  ename,
  hiredate,
  salary,
  -- 查询每个部门截止目前薪资排在第二的员工信息
  nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
  -- 查询每个部门截止目前薪资排在第三的员工信息
  nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee

执行结果如下：

8. NTILE(n)函数

描述：将分区中的有序数据分为n个等级，记录等级数

select 
  dname,
  ename,
  hiredate,
  salary,
-- 根据入职日期将每个部门的员工分成3组
ntile(3) over(partition by dname order by  hiredate  ) as rn 
from employee;

执行结果如下：