不能只靠 row_number()，必须配合 lag() 按时间序取上期排名再相减；两函数的 order by 必须不同：row_number() 按分数排序定名次，lag() 按时间排序定“上一条”，否则逻辑错误。
用 LAG() 和 ROW_NUMBER() 算分组内排名变化
直接说结论：不能只靠 ROW_NUMBER()，必须配合窗口函数 LAG() 或 LEAD() 拿到上一行的排名值，再做差。否则“波动”无从谈起。
常见错误是先用子查询算出每组每条记录的当前排名，再试图用外部查询连表比对——这不仅慢，还容易因排序不稳定导致前后行错位。
-
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
波动本身不难算，难的是明确“和谁比”“按什么顺序比”“并列怎么算”——这三个问题没想清，结果看着像对，其实全是噪声。
