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

2024-06-04 2080阅读

文章目录

前言
1. SQL窗口函数

1.1 窗口函数概念
1.2 窗口函数语法
1.3 常见窗口函数

1.3.1 聚合窗口函数
1.3.2 专用窗口函数
1.4 窗口函数性能比较
2. LeetCode 例题

2.1 LeetCode SQL 178：分数排名
2.2 LeetCode SQL 184：最高工资
2.3 LeetCode SQL 185：前三工资
3. 项目实战

3.1 需求描述
3.2 SQL 实战
4. 补充与总结

4.1 `ROWS BETWEEN`子句常见关键字含义
4.2 如何理解窗口函数的“窗口”？
4.3 总结

参考资料

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

前言

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 窗口函数语法

窗口函数的语法如下：

窗口函数([参数]) OVER (
  [PARTITION BY ] 
  [ORDER BY ]
  [ROWS BETWEEN 开始行 AND 结束行]
)

PARTITION BY 子句用于指定分组列，关键字：PARTITION BY 。
ORDER BY 子句用于指定排序列，关键字ORDER BY 。

ROWS BETWEEN 子句用于指定窗口的范围，关键字ROWS BETWEEN 即[开始行]、[结束行]（这部分在“补充与总结”小节中作补充详细说明）。

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

窗口函数([参数]) OVER (
  [PARTITION BY ] 
  [ORDER BY ]

也正因此，本文将ROWS BETWEEN 子句相关关键字知识点将会以补充的形式说明，而侧重常用窗口函数的学习与练习，侧重PARTITION BY 子句与ORDER BY子句的使用。

语法举例，设有Order表，查询销售数量总和及其当前行前两行和后两行的销售数量总和：

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

这个例子暂时看不懂个没关系，接下来，我们会详细介绍常见窗口函数，并在介绍的过程中举例。之后，上述例子就很好理解了。

1.3 常见窗口函数

本小节介绍常见窗口函数。

若要跟着本文进行练习，则可以参考着创建如下表，本文的例子均基于下表：

设计一个销售数据表。该表包含以下字段：

id ：销售记录的唯一标识符（主键）
product ：产品名称
category ：产品类别
sale_date ：销售日期
quantity ：销售数量

revenue ：销售收入

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

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

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

1.3.1 聚合窗口函数

许多窗口函数的教程，通常将常用的窗口函数分为两大类：聚合窗口函数与专用窗口函数。聚合窗口函数的函数名与普通常用聚合函数一致，功能也一致。从使用的角度来讲，与普通聚合函数的区别在于提供了窗口函数的专属子句，来使得数据的分析与获取更简便。主要有如下几个：

函数名	作用
SUM	求和
AVG	求平均值
COUNT	求数量
MAX	求最大值
MIN	求最小值

区别

这个例子演示与普通聚合函数的区别。设我们要求使用一条查询语句，在sales表每行最后一列都加上这一行的产品类别 category的平均销售收入revenue，并且以category顺序排序，即如下图所示：

普通聚合函数的一种解法：

  SELECT 
	t1.*, 
	t2.avg_revenue FROM sales t1 
  LEFT JOIN (
		SELECT category, AVG(revenue) AS avg_revenue 
		FROM sales  
		GROUP BY category
  ) t2 ON t1.category = t2.category ORDER BY t1.category

聚合窗口函数:

SELECT
  sales.*,
  AVG( revenue ) OVER ( PARTITION BY category ) AS avg_revenue 
FROM
	sales

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

1.3.2 专用窗口函数

常见的专用窗口函数

函数名	分类	说明
RANK	排序函数	类似于排名，并列的结果序号可以重复，序号不连续
DENSE_RANK	排序函数	类似于排名，并列的结果序号可以重复，序号连续
ROW_NUMBER	排序函数	对该分组下的所有结果作一个排序，基于该分组给一个行数
PERCENT_RANK	分布函数	每行按照公式 (rank-1) / (rows-1) 进行计算
CUME_DIST	分布函数	分组内小于、等于当前 rank 值的行数 / 分组内总行数

练习

分别对上述表格常见的专用窗口函数进行调用，查看结果。

SELECT 
	*,
	RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_rank`,
	DENSE_RANK() OVER(PARTITION BY category ORDER BY product DESC) AS `product_dense_rank`,
	ROW_NUMBER() OVER(PARTITION BY category ORDER BY product DESC) AS `product_row_number`,
	PERCENT_RANK() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_percent_rank`,
	CUME_DIST() OVER(PARTITION BY category ORDER BY quantity DESC) AS `quantity_cume_dist`
FROM sales

至于其它专用窗口函数，请读者自行查阅其它资料做拓展。

1.4 窗口函数性能比较

通过对上面我们对窗口函数的学习与练习，我们一来明白了窗口函数的相关概念、常见窗口函数的使用以及这些窗口函数的作用与效果。也通过窗口函数与一般函数子查询作了一个简单的对比，体现了窗口函数在一些特定需求的强大。那么既然窗口函数如此强大，那么窗口函数的性能对比传统函数、传统子查询与分组的性能相比如何呢？

窗口函数的性能和其它SQL语句一样，受数据量大小、分区复杂度情况等影响。同等数量级的一般情况下：

窗口函数本身内嵌分组，相当于把条件先筛了一遍，可减少部分子查询。减少的子查询部分相当于降低了子查询本身的连接消耗。
窗口函数窗口大小限制，可减少部分行数结果返回消耗。
窗口函数可用于子查询，简化部分语句。但又因为用在了子查询，还是有一定连接开销。
窗口聚合函数在窗口函数原有分区、排序的基础上增加了聚合，且因不会影响行数的关系，比原有分组行数要多，其开销比一般聚合函数开销要大一些，因此窗口聚合函数一般情况下会比普通聚合函数性能差一些。
当然，上述只是理论上的性能初步分析，实际还得视具体的情况而定。
至于窗口函数优化方案，可以以影响窗口函数性能的原因为切入点由因到果进行优化，例如缩小窗口大小限制。篇幅有限，不作详解。详情可参考文末推荐的优秀参考文章。

2. LeetCode 例题

上一小节，我们学习了 SQL 窗口函数的概念，从本小节开始，就是做题练习与实战了！

接下来要列举例题，是 @CaptinKoo 两个月前教我们窗口函数时提供的练习题。让我们跟随 @CaptinKoo 老师的脚步，进行窗口函数练习吧！

2.1 LeetCode SQL 178：分数排名

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

题目描述


题解
根据题目描述，我们得知，返回结果序号可重复，连续，因此我们使用DENSE_RANK()函数。
```
SELECT 
    score,
    DENSE_RANK() OVER(ORDER BY score DESC) AS `rank`
FROM Scores 
ORDER BY score DESC
```
2.2 LeetCode SQL 184：最高工资

题目链接
LeetCode-SQL184部门工资最高的员工

题目描述



题解
根据描述，我们可以通过 RANK 窗口函数对 Employee 表进行排序，获取 rank 值为1 的员工并关联到部门表。
```
SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM (
    SELECT 
    name, 
    salary,
    departmentId, 
    RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
    FROM Employee
) e 
LEFT JOIN Department d 
    ON e.departmentId = d.id
WHERE e.`rank` = 1
```
2.3 LeetCode SQL 185：前三工资

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

题目描述



题解
有了上面两道题的解题练习，这道题也迎刃而解：
```
SELECT 
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM (
    SELECT 
    name, 
    salary,
    departmentId, 
    DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS `rank`
    FROM Employee
) e 
LEFT JOIN Department d 
    ON e.departmentId = d.id
WHERE e.`rank` 
```