MySQL窗⼝函数的具体使⽤
⽬录
⼀、什么是窗⼝函数
1、怎么理解窗⼝?
2、什么是窗⼝函数
⼆、窗⼝函数⽤法
1、序号函数：row_number() / rank() / den_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 t (0.00 c)
怎么说？什么时候来我店⾥，请⼤家吃⾯馅⼉包⼦。
⼀、什么是窗⼝函数
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 t (0.00 c)
从这条SQL可以看出，对于第⼀⾏id=6这⾏的窗⼝就是第⼀⾏，对于第⼆⾏id=5这⾏的窗⼝就是前两⾏，以此类推（如下图）。
可见，窗⼝就是范围的意思，可以理解为⼀些记录（⾏）的集合；窗⼝函数也就是在满⾜某种条件的记录集合上执⾏计算的特殊函数。
对于每条记录都要在此窗⼝内执⾏函数，有的函数随着记录不同，窗⼝⼤⼩都是固定的，这种属于静态窗⼝；有的函数则相反，不同的记录对应着不同的窗⼝，这种动态变化的窗⼝叫滑动窗⼝。看完本⽂再回来看这句话相信会理解的更透彻[⼿动狗头]。
2、什么是窗⼝函数
窗⼝函数也叫OLAP函数（Online Anallytical Processing），可以对数据进⾏实时分析处理。
考研安排窗⼝函数多⽤在什么场景？主要有以下两类：
排名问题，例如：查包⼦铺利润⽉排名；
TOPN问题，例如：查每种包⼦利润最⾼的两个⽉；
我们常见的窗⼝函数和聚合函数有这些：
专⽤窗⼝函数：rank()，den_rank()，row_number()
聚合函数：max()，min()，count()，sum()，avg()
因为聚合函数也可以放在窗⼝函数中使⽤，因此窗⼝函数和普通聚合函数也很容易被混淆，⼆者区别如下：
聚合函数是将多条记录聚合为⼀条；⽽窗⼝函数是每条记录都会执⾏，有⼏条记录执⾏完还是⼏条。
聚合函数也可以⽤于窗⼝函数中，这个我会举例说明。
⼆、窗⼝函数⽤法
基本语法：
<；窗⼝函数> OVER (PARTITION BY <⽤于分组的列名> ORDER BY <⽤于排序的列名>);
-- over关键字⽤于指定函数的窗⼝范围，
-- partition by ⽤于对表分组，
-- order by⼦句⽤于对分组后的结果进⾏排序。
注意：窗⼝函数是对where或者group by⼦句处理后的结果再进⾏⼆次操作，因此会按照SQL语句的运⾏顺序，窗⼝函数⼀般放在lect⼦句中（from前），例如上⼀条SQL，可以往上拖着看看~
窗⼝函数都有哪些？懒得画了，借lulin916⽼哥的导图⼀⽤~~
序号函数：row_number() / rank() / den_rank()
分布函数：percent_rank() / cume_dist()
前后函数：lag() / lead()
头尾函数：first_val() / last_val()
其他函数：nth_value() / nfile()
让我们来分别举例看⼀看：
1、序号函数：row_number() / rank() / den_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() o
ver(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 t (0.00 c)
如上述⽰例可见，三个窗⼝函数服务与不同的三个典型业务需求，这三种⾜以应对我们的排序统计。
以后同学们在⾯试或笔试时被问到时，请不要再说⾃查询嵌套之类的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 t (0.00 c)
这⾥我想问⼀下同学们是不是发现这条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 t (0.00 c)
②计算当前⾏与前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 t (0.00 c)
吴兴杂诗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 t (0.00 c)
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 t (0.00 c)
本章⼩结
窗⼝函数就说到这⾥，窗⼝函数是我接触MySQL8以后发现的新东西，突然感觉MySQL开发团队还是很灵性的，每个版本都会新增⼀些玩⼉法，当然也很实⽤，希望MySQL9.0会给我们带来更多的惊喜。
到此这篇关于MySQL窗⼝函数的具体使⽤的⽂章就介绍到这了，更多相关MySQL窗⼝函数内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！
