目录

MySQL开窗函数

核心概念：什么是“窗”？

语法结构：

场景一：聚合类开窗（SUM, AVG）

 场景二：分组开窗（PARTITION BY）

 场景三：排名函数（RANK, DENSE_RANK, ROW_NUMBER）

1. ROW_NUMBER()：强行排座次

2. RANK()：中国式排名（并列跳跃）

3. DENSE_RANK()：密集排名（并列不跳跃）

场景四：错位计算（LAG, LEAD）

总结：什么时候用开窗函数？

MySQL开窗函数

MySQL 开窗函数（Window Function），是 MySQL 8.0 版本开始支持的一项核心功能--1，专为处理复杂的数据分析任务而设计。

简单来说，它可以在不减少行数的情况下，对一组数据进行计算（比如求排名、求累计和）。

很多新手觉得它难，是因为它打破了我们对 SQL 的固有认知。

• 普通聚合（GROUP BY）：是把一堆数据揉成一个球（多行变一行）。
• 开窗函数（OVER）：是给每一行数据装上一双眼睛，让它能看到周围的数据，但自己还是自己（多行变多行）。

为了让你彻底搞懂，我们用一个“班级成绩单”的例子，把开窗函数拆解成三个步骤来讲。

核心概念：什么是“窗”？

想象你站在一个长长的队伍里（这就是你的数据表）。

• 普通聚合：老师让全班同学抱成一团，只告诉你全班的平均分。你失去了自我，变成了一个数字。
• 开窗函数：老师给你戴上了一副“智能眼镜”。
  • 你还是站在队伍里（行数不变）。
  • 但你的眼镜上显示了各种信息：全班的平均分、你在班里的排名、你前一个人的分数……

这就是开窗函数的核心：不改变行数，但能进行跨行计算。

语法结构：

函数名() OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS BETWEEN ...] )

• 函数名：比如 SUM, AVG, RANK, ROW_NUMBER。
• OVER()：这是开启“智能眼镜”的开关。
• PARTITION BY：分组。相当于把大队伍拆成几个小队（比如按“班级”分队）。如果不写，默认全表是一个队。
• ORDER BY：排序。相当于规定队伍按什么顺序排（比如按“分数”从高到低）。

场景一：聚合类开窗（SUM, AVG）

场景：我想知道我的分数，以及我比全班平均分高多少？

如果不使用开窗函数，你需要写子查询，非常麻烦。使用开窗函数，只需要一行代码。

数据表：students

id	name	class	score
1	小明	一班	90
2	小红	一班	80
3	小刚	二班	95
4	小兰	二班	85

SQL 代码：

SELECT 
    name,
    class,
    score,
    AVG(score) OVER () AS 全班平均分,  -- 整个表算一个平均分
    score - AVG(score) OVER () AS 分差   -- 当前行分数 - 全班平均分
FROM students;

结果演示：

name	class	score	全班平均分	分差
小明	一班	90	87.5	2.5
小红	一班	80	87.5	-7.5
小刚	二班	95	87.5	7.5
小兰	二班	85	87.5	-2.5

解析：
注意看，全班平均分 这一列，每一行都是 87.5。开窗函数把全表的平均分“广播”到了每一行，让你可以直接做减法。

---

 场景二：分组开窗（PARTITION BY）

场景：我想知道我的分数，以及我比“我自己班级”的平均分高多少？

这时候就需要 PARTITION BY 了。它相当于把“一班”和“二班”隔离开，分别计算。

SQL 代码：

SELECT 
    name,
    class,
    score,
    AVG(score) OVER (PARTITION BY class) AS 班级平均分
FROM students;

结果演示：

name	class	score	班级平均分
小明	一班	90	85 (一班平均)
小红	一班	80	85 (一班平均)
小刚	二班	95	90 (二班平均)
小兰	二班	85	90 (二班平均)

解析：

• 小明的眼镜里看到的是“一班”的平均分（85）。
• 小刚的眼镜里看到的是“二班”的平均分（90）。
• 这就是 PARTITION BY ：组内计算，组间隔离。

---

 场景三：排名函数（RANK, DENSE_RANK, ROW_NUMBER）

这是面试和实际业务中最常用的场景。假设有三个人的分数分别是：100, 100, 90。

1. ROW_NUMBER()：强行排座次

规则：1, 2, 3
解释：哪怕分数一样，我也要强行分个先后（通常按数据库存储顺序或随机）。
适用场景：分页查询（每页显示10条），必须保证行号唯一。

2. RANK()：中国式排名（并列跳跃）

规则：1, 1, 3
解释：两个人并列第一，下一名直接跳到第三名（因为前面占了两个坑）。
适用场景：比赛颁奖，金牌有两个，就没有银牌了，直接发铜牌。

3. DENSE_RANK()：密集排名（并列不跳跃）

规则：1, 1, 2
解释：两个人并列第一，下一名是第二名。
适用场景：等级划分。比如 90分以上是A级，不管多少人90分，89分就是B级。

SQL 代码实战：

SELECT 
    name, 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS 强行排名,
    RANK()       OVER (ORDER BY score DESC) AS 并列跳跃,
    DENSE_RANK() OVER (ORDER BY score DESC) AS 并列不跳
FROM students;

场景四：错位计算（LAG, LEAD）

场景：计算每个月的销量增长量（本月销量 - 上月销量）

这是开窗函数最“神”的地方。LAG 可以让你看到上一行的数据，LEAD 可以让你看到下一行的数据。

数据表：tb_sales

month	sales
1月	100
2月	120
3月	110

SQL 代码：

SELECT 
    month,
    sales AS 本月销量,
    LAG(sales, 1) OVER (ORDER BY month) AS 上月销量,
    sales - LAG(sales, 1) OVER (ORDER BY month) AS 环比增长
FROM tb_sales;

结果演示：

month	本月销量	上月销量	环比增长
1月	100	NULL	NULL
2月	120	100	20
3月	110	120	-10

解析：

• 在 2月 这一行，LAG(sales, 1) 向上看了一眼，抓到了 1月 的 100。
• 然后直接做减法：120 - 100 = 20。
• 如果没有开窗函数，你需要把表自己连接自己（Self Join），代码会复杂好几倍！

总结：什么时候用开窗函数？

当你发现你需要“既要...又要...”的时候，就是开窗函数登场的时候：

1. 既要保留明细数据，又要看聚合统计（如：显示每个订单，同时显示该用户的总消费额）。 > 用 SUM() OVER (PARTITION BY user_id)
2. 既要看当前行，又要和上一行/下一行做对比（如：同比、环比）。 -> 用 LAG() 或 LEAD()
3. 既要排序，又要保留所有数据（如：取每个班级的前3名）。 -> 用 ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC)