前言
在前序内容中，我们掌握了事务、隔离级别等数据库核心机制，从本篇开始正式学习MySQL 8.0 重磅新特性 —— 窗口函数。窗口函数是数据分析、报表统计、岗位排名、分组 TopN 场景的利器，也是中高级开发、数据分析师面试高频必考知识点。
在 MySQL 5.7 及更早版本中，实现分组排名、累计统计、行间对比等复杂需求，需要嵌套大量子查询、关联多张表，代码冗长且性能低下。MySQL 8.0 正式引入窗口函数，仅用简短语法就能实现复杂分析逻辑，不改变原始数据行数，兼顾可读性与执行效率。
本篇聚焦三大核心排名类窗口函数，从概念、语法、分区排序、函数差异、全场景实战、避坑要点逐层讲解，案例贴合职场真实业务
一、本章知识点汇总
- 窗口函数的定义、作用与核心优势，对比传统聚合函数差异
- 窗口函数标准语法：
OVER()、PARTITION BY、ORDER BY详解 - 三大排名类窗口函数：
ROW_NUMBER()、RANK()、DENSE_RANK()核心区别 - 全局排名、分组排名两种主流实战场景
- 分组取 TopN 经典面试题型（窗口函数标准解法）
- 窗口函数执行顺序与使用限制
- 窗口函数与 GROUP BY 聚合函数的本质区别
- 职场高频踩坑点与性能优化建议
- 综合实战案例拓展
- 课后练习题 + 完整参考答案
二、各知识点详解
1. 什么是窗口函数？
窗口函数（Window Function），也叫开窗函数，是 MySQL 8.0 及以上版本支持的特殊函数。它会将查询结果集划分为若干个 “窗口”，在每个窗口内完成计算、排名、统计等操作，核心特点：不会合并数据行，原始数据全部保留。
核心优势
- 传统
GROUP BY会将多行数据聚合为一行，丢失明细；窗口函数保留所有明细行，同时附加统计 / 排名结果。 - 简化复杂逻辑：替代多层子查询、多表关联，代码更简洁。
- 性能更优：MySQL 对窗口函数做了底层优化，大数据量场景执行效率高于传统写法。
典型适用场景
- 学生成绩排名、员工薪资排名、商品销量排名
- 分组内排名、分组取前 N 名（TopN）
- 累计求和、移动平均值、行间数据对比（下篇讲解）
- 日报 / 周报、数据分析报表制作
2. 窗口函数标准语法
所有窗口函数都遵循函数名() + OVER()固定结构，OVER()子句用于定义窗口范围，包含三大可选子句。
sql
-- 完整通用语法 窗口函数名() OVER ( [PARTITION BY 字段1, 字段2 ...] -- 分区（分组），可选 [ORDER BY 排序字段 ASC|DESC] -- 窗口内排序，可选 [窗口帧范围] -- 高级用法，本篇暂不深入 ) AS 别名;关键字逐句解析
- OVER()：窗口函数的标志，括号内为空时，代表整个结果集为一个窗口。
- PARTITION BY：分区，等价于
GROUP BY，将数据划分为多个独立窗口，分区之间相互隔离，排名、计算互不干扰。省略则代表全局一个窗口。 - ORDER BY：对每个窗口内的数据进行排序，排名类窗口函数必须搭配 ORDER BY，否则排名无意义。
- 窗口帧范围：用于定义窗口内的计算行范围，排名场景默认即可，进阶统计场景使用。
3. 三大核心排名窗口函数（本篇重点，面试必背）
这三个函数专门用于排名，是入门和面试的核心，三者最大差异体现在并列名次的处理规则上。
表格
| 函数名称 | 核心功能 | 并列数据规则 | 排名序列示例 | 适用场景 |
|---|---|---|---|---|
| ROW_NUMBER() | 为每行分配唯一连续序号 | 分数相同也会区分序号，无并列 | 1,2,3,4,5 | 纯行号、分页、强制区分顺序 |
| RANK() | 标准排名 | 同分同名次，后续名次跳跃 | 1,2,2,4,5 | 体育赛事排名、通用榜单 |
| DENSE_RANK() | 密集排名 | 同分同名次，后续名次连续 | 1,2,2,3,4 | 成绩档位、等级划分、榜单密集排名 |
核心区分口诀
ROW_NUMBER：人人有编号，绝不并列；RANK：并列会跳号；DENSE_RANK：并列不跳号。
4. 窗口函数与 GROUP BY 聚合函数的核心区别
表格
| 对比项 | 窗口函数 | GROUP BY + 聚合函数 |
|---|---|---|
| 数据行数 | 保留原始所有行，不合并 | 分组后多行合并为一行，丢失明细 |
| 计算范围 | 分区内独立计算，明细 + 统计共存 | 整组聚合计算，仅展示聚合结果 |
| 排序能力 | 支持分区内单独排序 | 仅支持全局排序，组内排序繁琐 |
| 执行顺序 | 晚于 WHERE、GROUP BY，早于 LIMIT | 晚于 WHERE，早于 HAVING |
| 典型用法 | 排名、行间对比、累计计算 | 求和、计数、平均值等全局汇总 |
5. 基础使用限制（避坑前置知识）
- 窗口函数不能直接写在 WHERE 子句中作为过滤条件，因为执行顺序靠后。若需过滤排名结果，必须嵌套子查询 / 临时表。
PARTITION BY可以指定多个字段，实现多维度分区。- 排序字段建议建立索引，提升大表排名性能。
三、实战环境准备（直接复制运行）
我们创建学生成绩表，模拟班级、学生、分数场景，覆盖全局排名、分组排名、并列排名等所有场景，所有案例均基于此表执行。
sql
-- 创建学生成绩表 CREATE TABLE student_score ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', class_name VARCHAR(20) NOT NULL COMMENT '班级名称', student_name VARCHAR(20) NOT NULL COMMENT '学生姓名', score INT NOT NULL COMMENT '考试分数' ) COMMENT = '学生考试成绩表'; -- 插入测试数据（包含同分、不同班级，模拟并列场景） INSERT INTO student_score (class_name, student_name, score) VALUES ('一班', '张三', 95), ('一班', '李四', 95), ('一班', '王五', 88), ('一班', '赵六', 80), ('二班', '小明', 98), ('二班', '小红', 95), ('二班', '小刚', 95), ('二班', '小丽', 76); -- 基础查询（查看原始数据） SELECT * FROM student_score;原始数据说明：
- 一班：张三、李四同分 95 分；
- 二班：小明最高分 98，小红、小刚同分 95 分；
- 存在大量并列分数，完美测试三大排名函数差异。
四、应用案例及结果分析
案例 1：全局排名（无 PARTITION BY，全表一个窗口）
需求：对所有学生按分数从高到低全局排名，同时展示三种排名结果，直观对比函数差异。
sql
SELECT class_name AS 班级, student_name AS 姓名, score AS 分数, -- 唯一行号 ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, -- 标准排名（跳号） RANK() OVER (ORDER BY score DESC) AS rank_num, -- 密集排名（不跳号） DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_num FROM student_score;结果分析
- ROW_NUMBER：所有行序号 1~8 连续不重复，即使分数相同，序号也依次递增；
- RANK：95 分的多名学生并列第 2，下一名直接跳到第 5（名次跳跃）；
- DENSE_RANK：95 分学生并列第 2，下一名紧跟第 3（名次连续，无跳跃）；
- 无
PARTITION BY，整张表作为一个窗口，实现全表全局排名。
案例 2：分组排名（PARTITION BY，按班级分区）
需求：每个班级内部单独排名，班级之间互不干扰，按分数降序，展示三种排名。
sql
SELECT class_name AS 班级, student_name AS 姓名, score AS 分数, -- 班级内唯一行号 ROW_NUMBER() OVER (PARTITION BY class_name ORDER BY score DESC) AS class_row_num, -- 班级内标准排名 RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS class_rank, -- 班级内密集排名 DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS class_dense_rank FROM student_score;结果分析
PARTITION BY class_name将数据拆分为一班、二班两个独立窗口，排名在班级内重新从 1 开始；- 一班内部：张三、李四 95 分并列，三大函数延续各自规则；二班同理；
- 这是职场分组排名最常用写法，例如：各部门员工薪资排名、各品类商品销量排名。
案例 3：经典面试题 - 分组取 TopN（取每个班级前三名）
需求
查询每个班级分数前三名的学生，窗口函数标准解法（高频面试题）。
解题思路
窗口函数不能用于WHERE过滤，因此需要嵌套子查询：先通过窗口函数生成班级内排名，再在外层筛选排名≤3 的数据。
sql
-- 分组取Top3 完整SQL SELECT * FROM ( SELECT class_name, student_name, score, DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS rank_num FROM student_score ) AS t WHERE t.rank_num <= 3; -- 筛选前三名结果分析
- 内层子查询完成班级内密集排名；
- 外层
WHERE过滤出排名前三的学生； - 选用
DENSE_RANK原因：存在并列分数时，不会漏掉同分高分学生；若使用ROW_NUMBER会强制截断同分数据，根据业务场景灵活选择。
案例 4：仅使用 ROW_NUMBER 实现分页行号
需求：给所有学生数据添加连续行号，实现简易分页序号（替代传统变量行号写法）。
sql
SELECT ROW_NUMBER() OVER (ORDER BY id) AS 行号, class_name, student_name, score FROM student_score;结果分析
ROW_NUMBER天生生成唯一连续序号，是数据分页、流水号生成的最优选择。
五、注意事项（避坑指南）
- WHERE 不能直接使用窗口函数窗口函数执行顺序晚于
WHERE，直接在WHERE中写排名函数会语法报错。解决方案：嵌套子查询、CTE 公用表表达式（下篇讲解）。 - 排名函数必须搭配 ORDER BY若省略
OVER()中的ORDER BY，数据无序，排名结果无业务意义。 - 合理选择三大排名函数
- 分页、流水号、强制区分顺序 → 优先
ROW_NUMBER()； - 常规榜单、赛事排名（允许跳号）→ 优先
RANK()； - 成绩档位、等级、分组 TopN（保留同分）→ 优先
DENSE_RANK()。
- 分页、流水号、强制区分顺序 → 优先
- PARTITION BY 多字段分区规则支持
PARTITION BY 字段1,字段2，例如PARTITION BY 年级,班级，实现多级分区排名。 - 版本限制窗口函数仅MySQL 8.0 及以上版本支持，5.7 及以下版本无法使用，生产环境需提前确认数据库版本。
- 大表性能优化
PARTITION BY和ORDER BY涉及的字段建议建立复合索引，避免全表排序导致查询缓慢。 - 区分分区与分组
PARTITION BY是窗口分区（保留明细），GROUP BY是聚合分组（合并行），切勿混用。
六、核心总结
- 窗口函数核心定义：基于数据窗口计算，保留所有明细行，是 MySQL 8.0 标志性新特性，主打排名、统计、行间分析。
- 基础语法：
函数名() OVER(PARTITION BY 分区字段 ORDER BY 排序字段)。 - 三大排名函数核心差异
ROW_NUMBER()：唯一连续序号，无并列，适合分页、行号；RANK()：同分并列，名次跳跃，适合通用榜单；DENSE_RANK()：同分并列，名次连续，适合成绩、档位、分组 TopN。
- 两大核心场景
- 无
PARTITION BY：全局全表排名； - 有
PARTITION BY：分组（分区）排名，各组独立计算。
- 无
- 经典题型：分组取 TopN 必须嵌套子查询过滤排名，面试高频考点。
- 使用红线：窗口函数不支持直接在
WHERE中过滤，注意 MySQL 版本兼容。
一句话记忆：窗口函数存明细，OVER 括号定窗口；分区排序分两步，三大排名各有术；行号连续无并列，Rank 跳号 Dense 续；TopN 查询套子查，版本 8.0 别记错。
七、练习题（附答案思路）
基于student_score表完成以下练习，巩固排名函数用法。
题目 1
查询所有学生信息，使用RANK()函数，按分数升序做全局排名。
题目 2
使用DENSE_RANK()实现：查询每个班级分数后两名的学生。
题目 3
简述ROW_NUMBER()、RANK()、DENSE_RANK()三者在处理并列数据时的区别（面试简答题）。
题目 4
判断正误：可以直接在 WHERE 子句中使用窗口函数过滤排名？并说明原因。
参考答案思路
题目 1
sql
SELECT class_name, student_name, score, RANK() OVER (ORDER BY score ASC) AS global_rank FROM student_score;题目 2
sql
SELECT * FROM ( SELECT class_name, student_name, score, DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score DESC) AS rank_num FROM student_score ) t WHERE t.rank_num >= (SELECT COUNT(*) FROM student_score s WHERE s.class_name = t.class_name) - 1; -- 简易写法（直接筛选倒数2名，按分数升序排名） SELECT * FROM ( SELECT class_name, student_name, score, DENSE_RANK() OVER (PARTITION BY class_name ORDER BY score ASC) AS rank_num FROM student_score ) t WHERE t.rank_num <= 2;题目 3 参考答案
ROW_NUMBER()：无论分数是否相同，都会生成唯一且连续的序号，不存在并列名次；RANK()：分数相同则名次相同，后续名次会跳跃；DENSE_RANK()：分数相同则名次相同，后续名次连续不跳跃。
题目 4 参考答案
错误。窗口函数的执行顺序晚于 WHERE 子句，执行 WHERE 过滤时，窗口函数还未计算出结果，因此无法直接使用。解决方案是嵌套子查询或者使用 CTE 表达式。
