如何通过SQL计算分组内排名的波动，用前后行差值来分析？

本文共计884个文字，预计阅读时间需要4分钟。

直接说明结论：

常见错误是先用子查询算出每组每条记录的当前排名，再试图用外部查询连表比对——这不仅慢，还容易因排序不稳定导致前后行错位。

• ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY score DESC) 给每组按得分排当前名次（1 是最高）
• LAG(rn, 1) OVER (PARTITION BY group_col ORDER BY time_col) 按时间顺序取上一次的排名值，注意：ORDER BY 必须是时间维度，不是分数维度
• 两列相减即得“比上次上升/下降几名”，结果为正表示退步（数字变大），负表示进步

为什么 ORDER BY 在 LAG 和 ROW_NUMBER 中要不同？

这是最容易翻车的地方。两个窗口函数的 ORDER BY 语义完全不同：

• ROW_NUMBER() 的 ORDER BY score DESC 决定“这一时刻谁排第几”
• LAG() 的 ORDER BY time_col 决定“谁是上一条记录”，它不关心分数高低，只认时间先后
• 如果都用 score 排序，LAG() 取到的就不是“上次”，而是“分数更接近的那条”，逻辑全崩

示例片段：

SELECT user_id, group_id, score, time_col, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY score DESC) AS rn_now, LAG(ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY score DESC)) OVER (PARTITION BY group_id ORDER BY time_col) AS rn_prev, rn_now - COALESCE(rn_prev, rn_now) AS delta FROM scores;

处理并列排名时的波动计算差异

用 ROW_NUMBER() 会把并列者强行拆成 1、2、3；而 RANK() 或 DENSE_RANK() 会给出 1、1、3 或 1、1、2。选哪个取决于你对“波动”的定义：

• 若关注绝对位置变化（比如“从第 1 名掉到第 3 名”），用 ROW_NUMBER() 最直观
• 若关注相对层级变化（比如两人同分并列第 1，第三名就是第 2 层级），用 DENSE_RANK() 更合理
• RANK() 有跳名现象（1,1,3），会导致波动值突然变大，解释成本高，一般不推荐

注意：LAG() 无法跨组取值，所以 PARTITION BY 必须一致，否则 rn_prev 可能是 NULL 或来自其他组，差值完全失真。

性能和 NULL 值的实际处理要点

真实数据里，首条记录没有“上次”，LAG() 返回 NULL；另外，如果某组中间缺数据（如用户某周没提交），差值会跳变。这些不能简单用 0 填充：

• 用 COALESCE(rn_prev, rn_now) 把首条的波动设为 0（即“无变化”），比设成 NULL 更易统计
• 若需识别断档，可加一列 LAG(time_col) OVER (...) AS prev_time，检查时间间隔是否超阈值
• 在大表上，确保 (group_id, time_col) 有联合索引，否则 LAG() 窗口排序代价极高
• PostgreSQL 支持 LAG(..., 1, default_value) 直接指定默认值；MySQL 8.0+ 同样支持；老版本需嵌套 COALESCE

波动本身不难算，难的是明确“和谁比”“按什么顺序比”“并列怎么算”——这三个问题没想清，结果看着像对，其实全是噪声。