Mysql窗口函数

MySQL窗口函数分类详解：3大类搞定所有常用场景

在MySQL 8.0版本之前，我们想要实现排名、分组统计、取相邻行数据等需求，往往需要写复杂的子查询、自连接，不仅效率低，代码可读性也差。而MySQL 8.0引入的窗口函数，彻底解决了这个痛点——它能在不改变原有数据行数的前提下，对数据进行分组、排序、聚合，让复杂的统计需求变得简洁高效。

很多小伙伴刚接触窗口函数时，会被各种函数名称搞得眼花缭乱，比如RANK()、LAG()、SUM() OVER()等等。其实不用慌，所有MySQL窗口函数都可以归为3大类，只要掌握了每类的核心作用和使用场景，就能灵活运用，轻松应对工作中的各种统计需求。今天就来详细拆解这3大类窗口函数，结合实例帮大家吃透！

一、核心前提：什么是窗口函数？

在正式分类之前，先简单回顾下窗口函数的核心概念。窗口函数的本质是“对一组数据（窗口）进行计算”，它和普通聚合函数（如SUM、COUNT）的最大区别是：聚合函数会将多行数据合并为一行，而窗口函数不会合并行，每行都会保留原有数据，同时新增计算结果列。

所有窗口函数都遵循统一的语法结构，这也是我们学习的关键：

函数名() OVER (
    PARTITION BY 分组列  -- 可选：按指定列分组，每组单独计算
    ORDER BY 排序列 [ASC/DESC]  -- 可选：组内按指定列排序
)

其中，OVER()是窗口函数的标志，里面的PARTITION BY和ORDER BY是可选参数，根据需求灵活搭配。接下来，我们就按3大类逐一讲解。

二、第一类：排名类窗口函数（最常用，搞定各类排名需求）

排名类窗口函数的核心作用是“对数据进行排名”，也是工作中最常使用的一类窗口函数。它主要有3个常用函数，区别仅在于“并列名次是否跳号”，我们用一张表就能清晰区分。

1. 常用函数及区别

函数名称	核心作用	并列名次处理方式	示例（假设3人分数：90、90、80）
RANK()	对数据进行排名	并列会跳号	1、1、3
DENSE_RANK()	密集排名	并列不跳号	1、1、2
ROW_NUMBER()	生成连续行号	即使相同也强制连续编号	1、2、3

2. 实战示例（一看就懂）

假设我们有一张学生成绩表score，数据如下：

name	score	class
小明	90	1班
小红	90	1班
小刚	80	1班
小丽	85	2班
需求：按班级分组，对学生成绩降序排名，查看三种排名函数的效果。

SELECT
    name,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rk,
    DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC) AS drk,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rn
FROM score;

查询结果：

name	class	score	rk	drk	rn
小明	1班	90	1	1	1
小红	1班	90	1	1	2
小刚	1班	80	3	2	3
小丽	2班	85	1	1	1

3. 适用场景

这类函数主要用于“排名”相关的需求，比如：

• 班级/年级成绩排名、商品销量排名

• 取每个分组的Top N数据（如每个班级前2名学生）

• 筛选出排名靠前的记录（如销量前10的商品）

三、第二类：分区取值类窗口函数（取相邻行/最值行数据）

分区取值类窗口函数的核心作用是“获取当前行附近的行数据”，比如上一行、下一行、分组内的第一行、最后一行。这类函数不需要排名，而是专注于“取值”，解决了“如何快速获取相邻数据”的痛点。

1. 常用函数及作用

函数名称	核心作用	补充说明
LAG(列名)	获取当前行的上一行指定列的数据	可指定偏移量（如LAG(score,2)取上两行）
LEAD(列名)	获取当前行的下一行指定列的数据	同样可指定偏移量，默认偏移量为1
FIRST_VALUE(列名)	获取分组内的第一行指定列的数据	需配合ORDER BY指定排序规则
LAST_VALUE(列名)	获取分组内的最后一行指定列的数据	注意：默认窗口范围是“从开始到当前行”，需调整窗口范围才能取到真正的最后一行

2. 实战示例

还是用上面的score表，需求：查看每个学生的成绩、上一个学生的成绩、下一个学生的成绩，以及每个班级的最高分和最低分所在行的成绩。

SELECT
    name,
    class,
    score,
    -- 取上一行成绩
    LAG(score) OVER (PARTITION BY class ORDER BY score DESC) AS 上一人成绩,
    -- 取下一行成绩
    LEAD(score) OVER (PARTITION BY class ORDER BY score DESC) AS 下一人成绩,
    -- 取分组内第一行（最高分）
    FIRST_VALUE(score) OVER (PARTITION BY class ORDER BY score DESC) AS 班级最高分,
    -- 取分组内最后一行（最低分），调整窗口范围为整个分组
    LAST_VALUE(score) OVER (PARTITION BY class ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 班级最低分
FROM score;

3. 适用场景

这类函数主要用于“获取相邻数据”或“组内最值行”的需求，比如：

• 环比计算（如本月销售额与上月销售额对比）

• 查看相邻记录（如用户连续登录记录、订单连续下单记录）

• 获取分组内的最值所在行（如每个部门工资最高的员工信息）

四、第三类：聚合窗口函数（带OVER()的聚合函数）

聚合窗口函数是最容易理解的一类——它就是我们平时常用的聚合函数（SUM、AVG、COUNT、MAX、MIN），加上OVER()关键字，就变成了窗口函数。它的核心作用是“在不合并行的前提下，进行分组聚合”。

举个简单的例子：普通的SUM(score)会计算所有学生的总分，合并为一行；而SUM(score) OVER(PARTITION BY class)会按班级分组，每行都显示该班级的总分，不合并行。

1. 常用函数及作用

函数名称	核心作用	示例
SUM(列名) OVER()	分组内累计求和	按班级分组，显示每个学生的成绩及班级累计总分
AVG(列名) OVER()	分组内计算平均值	按班级分组，显示每个学生的成绩及班级平均成绩
COUNT(列名) OVER()	分组内计数	按班级分组，显示每个学生及班级总人数
MAX(列名) OVER()	分组内取最大值	按班级分组，显示每个学生的成绩及班级最高分
MIN(列名) OVER()	分组内取最小值	按班级分组，显示每个学生的成绩及班级最低分

2. 实战示例

需求：按班级分组，计算每个学生的成绩、班级平均成绩、班级总分、班级人数、班级最高分和最低分。

SELECT
    name,
    class,
    score,
    AVG(score) OVER (PARTITION BY class) AS 班级平均分,
    SUM(score) OVER (PARTITION BY class) AS 班级总分,
    COUNT(name) OVER (PARTITION BY class) AS 班级人数,
    MAX(score) OVER (PARTITION BY class) AS 班级最高分,
    MIN(score) OVER (PARTITION BY class) AS 班级最低分
FROM score;

查询结果中，每个学生的行都会显示对应的班级聚合信息，无需合并行，非常直观。

3. 适用场景

这类函数主要用于“分组统计但不合并行”的需求，比如：

• 显示每个员工的工资及所在部门的平均工资、总工资

• 计算累计金额（如每月销售额及全年累计销售额）

• 移动平均计算（如近3个月的平均销售额）

五、总结：3大类窗口函数快速记忆

MySQL窗口函数看似繁多，但只要记住以下3大类，就能轻松应对90%以上的工作场景：

1. 排名类：RANK()、DENSE_RANK()、ROW_NUMBER() —— 搞定各种排名需求，重点区分“并列是否跳号”。

2. 分区取值类：LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE() —— 快速获取相邻行、组内最值行数据。

3. 聚合窗口类：SUM/AVG/COUNT/MAX/MIN + OVER() —— 分组聚合不合并行，直观显示统计结果。

最后提醒一句：窗口函数仅支持MySQL 8.0及以上版本，如果你的MySQL版本较低，需要先升级才能使用。另外，灵活运用PARTITION BY和ORDER BY，能让窗口函数的功能更加强大，大家可以多动手练习，慢慢就能熟练掌握啦！