lag()和lead()需配合partition by与order by将相邻周期成绩拉至同行再计算差值；常见错误是漏分组或排序字段错误；推荐用row_number()生成连续序号规避缺考导致的错位；大表需优化索引与预过滤。
怎么用 LAG() 和 LEAD() 拿到上一次/下一次成绩
窗口函数本身不直接“算趋势”，得靠 LAG() 或 LEAD() 把相邻周期的成绩拉到同一行，再做减法。比如按学生、学期排序后，LAG(score) OVER (PARTITION BY student_id ORDER BY semester) 就能拿到该生上一学期的成绩。
常见错误是漏写 PARTITION BY：不按学生分组，就会把张三上学期和李四这学期混在一起比；或者 ORDER BY 用错字段（比如用 id 而非 semester），导致时间顺序乱。
实操建议：
- 确保
semester字段可排序（推荐用日期或数字，避免字符串如 "2023-1"） - 用
COALESCE(LAG(score), score)处理首学期无前值的情况，避免NULL污染后续计算 -
LEAD()同理，适合预测下学期变化，但依赖未来数据是否已入库
用 CASE WHEN + 窗口结果判断进退步类型
拿到差值后，不能只看正负数——+5 分可能是从 60 到 65（及格线挣扎），也可能是从 95 到 100（天花板突破）。业务上常需分级：CASE WHEN diff > 10 THEN '大幅进步' WHEN diff BETWEEN 3 AND 10 THEN '稳步提升' ...
注意点：
- 差值为 0 时别漏掉
WHEN diff = 0 THEN '持平'，否则会归入ELSE（可能误标为“异常”） - 如果成绩含小数，用
ROUND(diff, 1)避免浮点误差导致边界判断失准 - 别在
WHERE里过滤窗口函数结果（如WHERE diff > 0），必须套一层子查询或 CTE
为什么 ROW_NUMBER() 比 RANK() 更适合追踪连续学期
当某学生缺考某学期，数据里没那条记录，用 RANK() 会产生跳跃排名（比如学期序列变成 1, 2, 4），导致 LAG() 错位。而 ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY semester) 强制生成连续序号，再用这个序号做偏移，更可靠。
示例：
SELECT student_id, semester, score,
ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY semester) AS rn
FROM scores;
之后就能安全地写 LAG(score) OVER (PARTITION BY student_id ORDER BY rn)，不受实际学期编号空缺影响。
性能陷阱：大表上 PARTITION BY 的开销在哪
千万级学生成绩表，PARTITION BY student_id 会让每个学生的数据单独排序——如果 student_id 基数太大（比如 50 万不同学生），窗口函数会触发大量小排序，比全表按 semester 排一次还慢。
优化思路：
- 优先按
semester排序，再用student_id二次排序，让物理存储局部有序 - 加复合索引：
CREATE INDEX idx_sem_stu ON scores(semester, student_id); - 如果只要最近 3 学期趋势，先用子查询过滤
semester >= '2023-01'再开窗，别对全量跑
真正卡住的往往不是函数本身，而是没意识到 PARTITION BY 在底层等于“为每组重做一次排序”。
