RANK() 返回并列排名且跳号,LAG() 用于获取同组内上一行值;计算排名变化需严格按 class_id、subject 分区,并以 exam_date 排序确保“上一次”定义准确,且 RANK() 与 DENSE_RANK() 不可混用。

窗口函数中用 RANK() 和 LAG() 计算排名变化
直接用 RANK() 算出当前排名,再用 LAG() 拿上一次考试的同班级同科目的排名,相减就能得出变化值。关键不是“怎么写语法”,而是“怎么定义‘上一次’”——必须明确按时间(如 exam_date)排序,并确保数据中每个 (class_id, subject, exam_date) 组合唯一。
常见错误是漏掉 PARTITION BY 细粒度分组,导致 LAG() 跨班级或跨科目取值。正确做法是三层嵌套:最内层按班级+科目+时间排序生成当前排名;中间层用 LAG() 在相同班级和科目内取前一行的排名;最外层计算差值。
-
RANK() OVER (PARTITION BY class_id, subject ORDER BY score DESC)—— 同班同科按分数降序排,处理并列 -
LAG(rank_val) OVER (PARTITION BY class_id, subject ORDER BY exam_date)—— 仅在相同班级+科目内,按考试时间向前看一行 - 若某学生缺考某次,对应行的
score为NULL,RANK()会跳过该行,LAG()自动对齐非空记录,无需额外补全
处理并列排名时 RANK() 与 DENSE_RANK() 的差异影响变化值
当两个学生同分,RANK() 给并列第1后直接跳到第3,而 DENSE_RANK() 是第1、第1、第2。这直接影响变化值的解读:比如上次是 RANK = 1,这次两人同分又得第1,变化是 0;但如果用 DENSE_RANK(),结果一样;但若上次是第2,这次变成并列第1(RANK = 1),变化是 −1;而用 DENSE_RANK() 也是 −1。真正出问题的是跨周期比较:如果上期用 RANK(),本期改用 DENSE_RANK(),变化值就不可比。
- 同一分析中必须固定使用
RANK()或DENSE_RANK(),不能混用 - 若业务要求“第1名之后就是第2名”,选
DENSE_RANK();若强调“击败了多少人”,选RANK() -
ROW_NUMBER()不适合排名变化场景,因为它不处理并列,会让同分学生变化值人为放大
如何安全地关联“上次考试”的完整记录(不只是排名)
只靠 LAG(rank_val) 只能拿到上期排名,拿不到上期分数、老师评语等字段。这时应改用自连接 + 窗口函数辅助定位。核心思路是:先用 ROW_NUMBER() OVER (PARTITION BY class_id, subject ORDER BY exam_date DESC) 给每次考试标序号(1=最近,2=上一次),再用 rn = 1 和 rn = 2 分别抽最新和上期记录,最后 JOIN 关联。
- 避免用
exam_date - INTERVAL '7 days'这类硬写时间偏移,考试周期未必固定 - 如果某班某科缺上期数据,
LEFT JOIN后对应字段为NULL,变化值可设为NULL或按业务规则填'new'/'reentry' - 性能上,给
(class_id, subject, exam_date)建复合索引能显著加速排序和连接
MySQL 8.0+ 与 PostgreSQL 中 LAG() 的 NULL 处理差异
两者语法一致,但默认行为不同:PostgreSQL 的 LAG(val, 1, 0) 允许设默认值(如第1次考试无“上次”,填 0);MySQL 8.0+ 同样支持第三个参数,但旧版(如 5.7)根本不支持窗口函数,强行运行会报错 FUNCTION xxx does not exist。升级前务必确认版本。
- 检查版本:
SELECT VERSION();—— MySQL 必须 ≥ 8.0.2,PostgreSQL ≥ 8.4 - 兼容写法:用
COALESCE(LAG(rank_val) OVER (...), -1)替代默认值参数,更通用 - 若用 SQLAlchemy 或 ORM,注意其对窗口函数的支持程度,某些低版本驱动会把
OVER子句截断
实际中最容易被忽略的,是考试时间字段的精度和时区。比如 exam_date 只存日期但同一日有多场考试,ORDER BY exam_date 就无法稳定排序,必须加上 exam_time 或时间戳字段。











