SQL 必知必会：手把手教你掌握 MySQL 开窗函数的 4 大核心场景（附代码）

目录

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

SQL 代码：

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

结果演示：

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

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

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

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

SQL 代码：

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

结果演示：

解析：

• 小明的眼镜里看到的是“一班”的平均分（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

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;

结果演示：

解析：

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

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

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