【MySQL 8.0新特性】窗口函数

Dear 丶 2024-04-01 18:42 60阅读 0赞

#### 文章目录 ####

*  1. 窗口函数概述
 *  2. 序号函数
 *  3. 分布函数
 *  4. 前后函数
 *  5. 头尾函数
 *  6. 其他函数

## 1. 窗口函数概述 ##

> 什么是窗口函数？

窗口函数是MySQL8.0新增的，窗口函数又称开窗函数，属于MySQL的一大特点。

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

![image-20221114205402934][]

> 窗口函数有什么用？

窗口函数可以对数据库数据进行实时分析处理，比如市场分析、创建财务报表、创建计划等日常性商务工作。

> 窗口函数分为哪几种？

窗口函数分为以下几种：

1.  序号函数
    
     *  ROW\_NUMBER() —— 排序:1,2,3
     *  RANK0 —— 排序: 11,3
     *  DENSE\_RANK() —— 排序: 1,1,2
2.  分布函数
    
     *  PERCENT\_RANK() —— (rank-1) /(rows-1)
     *  CUME\_DIST() —— <=当前rank值的行数/总行数
3.  前后函数
    
     *  LAG (expr,n) —— 返回当前行的前n行的expr的值
     *  LEAD (expr,n) —— 返回当前行的后n行的expr的值
4.  头尾函数
    
     *  FIRST\_VALUE(expr) —— 返回第一个expr的值
     *  LAST\_VALUE (expr) —— 返回最后一个expr的值
5.  其他函数
    
     *  NTH\_VALUE (expr,n) —— 返回第n个expr的值
     *  NTILE (n) —— 将有序数据分为n个桶，记录等级数

![image-20221114210039308][]

> 窗口函数的基本用法是怎么样的？

<窗口函数> OVER ( [PARTITION BY <列清单>] ORDER BY <排序用列清单>)

over关键字用来指定函数执行的窗口范围,**若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行**，窗口函数基于所有行进行计算；如果不为空,则支持以下4中语法来设置窗口。

1.  window\_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读
2.  partition by子句：窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行，\*\*它的作用类似于GROUP BY分组。\*\*如果省略了 PARTITION BY，所有的数据作为一个组进行计算
3.  order by子句：按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
4.  frame子句：frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用

--------------------

## 2. 序号函数 ##

序号函数有三个，**分别是ROW\_NUMBER()、RANK()、DENSE\_RANK()，可以用来实现分组排序，并添加序号**

格式如下

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;

![image-20221114210652772][]

-- 对每个部门的员工按照薪资排序，并给出排名 rank
    select 
    dname,
    ename,
    salary,
    rank() over(partition by dname order by salary desc) as rn 
    from employee;

![image-20221114210709242][]

-- 对每个部门的员工按照薪资排序，并给出排名 dense-rank
    select 
    dname,
    ename,
    salary,
    dense_rank() over(partition by dname order by salary desc) as rn 
    from employee;

![image-20221114210724380][]

> 观察上面各个序号函数的区别是什么？

1.  ROW\_NUMBER():顺序排序——1、2、3
2.  RANK():并列排序，跳过重复序号——1、1、3
3.  DENSE\_RANK():并列排序，不跳过重复序号——1、1、2

--------------------

## 3. 分布函数 ##

分布函数**包括PERCENT\_RANK()和CUME\_DIST()**

1）PERCENT\_RANK()函数将某个数值在数据集中的排位作为数据集的百分比值返回，此处的百分比值的范围为 0 到 1

*  用途：每行**按照公式(rank-1) / (rows-1)进行计算**。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
 *  应用场景：不常用

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

![image-20221114211650330][]

2） CUME\_DIST

*  用途：分组**内小于、等于当前rank值的行数** / **分组内总行数**
 *  应用场景：查询小于等于当前薪资（salary）的比例

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;

![image-20221114212006869][]

这里需要解释一下，就rn1而言

首先组共12个人（因为没有partition语句 所有的数据位于一组），刘备这行数据对应的rn1是0.25

大于等于3000的数据共有3条，因此是3/12=0.25

--------------------

## 4. 前后函数 ##

前后函数有LAG (expr,n)和LEAD (expr n)。

Lag()和Lead()分析函**数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。**

在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag()和Lead()函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，**但是LAG()和LEAD()与left join、rightjoin等自连接相比**，效率更高，SQL更简洁。

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

/*
    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 
    */
    
    -- lag的用法
    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;

![image-20221114213451836][]

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

![image-20221114213602078][]

--------------------

## 5. 头尾函数 ##

头尾函数包括：LAST\_VALUE (expr)与FIRST\_VALUE(expr)

*  返回第一个expr的值：FIRST\_VALUE(expr)
 *  返回最后一个expr的值：LAST\_VALUE(expr)
 *  用途：返回第一个（FIRST\_VALUE(expr)）或最后一个（LAST\_VALUE(expr)）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;

![image-20221114213854860][]

--------------------

## 6. 其他函数 ##

其他函数包括NTH\_VALUE (expr,n)与NTILE (n)

*  NTH\_VALUE (expr,n) —— 返回第n个expr的值
 *  NTILE (n) —— 将有序数据分为n个桶，记录等级数
 *  用途：将分区中的有序数据分为n个等级，记录等级数
 *  应用场景：将每个部门员工按照入职日期分成3组

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

![image-20221114214144571][]

-- 取出每个部门的第一组员工
    select
    *
    from
    (
        SELECT 
            dname,
            ename,
            hiredate,
            salary,
        NTILE(3) OVER(PARTITION BY dname ORDER BY  hiredate  ) AS rn 
        FROM employee
    )t
    where t.rn = 1;

![image-20221114214226789][]

--------------------

> 参考：
> 
>  *  [2022新版黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程][2022_MySQL_mysql_mysql]
>  *  [MYSQL常见面试题之——窗口函数\_web3纪元的博客-CSDN博客\_窗口函数][MYSQL_web3_-CSDN]
>  *  [MySQL/Hive 常用窗口函数详解及相关面试题\_TRX1024的博客-CSDN博客][MySQL_Hive _TRX1024_-CSDN]

[image-20221114205402934]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0e43aa78ee6d47e9a8854e4fa417772c.png
[image-20221114210039308]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/269524c8b5ab4c948642d462e728f0a9.png
[image-20221114210652772]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/1e21d77c67c44c9cb4c7903f2d8f97f7.png
[image-20221114210709242]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/52b284fdae664fc6a7ee5c3a6dcfc7ee.png
[image-20221114210724380]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/8a92f1e378e54df5aa75469b5d1d2611.png
[image-20221114211650330]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/ce40bb26162942f5acc1acd595ebe692.png
[image-20221114212006869]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/7bed291dcf74427aab947bf690b85cd9.png
[image-20221114213451836]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/e00eb3a000b641a8972a33462d387791.png
[image-20221114213602078]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/80072175d89042cda94ee8ef0c972e51.png
[image-20221114213854860]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/093dd2d29ecd4867a004e74370b39f8d.png
[image-20221114214144571]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/0416c01631114682a1becf41ec1c0ef8.png
[image-20221114214226789]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/09081b3d971f4a4cb07aa474f9982401.png
[2022_MySQL_mysql_mysql]: https://www.bilibili.com/video/BV1iF411z7Pu
[MYSQL_web3_-CSDN]: https://blog.csdn.net/qq_34069667/article/details/107173779
[MySQL_Hive _TRX1024_-CSDN]: https://blog.csdn.net/weixin_43161811/article/details/123924474