本节介绍窗口函数。

介绍

MySQL 8.0 新增窗口函数,窗口函数又被称为开窗函数,与Oracle 窗口函数类似,属于MySQL的一大特点。

聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数。

聚合函数与窗口函数的区别

分类

窗口函数的分类

另外还有开窗聚合函数: SUM,AVG,MIN,MAX

通用语法

window_function ( expr ) OVER ( 
  PARTITION BY ... 
  ORDER BY ... 
  frame_clause 
)
  • window_function 是窗口函数的名称;
  • expr 是参数,有些函数不需要参数;
  • 分区(PARTITION BY):将数据行拆分成多个分区(组),类似group by,如果省略了 PARTITION BY,所有的数据作为一个组进行计算;
  • 排序(ORDER BY):OVER 子句中的ORDER BY选项用于指定分区内的排序方式,与 ORDER BY 子句的作用类似;
  • 窗口大小(frame_clause)。frame_clause选项用于在当前分区内指定一个计算窗口,也就是一个与当前行相关的数据子集。

窗口函数

序号函数、分布函数、前后函数、头尾函数、其他函数。

序号函数

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

格式:

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

操作:

use mydb4; 
create table employee( 
   dname varchar(20), -- 部门名 
   eid varchar(20), 
   ename varchar(20), 
   hiredate date, -- 入职日期 
   salary double -- 薪资
); 

insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);

-- 对每个部门的员工按照薪资排序,并给出排名
select 
dname,
ename,
salary,
-- row_number() 
row_number() over(partition by dname order by salary desc) as rn1 , 
-- rank()
rank() over(partition by dname order by salary desc) as rn2 ,
-- dense_rank()
dense_rank() over(partition by dname order by salary desc) as rn3 
from employee;
-- 结果
-- dname  ename salary rn1 rn2 rn3
-- 研发部  张飞  7000    1     1   1
-- 研发部  赵云  7000    2   1   1
-- 研发部  关羽  5000    3   3   2
-- 研发部  马超  4000    4   4   3
-- 研发部  黄忠  4000    5     4   3
-- 研发部  刘备  3000    6     6   4
-- 销售部  徐晃  9000    1     1   1
-- 销售部  张辽  6000    2     2   2
-- 销售部  曹洪  6000    3     2   2
-- 销售部  典韦  5000    4     4   3
-- 销售部  许褚  3000    5     5   4
-- 销售部  曹操  2000    6     6   5

-- 求魔每个部门薪资排在前三名的员工  --分组求TOPN的问题
select
*
from
(
select 
dname,
ename,
salary,
-- dense_rank()
dense_rank() over(partition by dname order by salary desc) as rn
from employee
)t
where t.rn<=3

-- 对所有员工进行全局排序,不分组
-- 不加partition by表示全局排序
select 
     dname,
     ename,
     salary,
     dense_rank() over( order by salary desc)  as rn
from employee;

开窗聚合函数

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

这里用SUM来举例。

实现:

  • 分组,组内进行累加
  • 对指定的行数进行相加
-- 分组,组内进行累加
select  
 dname,
 ename,
 salary,
 sum(salary)  over(partition by dname order by hiredate) as pv1 
from employee;
-- 运行结果:
--                     pv1
-- 研发部  刘备  3000    3000
-- 研发部  关羽  5000    8000
-- 研发部  张飞  7000    15000
-- 研发部  赵云  7000    22000
-- 研发部  马超  4000    26000
-- 研发部  黄忠  4000    30000

-- 销售部  曹操  2000    2000
-- 销售部  许褚  3000    5000
-- 销售部  典韦  5000    10000
-- 销售部  张辽  6000    16000
-- 销售部  徐晃  9000    25000

select  
 dname,
 ename,
 salary,
 sum(salary)  over(partition by dname) as pv1 
from employee;
-- 如果没有order  by排序语句 , 默认把分组内的所有数据进行sum操作
-- 运行结果:
-- 研发部  刘备  3000    30000
-- 研发部  关羽  5000    30000
-- 研发部  张飞  7000    30000
-- 研发部  赵云  7000    30000
-- 研发部  马超  4000    30000
-- 研发部  黄忠  4000    30000
-- 销售部  曹操  2000    31000
-- 销售部  许褚  3000    31000
-- 销售部  典韦  5000    31000
-- 销售部  张辽  6000    31000
-- 销售部  徐晃  9000    31000

-- 可以指定行数相加
select  
 dname,
 ename,
 salary,
 -- 前面三行一直加到自己的所在行
 sum(salary) over(partition by dname order by hiredate  rows between 3 preceding and current row) as c1    
from employee;
-- 运行结果:
-- 研发部  刘备  3000    3000
-- 研发部  关羽  5000    8000
-- 研发部  张飞  7000    15000
-- 研发部  赵云  7000    22000
-- 研发部  马超  4000    23000   # 这里的23000,是5000+7000+7000+4000得到的
-- 研发部  黄忠  4000    22000   # 这里的22000,是7000+7000+4000+4000得到的
-- 销售部  曹操  2000    2000
-- 销售部  许褚  3000    5000
-- 销售部  典韦  5000    10000
-- 销售部  张辽  6000    16000
-- 销售部  徐晃  9000    23000


select  
 dname,
 ename,
 salary,
 -- 3 preceding表示前三行,1 following表示后一行
 -- 当然包括自己所在行,也就是一共5行所加的结果
 -- 要是向上没有三行,则有一行算一行
 -- 要是向后没有一行,则有一行算一行
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1 
from employee;
-- 运行结果:
-- 研发部  刘备  3000    8000
-- 研发部  关羽  5000    15000
-- 研发部  张飞  7000    22000
-- 研发部  赵云  7000    26000   #这里的26000是3000+5000+7000+7000+4000=26000
-- 研发部  马超  4000    27000
-- 研发部  黄忠  4000    22000
-- 销售部  曹操  2000    5000
-- 销售部  许褚  3000    10000
-- 销售部  典韦  5000    16000
-- 销售部  张辽  6000    25000
-- 销售部  徐晃  9000    29000

select  
 dname,
 ename,
 salary,
 -- current row表示当前行
 -- unbounded following表示到最后
 -- 句子的意思表示从当前行一直加到最后
 sum(salary) over(partition by dname order by hiredate  rows between current row and unbounded following) as c1 
from employee;
-- 运行结果
-- 研发部  刘备  3000    30000
-- 研发部  关羽  5000    27000
-- 研发部  张飞  7000    22000
-- 研发部  赵云  7000    15000
-- 研发部  马超  4000    8000
-- 研发部  黄忠  4000    4000
-- 销售部  曹操  2000    31000
-- 销售部  许褚  3000    29000
-- 销售部  典韦  5000    26000
-- 销售部  张辽  6000    21000
-- 销售部  徐晃  9000    15000

# 上面的sum可以替换为SUM,AVG,MIN,MAX中的任意一个

分布函数

CUME_DIST函数

• 用途:分组内小于、等于当前rank值的行数 / 分组内总行数

• 应用场景:查询小于等于当前薪资(salary)的比例

案例:

-- 原始数据:
-- 研发部  1001    刘备  2021-11-01  3000
-- 研发部  1002    关羽  2021-11-02  5000
-- 研发部  1003    张飞  2021-11-03  7000
-- 研发部  1004    赵云  2021-11-04  7000
-- 研发部  1005    马超  2021-11-05  4000
-- 研发部  1006    黄忠  2021-11-06  4000
-- 销售部  1007    曹操  2021-11-01  2000
-- 销售部  1008    许褚  2021-11-02  3000
-- 销售部  1009    典韦  2021-11-03  5000
-- 销售部  1010    张辽  2021-11-04  6000
-- 销售部  1011    徐晃  2021-11-05  9000
-- 销售部  1012    曹洪  2021-11-06  6000

SELECT
  dname,
    ename,
    salary,
    CUME_DIST() over(order by salary) as rn1,
    CUME_DIST() over(PARTITION by dname  order by salary) as rn1
from employee;

-- 结果:
-- 研发部  刘备  3000    0.25    0.16666666666666666  
   #0.25的理由是:一共12行,小于等于3000的是3行,3/12=0.25
   #0.16666的理由是:进行了分组,也就是说一共6行,小于等于3000的就一行。1/6=0.166666

     #剩下的同理

-- 研发部  马超  4000    0.4166666666666667  0.5
-- 研发部  黄忠  4000    0.4166666666666667  0.5
-- 研发部  关羽  5000    0.5833333333333334  0.6666666666666666
-- 研发部  张飞  7000    0.9166666666666666  1
-- 研发部  赵云  7000    0.9166666666666666  1
-- 销售部  曹操  2000    0.08333333333333333 0.16666666666666666
-- 销售部  许褚  3000    0.25    0.3333333333333333
-- 销售部  典韦  5000    0.5833333333333334  0.5
-- 销售部  张辽  6000    0.75    0.8333333333333334
-- 销售部  曹洪  6000    0.75    0.8333333333333334
-- 销售部  徐晃  9000    1   1

PERCENT_RANK函数

•用途:每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数

•应用场景:不常用

-- 原始数据:
-- 研发部  1001    刘备  2021-11-01  3000
-- 研发部  1002    关羽  2021-11-02  5000
-- 研发部  1003    张飞  2021-11-03  7000
-- 研发部  1004    赵云  2021-11-04  7000
-- 研发部  1005    马超  2021-11-05  4000
-- 研发部  1006    黄忠  2021-11-06  4000
-- 销售部  1007    曹操  2021-11-01  2000
-- 销售部  1008    许褚  2021-11-02  3000
-- 销售部  1009    典韦  2021-11-03  5000
-- 销售部  1010    张辽  2021-11-04  6000
-- 销售部  1011    徐晃  2021-11-05  9000
-- 销售部  1012    曹洪  2021-11-06  6000

SELECT
  dname,
    ename,
    salary,
    rank() over(PARTITION by dname order by salary desc) as rn1,
    PERCENT_RANK() over(PARTITION by dname  order by salary desc) as rn2
from employee;
-- 运行结果:
-- 研发部  张飞  7000    1   0
    -- 用rank来排名,一组6个人,里面的薪资第1名
        -- rn2:(1-1)/(6-1) = 0

-- 研发部  赵云  7000    1   0
-- 研发部  关羽  5000    3   0.4
    -- 用rank来排名,一组6个人,里面的薪资第3名
        -- rn2:(3-1)/(5-1) = 0.4

-- 研发部  马超  4000    4   0.6
-- 研发部  黄忠  4000    4   0.6
-- 研发部  刘备  3000    6   1
-- 销售部  徐晃  9000    1   0
-- 销售部  张辽  6000    2   0.2
-- 销售部  曹洪  6000    2   0.2
-- 销售部  典韦  5000    4   0.6
-- 销售部  许褚  3000    5   0.8
-- 销售部  曹操  2000    6   1

头尾函数

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

•应用场景:查询前1名同学的成绩和当前同学成绩的差值

lag(expr,n[,默认值])

语法:

lag(expr,n[,默认值])
SELECT
  dname,
    ename,
    salary,
    hiredate,
    lag(hiredate,1,'2000-06-01') over(PARTITION by dname order by hiredate) as time1,   -- hiredate入职日期
    lag(hiredate,2) over(PARTITION by dname  order by hiredate) as time2
from employee;
-- 运行结果
-- dname   ename salary hiredata     time1       time2
-- 研发部  刘备  3000    2021-11-01  2000-06-01  
-- 研发部  关羽  5000    2021-11-02  2021-11-01  
-- 研发部  张飞  7000    2021-11-03  2021-11-02  2021-11-01
-- 研发部  赵云  7000    2021-11-04  2021-11-03  2021-11-02
-- 研发部  马超  4000    2021-11-05  2021-11-04  2021-11-03
-- 研发部  黄忠  4000    2021-11-06  2021-11-05  2021-11-04
-- 销售部  曹操  2000    2021-11-01  2000-06-01  
-- 销售部  许褚  3000    2021-11-02  2021-11-01  
-- 销售部  典韦  5000    2021-11-03  2021-11-02  2021-11-01
-- 销售部  张辽  6000    2021-11-04  2021-11-03  2021-11-02
-- 销售部  徐晃  9000    2021-11-05  2021-11-04  2021-11-03
-- 销售部  曹洪  6000    2021-11-06  2021-11-05  2021-11-04
# 相当于把本行的前一行或者n行放到了一行里

lag函数关系示意

lead(expr,n[,默认值])

语法:

lead(expr,n[,默认值])
SELECT
  dname,
    ename,
    salary,
    hiredate,
    lead(hiredate,1,'2000-06-01') over(PARTITION by dname order by hiredate) as time1,   -- hiredate入职日期
    lead(hiredate,2) over(PARTITION by dname  order by hiredate) as time2
from employee;
-- 运行结果:
-- dname   ename salary hiredata     time1       time2
-- 研发部  刘备  3000    2021-11-01  2021-11-02  2021-11-03
-- 研发部  关羽  5000    2021-11-02  2021-11-03  2021-11-04
-- 研发部  张飞  7000    2021-11-03  2021-11-04  2021-11-05
-- 研发部  赵云  7000    2021-11-04  2021-11-05  2021-11-06
-- 研发部  马超  4000    2021-11-05  2021-11-06  
-- 研发部  黄忠  4000    2021-11-06  2000-06-01  
-- 销售部  曹操  2000    2021-11-01  2021-11-02  2021-11-03
-- 销售部  许褚  3000    2021-11-02  2021-11-03  2021-11-04
-- 销售部  典韦  5000    2021-11-03  2021-11-04  2021-11-05
-- 销售部  张辽  6000    2021-11-04  2021-11-05  2021-11-06
-- 销售部  徐晃  9000    2021-11-05  2021-11-06  
-- 销售部  曹洪  6000    2021-11-06  2000-06-01  

lead示意图

头尾函数

•用途:返回到目前为止的第一个FIRST_VALUE(expr)或最后一个LAST_VALUE(expr)的值

•应用场景:截止到当前,按照日期排序查询第1个入职和最后1个入职员工的薪资

-- 注意,  如果不指定ORDER BY,则进行排序混乱,会出现错误的结果
select
  dname,
  ename,
  hiredate,
  salary,
    -- 获取第一个入职人员的薪资
  first_value(salary) over(partition by dname order by hiredate) as first,
    -- 获取最后一个入职人员的薪资
  last_value(salary) over(partition by dname order by  hiredate) as last 
from  employee;
-- 运行结果:
-- 研发部  刘备  2021-11-01  3000    3000    3000  (第一个3000是到目前为止第一个入职的薪资3000)(第一个3000是到目前为止最后一个入职的薪资3000)
-- 研发部  关羽  2021-11-02  5000    3000    5000  (第一个3000是到目前为止第一个入职的薪资3000)(第一个5000是到目前为止最后一个入职的薪资5000)
-- 研发部  张飞  2021-11-03  7000    3000    7000  (第一个3000是到目前为止第一个入职的薪资3000)(第一个7000是到目前为止最后一个入职的薪资7000)
-- 研发部  赵云  2021-11-04  7000    3000    7000
-- 研发部  马超  2021-11-05  4000    3000    4000
-- 研发部  黄忠  2021-11-06  4000    3000    4000
-- 销售部  曹操  2021-11-01  2000    2000    2000
-- 销售部  许褚  2021-11-02  3000    2000    3000
-- 销售部  典韦  2021-11-03  5000    2000    5000
-- 销售部  张辽  2021-11-04  6000    2000    6000
-- 销售部  徐晃  2021-11-05  9000    2000    9000
-- 销售部  曹洪  2021-11-06  6000    2000    6000

其他窗口函数

nth_value(salary,2)

•用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名

•应用场景:截止到当前薪资,显示每个员工的薪资中排名第2或者第3的薪资

-- 查询每个部门截止目前(按入职日期排序)薪资排在第二和第三的员工信息
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;
-- 运行结果
-- dname   ename  hiredata  salary second_score third_score
-- 研发部  刘备  2021-11-01  3000        
-- 研发部  关羽  2021-11-02  5000       5000 
-- 研发部  张飞  2021-11-03  7000       5000         7000
-- 研发部  赵云  2021-11-04  7000       5000         7000
-- 研发部  马超  2021-11-05  4000       5000         7000
-- 研发部  黄忠  2021-11-06  4000       5000         7000
-- 销售部  曹操  2021-11-01  2000        
-- 销售部  许褚  2021-11-02  3000       3000 
-- 销售部  典韦  2021-11-03  5000       3000         5000
-- 销售部  张辽  2021-11-04  6000       3000         5000
-- 销售部  徐晃  2021-11-05  9000       3000         5000
-- 销售部  曹洪  2021-11-06  6000       3000         5000

NTILE(n)

•用途:将分区中的有序数据分为n个等级,记录等级数

•应用场景:将每个部门员工按照入职日期分成3组

-- 根据入职日期将每个部门的员工分成3组,取出每个部门的第一组员工
select
* from(
select 
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(partition by dname order by  hiredate  ) as nt 
from employee
)t 
where t.nt = 1;
-- 运行结果:
-- dname   ename   hiredate salary  nt 
-- 研发部  刘备  2021-11-01  3000        1
-- 研发部  关羽  2021-11-02  5000        1
-- 销售部  曹操  2021-11-01  2000        1
-- 销售部  许褚  2021-11-02  3000        1

声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/sql17.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 05 日
如果觉得我的文章对你有用,请随意赞赏