MySQL窗口函数的具体使用

吾爱主题阅读：172 2024-04-02 08:07:12 评论：0

一、什么是窗口函数
- 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…陈哈哈的包子铺。悄悄告诉你，哈哥今年盘了个包子铺卖包子，这张表就是包子铺这半年的利润~

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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、怎么理解窗口?

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

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

1 2	`select` `*,` `sum` `(sales) over(` `order` `by` ` `month` `) `as` `累计利润` `from` `chh_baozipu` `where` `product=` `'猪肉大葱包子'` `;`

1 2 3 4 5 6 7 8 9 10 11 12 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()

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

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

二、窗口函数用法

基本语法：

1 2 3 4 <窗口函数> 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

1 2 3 4 5 6 7 8 9 10 11 12 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。

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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不同？有哪几个地方不同呢？

1 2 3 4 5 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行）的聚合窗口函数

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

1 2 3 select *, sum (sales) over win as '近三个月利润相加' from chh_baozipu window win as (partition by product order by ` month ` rows 2 preceding);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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行的聚合窗口函数

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

1 2 3 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);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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的值；

应用场景：截止到当前，按照日期排序查询当前最大月收入和当前最小月收入。

1 2 3 4 5 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 `);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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的成绩的利润。

1 2 3 4 5 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 `);

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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)