在业务数据分析过程中，基于分组的动态排名变动统计是高频需求，比如统计不同班级学生每次考试的排名变化、不同区域销售人员的业绩排名波动等。这类需求需要同时处理分组、排序、跨周期排名对比多个逻辑，传统的SQL写法往往较为繁琐，而窗口函数可以大幅简化实现过程。
基础数据准备
首先我们创建一张模拟的成绩表，包含班级、学生姓名、考试月份、考试成绩四个字段，后续所有示例都基于这张表展开。
-- 创建成绩表
CREATE TABLE student_score (
class VARCHAR(20) COMMENT '班级',
student_name VARCHAR(20) COMMENT '学生姓名',
exam_month VARCHAR(10) COMMENT '考试月份',
score INT COMMENT '考试成绩'
);
-- 插入测试数据
INSERT INTO student_score (class, student_name, exam_month, score) VALUES
('一班', '张三', '2024-01', 85),
('一班', '张三', '2024-02', 90),
('一班', '李四', '2024-01', 92),
('一班', '李四', '2024-02', 88),
('一班', '王五', '2024-01', 78),
('一班', '王五', '2024-02', 85),
('二班', '赵六', '2024-01', 88),
('二班', '赵六', '2024-02', 91),
('二班', '钱七', '2024-01', 95),
('二班', '钱七', '2024-02', 89),
('二班', '孙八', '2024-01', 82),
('二班', '孙八', '2024-02', 87);
窗口函数实现分组内排名
要实现动态排名变动统计，首先需要先计算出每个分组内每个周期的排名，SQL的窗口函数RANK()、DENSE_RANK()、ROW_NUMBER()都可以实现分组内排序排名，三者的逻辑略有区别。
三种排名窗口函数对比
| 函数名称 | 排名逻辑 | 相同分数处理 | 后续排名 |
|---|---|---|---|
| ROW_NUMBER() | 按排序顺序依次分配连续整数 | 相同分数随机分配不同序号 | 连续不跳号 |
| DENSE_RANK() | 按排序顺序分配排名，相同值排名相同 | 相同分数排名一致 | 连续跳号 |
| RANK() | 按排序顺序分配排名，相同值排名相同 | 相同分数排名一致 | 不连续跳号 |
我们使用RANK()函数计算每个班级内每个月的成绩排名，示例代码如下：
SELECT
class,
student_name,
exam_month,
score,
-- 按班级分组，按成绩降序排名
RANK() OVER (PARTITION BY class ORDER BY score DESC) AS month_rank
FROM student_score
ORDER BY class, exam_month, month_rank;
执行上述代码后，我们可以得到每个班级内每个学生的月度排名，接下来就可以基于这个排名结果计算排名变动情况。
动态排名变动统计实现
排名变动统计需要对比同一个学生在不同周期的排名差异，我们可以通过自连接或者窗口函数的LAG()函数实现，后者代码更简洁。
使用LAG()函数获取上一周期排名
LAG()窗口函数可以获取当前行之前指定偏移量的行的字段值，我们可以用它获取同一个学生上个月的排名，进而计算排名变动值。
WITH month_rank_data AS (
-- 第一步：先计算每个月的班级内排名
SELECT
class,
student_name,
exam_month,
score,
RANK() OVER (PARTITION BY class ORDER BY score DESC) AS current_rank
FROM student_score
)
SELECT
class,
student_name,
exam_month,
score,
current_rank,
-- 获取该学生上个月的排名，没有上月数据则为NULL
LAG(current_rank, 1) OVER (PARTITION BY class, student_name ORDER BY exam_month) AS last_month_rank,
-- 计算排名变动：上月排名减当前排名，正数表示排名上升，负数表示下降，0表示不变
COALESCE(
LAG(current_rank, 1) OVER (PARTITION BY class, student_name ORDER BY exam_month) - current_rank,
0
) AS rank_change
FROM month_rank_data
ORDER BY class, student_name, exam_month;
上述代码首先通过CTE计算出每个月的班级内排名，然后使用LAG()函数按学生和班级分组，按月份排序获取上个月的排名，最后计算排名变动值。如果学生上个月没有考试数据，排名变动默认记为0。
传统实现方式与窗口函数对比
如果不使用窗口函数，我们通常需要用自连接的方式实现相同的需求，以下是传统自连接的实现代码：
-- 传统自连接实现排名变动统计
SELECT
a.class,
a.student_name,
a.exam_month,
a.score,
a.current_rank,
COALESCE(b.current_rank, a.current_rank) AS last_month_rank,
COALESCE(b.current_rank - a.current_rank, 0) AS rank_change
FROM (
-- 子查询计算当前月排名
SELECT
s1.class,
s1.student_name,
s1.exam_month,
s1.score,
(SELECT COUNT(*) + 1 FROM student_score s2
WHERE s2.class = s1.class AND s2.exam_month = s1.exam_month AND s2.score > s1.score) AS current_rank
FROM student_score s1
) a
LEFT JOIN (
-- 子查询计算上月排名
SELECT
s1.class,
s1.student_name,
s1.exam_month,
(SELECT COUNT(*) + 1 FROM student_score s2
WHERE s2.class = s1.class AND s2.exam_month = s1.exam_month AND s2.score > s1.score) AS current_rank
FROM student_score s1
) b ON a.class = b.class AND a.student_name = b.student_name
AND DATE_ADD(STR_TO_DATE(CONCAT(b.exam_month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH) = STR_TO_DATE(CONCAT(a.exam_month, '-01'), '%Y-%m-%d')
ORDER BY a.class, a.student_name, a.exam_month;
对比两种方式可以看出，窗口函数的实现代码更简洁，逻辑更清晰，不需要多层嵌套子查询和自连接，执行效率也更高，尤其是在数据量较大的场景下，窗口函数的性能优势会更加明显。
注意事项
- 使用窗口函数时，
PARTITION BY子句用于指定分组字段，ORDER BY子句用于指定分组内的排序规则，二者需要根据业务需求正确设置。 - 如果排名变动统计需要跨多个周期，比如对比三个月的排名变化，可以调整
LAG()函数的偏移量参数，比如LAG(current_rank, 2)可以获取两个月前的排名。 - 不同数据库对窗口函数的支持程度略有差异，MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库都支持标准的窗口函数语法，低版本数据库可能需要使用传统方式实现。
窗口函数是SQL处理分组排序类需求的高效工具，掌握它的使用方法可以大幅降低复杂统计需求的实现难度，提升数据分析效率。