MySQL 一定要会的窗口函数

MySQL 窗口函数（Window Functions） 是 MySQL 8.0 引入的一项强大功能，主要用于数据分析。它们允许在查询结果的某些行上执行计算，并返回这些行，同时保留其各自的行。窗口函数非常适合排名、累计和移动平均等操作。

1.窗口函数的基本概念

窗口函数，也叫 OLAP 函数（Online Analytical Processing），可以对数据库数据进行实时分析处理。它们与普通的聚合函数不同，不会对结果进行分组，使得输出中的行数与输入中的行数相同。

2.窗口函数的语法

窗口函数的基本语法如下：

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

• PARTITION BY 子句用于指定分组列，类似于 GROUP BY。
• ORDER BY 子句用于指定排序列。
• ROWS BETWEEN 子句用于指定窗口的范围。

3.常见的窗口函数

1. 聚合窗口函数：如 SUM(), AVG(), COUNT(), MAX(), MIN() 等。
2. 排序相关的函数：如 ROW_NUMBER(), RANK(), DENSE_RANK() 等。
3. 前后行值访问：如 LAG(), LEAD() 等。

4.窗口函数示例

假设有一个销售数据表 sales，包含以下字段：id, product, category, sale_date, quantity, revenue。我们可以使用窗口函数来计算每个产品的累计销售数量：

SELECT 
    product, 
    sale_date, 
    quantity, 
    SUM(quantity) OVER (PARTITION BY product ORDER BY sale_date) AS cumulative_quantity
FROM 
    sales;

这个查询会根据 product 分组，并按 sale_date 排序，计算每个产品的累计销售数量。

5.补充排序和前后值

ROW_NUMBER()

ROW_NUMER() 为每一行分配一个唯一的序号，即使两行的值相同，它们也会被分配不同的序号。它的排序是连续的，没有间隔。

语法：

ROW_NUMBER() OVER (PARTITION BY <分组列> ORDER BY <排序列>)

示例：根据每个员工的薪水降序排列，并分配一个唯一的行号。

SELECT 
    name, 
    salary, 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM 
    employees;

RANK()

RANK() 为每一行分配一个序号，当两行的值相同时，它们会被分配相同的序号。排名会有跳跃，比如，如果有两个第二名，那么接下来的排名就是第四名。

语法：薪水相同的员工会有相同的排名，但排名会跳跃。

RANK() OVER (PARTITION BY <分组列> ORDER BY <排序列>)

示例：

SELECT 
    name, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM 
    employees;

DENSE_RANK()

DENSE_RANK() 与 RAN() 类似，就是相同的行也会分配连续的序号，但是下一行的序号不会有间隔。比如，如果有两个第二名，那么接下来的排名就是第三名。

语法：

DENSE_RANK() OVER (PARTITION BY <分组列> ORDER BY <排序列>)

示例：薪水相同的员工会有相同的排名，并且排名是连续的。

SELECT 
    name, 
    salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM 
    employees;

LAG()

LAG() 函数返回当前行之前第 N 行的值，如果 N 行不存在，则返回指定的默认值或 NULL。

语法：

LAG(column_name, offset, default_value) OVER (PARTITION BY <分组列> ORDER BY <排序列>)

示例：查看每个产品的销售额变化情况，即当前销售额与前一条记录的比较。

SELECT 
    product_id, 
    sale_date, 
    amount, -- offset 表示向前偏移的行数，这里是 1
    LAG(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_amount
FROM 
    sales;

LEAD()

LEAD() 函数与 LAG() 相反，它返回当前行之后的第 N 行数据，如果后 N 行不存在，则返回默认值或 NULL 。

语法：

LEAD(column_name, offset, default_value) OVER (PARTITION BY <分组列> ORDER BY <排序列>)

示例：查看每个产品的当前销售额和下一条记录的销售额。

SELECT 
    product_id, 
    sale_date, 
    amount, 
    LEAD(amount, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_amount
FROM 
    sales;

6.实战练习

可以参考一些在线练习平台如 LeetCode 上的 SQL 题目来练习窗口函数的使用。

LeetCode SQL 178.分数排名

表: Scores

Column Name	Type
id	int
score	decimal

id 是该表的主键（有不同值的列）。
该表的每一行都包含了一场比赛的分数。Score 是一个有两位小数点的浮点值。

编写一个解决方案来查询分数的排名。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Scores 表:

id	score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

输出:

score	rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

---

解题思路：根据分数排序的思路，我们可以使用 DENSE_RANK() 进行排序。

SELECT
    score, DENSE_RANK() OVER(ORDER BY score DESC) AS 'rank'
FROM Scores;

LeetCode SQL 180.连续出现的数字

表：Logs

Column Name	Type
id	int
num	varchar

在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

结果格式如下面的例子所示：

示例 1:

输入：
Logs 表：

id	num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

输出：
Result 表：

ConsecutiveNums
1

解释： 1 是唯一连续出现至少三次的数字。

---

解题思路：要判断是否是连续出现的数字，那么我们可以去判断它的前两位数字与当前数字是否相等，因此，我们这里可以使用 LAG() 函数去找出前两个数字，然后通过子查询去进行判断。

WITH numbered_logs AS (
    SELECT 
        num AS ConsecutiveNums,
        LAG(num, 1) OVER (ORDER BY id) AS prev_num,
        LAG(num, 2) OVER (ORDER BY id) AS prev_prev_num
    FROM logs
)
SELECT distinct ConsecutiveNums
FROM numbered_logs
WHERE prev_num = ConsecutiveNums AND prev_prev_num = prev_num;

LeetCode SQL 1789.员工的直属部门

表：Employee

Column Name	Type
employee_id	int
department_id	int
primary_flag	varchar

这张表的主键为 employee_id, department_id (具有唯一值的列的组合)
employee_id 是员工的ID
department_id 是部门的ID，表示员工与该部门有关系
primary_flag 是一个枚举类型，值分别为(‘Y’, ‘N’). 如果值为’Y’,表示该部门是员工的直属部门。 如果值是’N’,则否

一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.

请编写解决方案，查出员工所属的直属部门。

返回结果 没有顺序要求 。

返回结果格式如下例子所示：

示例 1：

输入：
Employee table:

employee_id	department_id	primary_flag
1	1	N
2	1	Y
2	2	N
3	3	N
4	2	N
4	3	Y
4	4	N

输出：

employee_id	department_id
1	1
2	1
3	3
4	3

解释：

• 员工 1 的直属部门是 1
• 员工 2 的直属部门是 1
• 员工 3 的直属部门是 3
• 员工 4 的直属部门是 3

---

解题思路：我们发现，员工直属部门可能会有两种情况。情况一，就是 primary_flag 为 Y；情况二，就是部门数为 cnt 为 1 的情况。除了可以用 UNION 将其合并之外，还能用窗口函数计算每个员工的部门数，然后通过子查询得到结果。

select
employee_id, department_id
from (
    select
    employee_id,
    department_id,
    primary_flag,
    count(*) over(partition by employee_id) cnt
    from Employee
) T
where cnt = 1 or primary_flag = 'Y';

LeetCode SQL 1204.最后一个能进入巴士的人

表: Queue

Column Name	Type
person_id	int
person_name	varchar
weight	int
turn	int

person_id 是这个表具有唯一值的列。
该表展示了所有候车乘客的信息。
表中 person_id 和 turn 列将包含从 1 到 n 的所有数字，其中 n 是表中的行数。
turn 决定了候车乘客上巴士的顺序，其中 turn=1 表示第一个上巴士，turn=n 表示最后一个上巴士。
weight 表示候车乘客的体重，以千克为单位。

有一队乘客在等着上巴士。然而，巴士有1000 千克 的重量限制，所以其中一部分乘客可能无法上巴士。

编写解决方案找出 最后一个 上巴士且不超过重量限制的乘客，并报告 person_name 。题目测试用例确保顺位第一的人可以上巴士且不会超重。

返回结果格式如下所示。

示例 1：

输入：
Queue 表

person_id	person_name	weight	turn
5	Alice	250	1
4	Bob	175	5
3	Alex	350	2
6	John Cena	400	3
1	Winston	500	6
2	Marie	200	4

输出：

person_name
John Cena

解释：
为了简化，Queue 表按 turn 列由小到大排序。

Turn	ID	Name	Weight	Total Weight
1	5	Alice	250	250
2	3	Alex	350	600
3	6	John Cena	400	1000(最后一个上)
4	2	Marie	200	1200(无法上巴士)
5	4	Bob	175	-
6	1	Winston	500	-

---

解题思路：根据题目我们自然能想到要根据 turn 进行排序，然后累计计算 Weight，只要找到最接近 1000 的 ID 就好了。而通过窗口函数，我们就可以实现累计计算，非常方便。

SELECT person_name
FROM
    (SELECT 
    *,sum(weight) OVER(ORDER BY turn) AS SUMWEIGHT
    FROM Queue) T
WHERE SUMWEIGHT <= 1000
ORDER BY SUMWEIGHT DESC
LIMIT 0,1;