【MySQL】窗口函数详解（概念+练习+实战）

牛逼的兄弟两个月前教了我一招......

前言

2023年12月下旬，广东终于冷了！追念直到12月15那天，依然穿着短袖上班，吹着风扇空调睡觉… 哈哈，这是截至发文时的一些感受与题外话。气候是冷了，但心中依然热情似火，一是工作业务上又有轻微复杂的业务，有挑战；二是虽然有挑战，但想起牛逼的兄弟@CaptinKoo两个月前教了我一招：SQL窗口函数，业务难题迎刃而解！趁着这次解决难题的热度，将本次学到的窗口函数知识点以及项目实战记载下来，供各位分享。
我个人学习窗口函数主要有两个用处：一是对现有SQL知识的拓展，二是能利用窗口函数对一些特定场景做SQL简化，解决复杂题目。
但在正式开始之前，得事先说明一个条件：
条件

• 窗口函数是 Mysql 8 的新特性。本文的学习与演示，都基于Mysql 8
  
• 学习窗口函数，建议有肯定的SQL底子
  

学习目标

• 学习并相识SQL窗口函数相关概念
  
• 能利用SQL窗口函数解决部分业务场景题目，项目实战
  
• 若实际业务用得少，那上述知识相识一下即可，建议收藏本文，用到的时候可以翻出来参考
  

下面我们开始！
1. SQL窗口函数

这一末节我们介绍窗口函数的一些概念。
1.1 窗口函数概念

概念
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据举行实时分析处理。
窗口函数在MySQL 8中引入，是Mysql 8的新特性。是一种主要用于数据分析、特定字段分组等的一种特别的函数。
常见利用场景

• 数据分析，如排名、排序、分组统计、计算、前后值比力等
  
• 对某些分组场景简化SQL，提升效率
  
• 常用于子查询，将一些复杂条件简化
  

1.2 窗口函数语法

窗口函数的语法如下：

1. 窗口函数([参数]) OVER (
2.   [PARTITION BY <分组列>]
3.   [ORDER BY <排序列 ASC/DESC>]
4.   [ROWS BETWEEN 开始行 AND 结束行]
5. )

复制代码

• PARTITION BY 子句用于指定分组列，关键字：PARTITION BY 。
  
• ORDER BY 子句用于指定排序列，关键字ORDER BY 。
  
• ROWS BETWEEN 子句用于指定窗口的范围，关键字ROWS BETWEEN 即[开始行]、[结束行]（这部分在“增补与总结”末节中作增补具体说明）。
  

此中，ROWS BETWEEN 子句在实际中可能用得相对少一些，因此有部分参考资料的语法形貌省略了ROWS BETWEEN 子句，主要侧重于PARTITION BY分组与ORDER BY排序：

1. 窗口函数([参数]) OVER (
2.   [PARTITION BY <分组列>]
3.   [ORDER BY <排序列 ASC/DESC>]

复制代码

也正因此，本文将ROWS BETWEEN 子句相关关键字知识点将会以增补的形式说明，而侧重常用窗口函数的学习与练习，侧重PARTITION BY 子句与ORDER BY子句的利用。
语法举例，设有Order表，查询销售数量总和及其当前行前两行和后两行的销售数量总和：

1. SELECT product_id, order_date, quantity,
2.        SUM(quantity) OVER (PARTITION BY product_id ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS sum_surrounding_quantities
3. FROM orders

复制代码

这个例子暂时看不懂个不要紧，接下来，我们会具体介绍常见窗口函数，并在介绍的过程中举例。之后，上述例子就很好明白了。
1.3 常见窗口函数

本末节介绍常见窗口函数。
若要跟着本文举行练习，则可以参考着创建如下表，本文的例子均基于下表：
设计一个销售数据表。该表包含以下字段：

• id ：销售记载的唯一标识符（主键）
  
• product ：产物名称
  
• category ：产物种别
  
• sale_date ：销售日期
  
• quantity ：销售数量
  
• revenue ：销售收入
  

以下是创建表的DDL以及 插入模拟数据的DML

1. CREATE TABLE sales (
2.   id INT PRIMARY KEY,
3.   product VARCHAR(50),
4.   category VARCHAR(50),
5.   sale_date DATE,
6.   quantity INT,
7.   revenue DECIMAL(10, 2)
8. );
10. INSERT INTO sales (id, product, category, sale_date, quantity, revenue)
11. VALUES
12.   (1, 'Product A', 'Category 1', '2022-01-01', 10, 100.00),
13.   (2, 'Product B', 'Category 1', '2022-01-01', 5, 50.00),
14.   (3, 'Product A', 'Category 2', '2022-01-02', 8, 80.00),
15.   (4, 'Product B', 'Category 2', '2022-01-02', 3, 30.00),
16.   (5, 'Product A', 'Category 1', '2022-01-03', 12, 120.00),
17.   (6, 'Product B', 'Category 1', '2022-01-03', 7, 70.00),
18.   (7, 'Product A', 'Category 2', '2022-01-04', 6, 60.00),
19.   (8, 'Product B', 'Category 2', '2022-01-04', 4, 40.00);

复制代码

好的，预备工作完成，下面我们一边学习具体窗口函数并练习吧！
1.3.1 聚合窗口函数

许多窗口函数的教程，通常将常用的窗口函数分为两大类：聚合窗口函数 与 专用窗口函数。聚合窗口函数的函数名与平凡常用聚合函数同等，功能也同等。从利用的角度来讲，与平凡聚合函数的区别在于提供了窗口函数的专属子句，来使得数据的分析与获取更轻便。主要有如下几个：
函数名作用SUM求和AVG求平均值COUNT求数量MAX求最大值MIN求最小值 区别
这个例子演示与平凡聚合函数的区别。设我们要求利用一条查询语句，在sales表每行最后一列都加上这一行的产物种别 category的 平均 销售收入revenue，而且以category次序排序，即如下图所示：

• 平凡聚合函数的一种解法：
  

1.   SELECT
2.         t1.*,
3.         t2.avg_revenue FROM sales t1
4.   LEFT JOIN (
5.                 SELECT category, AVG(revenue) AS avg_revenue
6.                 FROM sales  
7.                 GROUP BY category
8.   ) t2 ON t1.category = t2.category ORDER BY t1.category

复制代码

• 聚合窗口函数:
  

1. SELECT
2.   sales.*,
3.   AVG( revenue ) OVER ( PARTITION BY category ) AS avg_revenue
4. FROM
5.         sales

复制代码

这么一对比，窗口聚合函数简单不少！
1.3.2 专用窗口函数

常见的专用窗口函数
函数名分类说明RANK排序函数类似于排名，并列的结果序号可以重复，序号不一连DENSE_RANK排序函数类似于排名，并列的结果序号可以重复，序号一连ROW_NUMBER排序函数对该分组下的所有结果作一个排序，基于该分组给一个行数PERCENT_RANK分布函数每行按照公式 (rank-1) / (rows-1) 举行计算CUME_DIST分布函数分组内小于、即是当前 rank 值的行数 / 分组内总行数 练习
分别对上述表格常见的专用窗口函数举行调用，查看结果。

1. SELECT
2.         *,
3.         RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_rank`,
4.         DENSE_RANK() OVER(PARTITION BY category ORDER BY product DESC) AS `product_dense_rank`,
5.         ROW_NUMBER() OVER(PARTITION BY category ORDER BY product DESC) AS `product_row_number`,
6.         PERCENT_RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_percent_rank`,
7.         CUME_DIST() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_cume_dist`
8. FROM sales

复制代码

至于别的专用窗口函数，请读者自行查阅别的资料做拓展。
1.4 窗口函数性能比力

通过对上面我们对窗口函数的学习与练习，我们一来明白了窗口函数的相关概念、常见窗口函数的利用以及这些窗口函数的作用与效果。也通过窗口函数与一般函数子查询作了一个简单的对比，表现了窗口函数在一些特定需求的强大。那么既然窗口函数云云强大，那么窗口函数的性能对比传统函数、传统子查询与分组的性能相比怎样呢？
窗口函数的性能和别的SQL语句一样，受数据量巨细、分区复杂度情况等影响。划一数量级的一般情况下：

• 窗口函数本身内嵌分组，相称于把条件先筛了一遍，可镌汰部分子查询。镌汰的子查询部分相称于低沉了子查询本身的毗连消耗。
  
• 窗口函数窗口巨细限制，可镌汰部分行数结果返回消耗。
  
• 窗口函数可用于子查询，简化部分语句。但又由于用在了子查询，照旧有肯定毗连开销。
  
• 窗口聚合函数在窗口函数原有分区、排序的底子上增长了聚合，且因不会影响行数的关系，比原有分组行数要多，其开销比一般聚合函数开销要大一些，因此窗口聚合函数一般情况下会比平凡聚合函数性能差一些。
  

当然，上述只是理论上的性能初步分析，实际还得视具体的情况而定。
至于窗口函数优化方案，可以以影响窗口函数性能的起因于切入点由因到果举行优化，例如缩小窗口巨细限制。篇幅有限，不作详解。详情可参考文末推荐的优秀参考文章。
2. LeetCode 例题

上一末节，我们学习了 SQL 窗口函数的概念，从本末节开始，就是做题练习与实战了！
接下来要列举例题，是 @CaptinKoo 两个月前教我们窗口函数时提供的练习题。让我们跟随 @CaptinKoo 老师的脚步，举行窗口函数练习吧！
2.1 LeetCode SQL 178：分数排名

题目链接
LeetCode-SQL178分数排名链接
题目形貌

题解
根据题目形貌，我们得知，返回结果序号可重复，一连，因此我们利用DENSE_RANK()函数。

1. SELECT
2.     score,
3.     DENSE_RANK() OVER(ORDER BY score DESC) AS `rank`
4. FROM Scores
5. ORDER BY score DESC

复制代码

2.2 LeetCode SQL 184：最高工资

题目链接
LeetCode-SQL184部分工资最高的员工
题目形貌

题解
根据形貌，我们可以通过 RANK 窗口函数对 Employee 表举行排序，获取 rank 值为1 的 员工并关联到部分表。

1. SELECT
2.     d.name AS Department,
3.     e.name AS Employee,
4.     e.salary AS Salary
5. FROM (
6.     SELECT
7.     name,
8.     salary,
9.     departmentId,
10.     RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
11.     FROM Employee
12. ) e
13. LEFT JOIN Department d
14.     ON e.departmentId = d.id
15. WHERE e.`rank` = 1

复制代码

2.3 LeetCode SQL 185：前三工资

题目链接
LeetCode-SQL185部分工资前三高的所有员工
题目形貌

题解
有了上面两道题的解题练习，这道题也迎刃而解：

1. SELECT
2.     d.name AS Department,
3.     e.name AS Employee,
4.     e.salary AS Salary
5. FROM (
6.     SELECT
7.     name,
8.     salary,
9.     departmentId,
10.     DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
11.     FROM Employee
12. ) e
13. LEFT JOIN Department d
14.     ON e.departmentId = d.id
15. WHERE e.`rank` <= 3

复制代码

太棒了！我们一下就完成了三道包括中等、困难难度的LeetCode题目，接下来，我们可以将我们学习并练习过的知识点用于项目实战了！
3. 项目实战

本末节是我个人用窗口函数解决实际工作题目标实战记载。涉及的表、字段均已做形貌更换，脱敏处理。
3.1 需求形貌

已知用户订单评价表order_evaluate有如下字段：
字段名类型说明idbigint评价表主键idevaluatetext评价内容user_idbigint用户idupdate_timedatetime更新时间order_idbigint订单id 此中，每个订单可以有多个评价，每个评价都可以修改。业务必要，必要获取当前用户所有订单最近一次评价内容，并返回订单id、最近一次评价的内容。
3.2 SQL 实战

此次实战业务必要根据update_time获取最近一次批评并根据order_id举行分组。
在尚未体系学习窗口函数时，我们第一时间会想到的是传统子查询。
但毕竟实际业务远比这里的脱敏形貌要复杂，一时间难以实现，于是我第一时间回首了@CaptinKoo大佬教我的窗口函数并解决：

1. # 毕竟是Demo，忽略 user_id 条件，实际业务会补充齐全其它条件
2. SELECT
3.         t1.order_id,
4.         evaluate
5. FROM
6.         order_evaluate t1
7.         INNER JOIN (
8.                 SELECT id,
9.                 order_id,
10.                 ROW_NUMBER() OVER ( PARTITION BY order_id ORDER BY update_time DESC ) AS row_num FROM order_evaluate) t2 ON t1.id = t2.id
11. WHERE
12.         t2.row_num = 1

复制代码

这条SQL是通过ROW_NUM()函数将工单评价表根据工单分组，更新时间倒序并给它一个行序号。行序号1的就是我们要求的结果。
你能想到用传统子查询实现类似功能的SQL吗？
4. 增补与总结

4.1 ROWS BETWEEN子句常见关键字寄义

关键字及其寄义表
关键字寄义PRECEDING当前行数往前FOLLOWING当前行数今后CURRENT ROW当前行UNBOUNDED起点（一般结合PRECEDING，FOLLOWING利用）UNBOUNDED PRECEDING表示该窗口最前面的行（起点）UNBOUNDED FOLLOWING表示该窗口最后面的行（尽头） 此表的知识内容来自于参考文章
根据这个关键字寄义表，读者可以明白文初提到的例子了吗？
可选挑战题目
这里提供一题可选的挑战题目链接，是LeetCode困难题目，依然来自@CaptinKoo大佬的推荐，此题的一种解法用到ROWS BETWEEN子句。
LeetCode-601体育馆的人流量
4.2 怎样明白窗口函数的“窗口”？

既然这种函数叫"窗口函数"，那么它应该可以像"窗口"一样，通过滚动的方式，获取肯定范围内的视图。
而滚动的方式恰好就是ROWS BETWEEN子句。通过ROWS BETWEEN子句，获取窗口函数结果的范围，从而有给用户"窗口"的感觉。
用术语表达，则是：通过定义帧，决定窗口的巨细。
窗口函数定义帧通常有两种方式：RANGE和ROWS, 两者决定窗口帧的界限怎样计算。

• RANGE 基于排序列的值定义帧
  
• ROWS 基于行数定义帧，不思量排序列
  

由于两者用法相似，且一般ROWS BETWEEN 子句会用得多一些，因此本文的语法概述忽略了RANGE子句。此处作为增补，供读者参考。
4.3 总结

本文借由好兄弟@CaptinKoo两个月前教过我的窗口函数知识，截至发文日期顺遂解决一个相对比力复杂的业务的故事，记载我从CaptinKoo学到的窗口函数相关知识，以及CaptinKoo大佬推荐的相关习题，以及我个人本次实战的脱敏形貌。
通过本文，我们学习到了：

• Mysql 窗口函数相关概念：此中，语法结构是重点；
  
• 常见窗口函数：聚合窗口函数、专用窗口函数（排序函数、分布函数等）
  
• 相关习题与练习
  
• 一个实际的练习供大佬们参考
  
• 窗口函数“窗口”的表现，ROWS BETWEEN子句相关增补知识点
  

参考资料

• 知乎-窗口函数优秀参考文章1
  
• 知乎-窗口函数优秀参考文章2
  
• CSDN-窗口函数优秀参考文章1
  
• CSDN-窗口函数优秀参考文章2
  

再次感谢@CaptinKoo的引导！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。