MySQL窗口函数的具体使用

目录
  • 一、什么是窗口函数
    • 1、怎么理解窗口?
    • 2、什么是窗口函数
  • 二、窗口函数用法
    • 1、序号函数:row_number() / rank() / dense_rank()
    • 2、分布函数:percent_rank() / cume_dist()
    • 3、前后函数:lag(expr,n) / lead(expr,n)
    • 4、头尾函数:first_value(expr)、last_value(expr)
    • 5、其他函数:nth_value() / nfile()
  • 本章小结

      之前我给粉丝们搞过个投票,寻找mysql中那个最熟悉的陌生人~~mysql中哪些技术点是你既熟悉又陌生的?

    前三名和我预料大差不差,分别是:

    1、游标2、窗口函数3、聚簇索引

      这三个点虽然平时用得少,但在面试中却常被问到。值得一提的是,很多面试官对问题竟然也是一知半解。。

      今天我想和你聊聊窗口函数,mysql从8.0开始支持窗口函数,或许你们公司的mysql版本还无法让你爽一把,但我建议你要在本地搞一个试试,真香!

      好了,废话不多说,老规矩,先上开胃小菜,看看今天的测试表数据吧。

      本文用来演示用的测试表是chh_baozipu,翻译过来就是emmm…陈哈哈的包子铺。悄悄告诉你,哈哥今年盘了个包子铺卖包子,这张表就是包子铺这半年的利润~

    mysql> select * from chh_baozipu ;
    +----+--------------------+-------+---------+
    | id | product            | sales | month   |
    +----+--------------------+-------+---------+
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |
    |  7 | 面馅儿包子         |   700 | 2021-11 |
    |  8 | 面馅儿包子         |   200 | 2021-10 |
    |  9 | 面馅儿包子         |   300 | 2021-09 |
    | 10 | 面馅儿包子         |     0 | 2021-08 |
    | 11 | 面馅儿包子         |   100 | 2021-07 |
    | 12 | 面馅儿包子         |   200 | 2021-06 |
    +----+--------------------+-------+---------+
    12 rows in set (0.00 sec)
    

      怎么说?什么时候来我店里,请大家吃面馅儿包子。

    一、什么是窗口函数

    1、怎么理解窗口?

      其实窗口的概念是非常重要的,要想学会窗口函数,可不能只知其一不知其二;我们得搞清楚窗口代表着啥,才知道什么时候该用它。

      拿测试表举个简单的例子,统计一下:包子铺的猪肉大葱包子这半年截至每月累计利润

    select *,sum(sales) over(order by `month`) as 累计利润 
    	from chh_baozipu where product='猪肉大葱包子';
    
    mysql> select *,sum(sales) over(order by `month`) as 累计利润 from chh_baozipu where product='猪肉大葱包子';
    +----+--------------------+-------+---------+--------------+
    | id | product            | sales | month   | 累计利润     |
    +----+--------------------+-------+---------+--------------+
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |         1000 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |         2600 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |         3400 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |         4400 |
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |         6000 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |         6600 |
    +----+--------------------+-------+---------+--------------+
    6 rows in set (0.00 sec)
    

      从这条sql可以看出,对于第一行id=6这行的窗口就是第一行,对于第二行id=5这行的窗口就是前两行,以此类推(如下图)。

      可见,窗口就是范围的意思,可以理解为一些记录(行)的集合;窗口函数也就是在满足某种条件的记录集合上执行计算的特殊函数。

      对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。看完本文再回来看这句话相信会理解的更透彻[手动狗头]。

    2、什么是窗口函数

    窗口函数也叫olap函数(online anallytical processing),可以对数据进行实时分析处理。

    窗口函数多用在什么场景?主要有以下两类:

    • 排名问题,例如:查包子铺利润月排名;
    • topn问题,例如:查每种包子利润最高的两个月;

    我们常见的窗口函数和聚合函数有这些:

    • 专用窗口函数:rank()dense_rank()row_number()
    • 聚合函数:max()min()count()sum()avg()

    因为聚合函数也可以放在窗口函数中使用,因此窗口函数和普通聚合函数也很容易被混淆,二者区别如下:

    • 聚合函数是将多条记录聚合为一条;而窗口函数是每条记录都会执行,有几条记录执行完还是几条
    • 聚合函数也可以用于窗口函数中,这个我会举例说明。

    二、窗口函数用法

    基本语法:

    <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>);
    -- over关键字用于指定函数的窗口范围,
    -- partition by 用于对表分组,
    -- order by子句用于对分组后的结果进行排序。
    

    注意:窗口函数是对where或者group by子句处理后的结果再进行二次操作,因此会按照sql语句的运行顺序,窗口函数一般放在select子句中(from前),例如上一条sql,可以往上拖着看看~

    窗口函数都有哪些?懒得画了,借lulin916老哥的导图一用~~

    • 序号函数:row_number() / rank() / dense_rank()
    • 分布函数:percent_rank() / cume_dist()
    • 前后函数:lag() / lead()
    • 头尾函数:first_val() / last_val()
    • 其他函数:nth_value() / nfile()

    让我们来分别举例看一看:

    1、序号函数:row_number() / rank() / dense_rank()

    row_number():顺序排序 —— 1、2、3

    rank():并列排序,跳过重复序号 —— 1、1、3

    dense_rank():并列排序,不跳过重复序号 —— 1、1、2

    mysql> select *,row_number() over(order by sales desc) as pro_row_number,rank() over(order by sales desc) as pro_rank,dense_rank() over(order by sales desc) as pro_dense_rank from chh_baozipu where product='猪肉大葱包子';
    +----+--------------------+-------+---------+----------------+----------+----------------+
    | id | product            | sales | month   | pro_row_number | pro_rank | pro_dense_rank |
    +----+--------------------+-------+---------+----------------+----------+----------------+
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |              1 |        1 |              1 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |              2 |        1 |              1 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |              3 |        3 |              2 |
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |              4 |        3 |              2 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |              5 |        5 |              3 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |              6 |        6 |              4 |
    +----+--------------------+-------+---------+----------------+----------+----------------+
    6 rows in set (0.00 sec)
    

    如上述示例可见,三个窗口函数服务与不同的三个典型业务需求,这三种足以应对我们的排序统计。

    以后同学们在面试或笔试时被问到时,请不要再说自查询嵌套之类的lowb方案了,不然可别说你认识我~狗子们

    2、分布函数:percent_rank() / cume_dist()

    这个分布函数基本不用,不讲。有兴趣的同学自行百度~

    3、前后函数:lag(expr,n) / lead(expr,n)

    expr后面还会涉及到,统一解释一下:expr可以是表达式,也可以是列名

    前后函数常用于:返回位于当前行的前n行(lag(expr,n))或后n行(lead(expr,n))的expr的值

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

    内层sql先通过lag()函数得到前1名同学的成绩,外层sql再将当前同学和前1名同学的成绩做差得到成绩差值diff。

    这里换成哈哥的测试表就有点尬了。。但你肯定明白这意思,来,让我们尬查一下:

    mysql> select *,lag(sales,1) over win as pro_lag,lead(sales,1) over win as pro_lead from chh_baozipu window win as (partition by product order by sales desc);
    +----+--------------------+-------+---------+---------+----------+
    | id | product            | sales | month   | pro_lag | pro_lead |
    +----+--------------------+-------+---------+---------+----------+
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |    null |     1600 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |    1600 |     1000 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |    1600 |     1000 |
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |    1000 |      800 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |    1000 |      600 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |     800 |     null |
    |  7 | 面馅儿包子         |   700 | 2021-11 |    null |      300 |
    |  9 | 面馅儿包子         |   300 | 2021-09 |     700 |      200 |
    |  8 | 面馅儿包子         |   200 | 2021-10 |     300 |      200 |
    | 12 | 面馅儿包子         |   200 | 2021-06 |     200 |      100 |
    | 11 | 面馅儿包子         |   100 | 2021-07 |     200 |        0 |
    | 10 | 面馅儿包子         |     0 | 2021-08 |     100 |     null |
    +----+--------------------+-------+---------+---------+----------+
    12 rows in set (0.00 sec)
    
    

    这里我想问一下同学们是不是发现这条sql和前面sql不同?有哪几个地方不同呢?

    select *,
    lag(sales,1) over win as pro_lag,
    lead(sales,1) over win as pro_lead
    from chh_baozipu where product='猪肉大葱包子' 
    window win as (partition by product order by sales desc);
    

    1、把窗口提取出来设置了别名

    其实,这种是把窗口提了出来,设置别名为:win,像我们写sql时用别名一样,这样看起来会简洁舒服一些,是吧。

    有人问程序员要什么简洁?别人看不懂才会觉得代码牛b啊。这种同学一看就是没被社会毒打过,等你遇到百年一见的祖传代码时候,你就懂啥叫大道至简了(借胖哥图一用)。

    2、窗口中增加了partition by product

      这个关键字在over子句中,也就意味着控制了窗口的内容,在上面基础语法中我告诉你over中有两个个关键词:

    • partition by 是对窗口内容进行分组处理;
    • order by 是对窗口内容分组后进行排序;

      其实,还有更有意思的控制窗口范围的方式~~

      对于滑动窗口的范围指定,有两种方式,基于行和基于范围,我跟你着重介绍常用的基于行来控制窗口范围;

      通常使用between frame_start and frame_end语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录:

    • current row 边界是当前行,一般和其他范围关键字一起使用
    • unbounded preceding 边界是分区中的第一行
    • unbounded following 边界是分区中的最后一行
    • expr preceding 边界是当前行减去expr的值
    • expr following 边界是当前行加上expr的值

    来看几个例子:

    ①计算当前行与前n行(共n+1行)的聚合窗口函数

    下例中控制窗口大小为当前月+前两个月的利润总和,来看一下效果:

    select *,sum(sales) over win as '近三个月利润相加'
    from chh_baozipu 
    window win as (partition by product order by `month` rows 2 preceding);
    
    mysql> select *,sum(sales) over win as '近三个月利润相加'
        -> from chh_baozipu 
    	-> window win as (partition by product order by `month` rows 2 preceding);
    +----+--------------------+-------+---------+--------------------------+
    | id | product            | sales | month   | 近三个月利润相加         |
    +----+--------------------+-------+---------+--------------------------+
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |                     1000 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |                     2600 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |                     3400 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |                     3400 |
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |                     3400 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |                     3200 |
    | 12 | 面馅儿包子         |   200 | 2021-06 |                      200 |
    | 11 | 面馅儿包子         |   100 | 2021-07 |                      300 |
    | 10 | 面馅儿包子         |     0 | 2021-08 |                      300 |
    |  9 | 面馅儿包子         |   300 | 2021-09 |                      400 |
    |  8 | 面馅儿包子         |   200 | 2021-10 |                      500 |
    |  7 | 面馅儿包子         |   700 | 2021-11 |                     1200 |
    +----+--------------------+-------+---------+--------------------------+
    12 rows in set (0.00 sec)
    

    ②计算当前行与前n1行、后n2行的聚合窗口函数

    下例中控制窗口大小为当前月前一个月到后一个月的利润总和,来看一下效果:

    select *,sum(sales) over win as '前三个月利润相加' 
    from chh_baozipu 
    window win as (partition by product order by `month` rows between n1 preceding and n2 following);
    
    mysql> select *,sum(sales) over win as '前一个月到下一个月利润相加' from chh_baozipu window win as (partition by product order by `month` rows between 1 preceding and 1 following);
    +----+--------------------+-------+---------+--------------------------+
    | id | product            | sales | month   |前一个月到下一个月利润相加|
    +----+--------------------+-------+---------+--------------------------+
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |                     2600 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |                     3400 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |                     3400 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |                     3400 |
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |                     3200 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |                     2200 |
    | 12 | 面馅儿包子         |   200 | 2021-06 |                      300 |
    | 11 | 面馅儿包子         |   100 | 2021-07 |                      300 |
    | 10 | 面馅儿包子         |     0 | 2021-08 |                      400 |
    |  9 | 面馅儿包子         |   300 | 2021-09 |                      500 |
    |  8 | 面馅儿包子         |   200 | 2021-10 |                     1200 |
    |  7 | 面馅儿包子         |   700 | 2021-11 |                      900 |
    +----+--------------------+-------+---------+--------------------------+
    12 rows in set (0.00 sec)
    

    4、头尾函数:first_value(expr)、last_value(expr)

    头尾函数应用于:返回第一个或最后一个expr的值;

    应用场景:截止到当前,按照日期排序查询当前最大月收入当前最小月收入

    select *,
    first_value(sales) over win as '当前最大月收入',
    last_value(sales) over win as '当前最小月收入' 
    from chh_baozipu 
    window win as (partition by product order by `month`);
    
    mysql> select *,first_value(sales) over win as '当前最大月收入',last_value(sales) over win as '当前最小月收入' from chh_baozipu window win as (partition by product order by `month`);
    +----+--------------------+-------+---------+-----------------------+-----------------------+
    | id | product            | sales | month   | 当前最大月收入        | 当前最小月收入        |
    +----+--------------------+-------+---------+-----------------------+-----------------------+
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |                  1000 |                  1000 |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |                  1000 |                  1600 |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |                  1000 |                   800 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |                  1000 |                  1000 |
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |                  1000 |                  1600 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |                  1000 |                   600 |
    | 12 | 面馅儿包子         |   200 | 2021-06 |                   200 |                   200 |
    | 11 | 面馅儿包子         |   100 | 2021-07 |                   200 |                   100 |
    | 10 | 面馅儿包子         |     0 | 2021-08 |                   200 |                     0 |
    |  9 | 面馅儿包子         |   300 | 2021-09 |                   200 |                   300 |
    |  8 | 面馅儿包子         |   200 | 2021-10 |                   200 |                   200 |
    |  7 | 面馅儿包子         |   700 | 2021-11 |                   200 |                   700 |
    +----+--------------------+-------+---------+-----------------------+-----------------------+
    12 rows in set (0.00 sec)
    

    5、其他函数:nth_value() / nfile()

    nfile()不常用,不再赘述;这里我们只提一下nth_value(expr,n)函数;

    nth_value用途:返回窗口中第n个expr的值。

    应用场景:截止到当前,显示陈哈哈包子铺月利润榜中排名第2和第3的成绩的利润。

    select *,
    nth_value(sales,2) over win as '当前排名第二的月收入',
    nth_value(sales,3) over win as '当前排名第三的月收入' 
    from chh_baozipu 
    window win as (partition by product order by `month`);
    
    mysql> select *,nth_value(sales,2) over win as '当前排名第二的月收入',nth_value(sales,3) over win as '当前排名第三的月收入' from chh_baozipu window win as (partition by product order by `month`);
    +----+--------------------+-------+---------+--------------------------------+--------------------------------+
    | id | product            | sales | month   | 当前排名第二的月收入           | 当前排名第三的月收入           |
    +----+--------------------+-------+---------+--------------------------------+--------------------------------+
    |  6 | 猪肉大葱包子       |  1000 | 2021-06 |                           null |                           null |
    |  5 | 猪肉大葱包子       |  1600 | 2021-07 |                           1600 |                           null |
    |  4 | 猪肉大葱包子       |   800 | 2021-08 |                           1600 |                            800 |
    |  3 | 猪肉大葱包子       |  1000 | 2021-09 |                           1600 |                            800 |
    |  2 | 猪肉大葱包子       |  1600 | 2021-10 |                           1600 |                            800 |
    |  1 | 猪肉大葱包子       |   600 | 2021-11 |                           1600 |                            800 |
    | 12 | 面馅儿包子         |   200 | 2021-06 |                           null |                           null |
    | 11 | 面馅儿包子         |   100 | 2021-07 |                            100 |                           null |
    | 10 | 面馅儿包子         |     0 | 2021-08 |                            100 |                              0 |
    |  9 | 面馅儿包子         |   300 | 2021-09 |                            100 |                              0 |
    |  8 | 面馅儿包子         |   200 | 2021-10 |                            100 |                              0 |
    |  7 | 面馅儿包子         |   700 | 2021-11 |                            100 |                              0 |
    +----+--------------------+-------+---------+--------------------------------+--------------------------------+
    12 rows in set (0.00 sec)
    

    本章小结

      窗口函数就说到这里,窗口函数是我接触mysql8以后发现的新东西,突然感觉mysql开发团队还是很灵性的,每个版本都会新增一些玩儿法,当然也很实用,希望mysql9.0会给我们带来更多的惊喜。

    到此这篇关于mysql窗口函数的具体使用的文章就介绍到这了,更多相关mysql窗口函数内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!

    (0)
    上一篇 2022年3月21日
    下一篇 2022年3月21日

    相关推荐