SQL窗口函数计算分组内差分变化量方法

时间：2026-06-25 07:08

在SQL中，使用窗口函数LAG()可计算分组内当前行与上一行的差值，需配合PARTITIONBY分组和ORDERBY排序，注意排序唯一性以避免歧义。首行结果默认NULL，可用COALESCE处理。大表需联合索引优化性能，且窗口函数不支持嵌套。

先思考一个问题：在 SQL 里，怎么算当前行与上一行的差值？比如每个用户每天的访问量，想看看“比前一天增长了多少”。这个需求很常见，但实现思路得绕个弯——不是 GROUP BY 加聚合，而是用窗口函数 LAG() 或 LEAD() 把相邻行的值“搬”过来，再做减法。核心前提是：必须先按业务逻辑排序。

如何在SQL中利用窗口函数计算分组内的差分变化量？

差分变化量：不只是简单的减法

差分变化量的核心定义很简单：当前行的值，往上走一行（或往下走一行），减一减，结果就是这个差分值。但关键不在于数学，而在于 SQL 如何定位那“上一行”。LAG() 和 LEAD() 就是干这个的：LAG(字段, 偏移量, 默认值) 可以拿到当前行之前第 N 行的值。OVER 子句里的 PARTITION BY 负责分组，ORDER BY 则定义了“前后”的次序。

这里面有个容易被忽视的细节：ORDER BY 必须保证顺序唯一。如果数据里存在重复的时间戳，窗口函数就不知道谁先谁后，结果可能随机分配。解决办法是加一个二级排序，比如 ORDER BY date, id，用唯一标识符打破平局。否则，差分结果可能对不上你想表达的业务逻辑。

怎么写？LAG() 实战

最常见的场景是每个用户每天访问量的逐日增长。分组是 PARTITION BY user_id，排序是 ORDER BY date。然后取前一行的 visits 值，用当前行减去它：

SELECT  user_id,  date,  visits,  visits - LAG(visits, 1, 0) OVER (PARTITION BY user_id ORDER BY date) AS diffFROM user_daily_stats;

几个小技巧：

LAG() 的第二个参数 1 表示偏移一行（默认就是1，可以省）；第三个参数是默认值，建议显式写成 0 或 NULL，避免隐式类型转换搞出意外
OVER 子句必须同时带上 PARTITION BY 和 ORDER BY。漏掉 ORDER BY，窗口顺序会混乱，结果不可预测
重复时间戳一定要加二级排序，否则一个分组内可能有多个“上一行”候选

遇上 NULL，怎么办？

首行计算 LAG() 必然返回 NULL——没有上一行，自然没值可用。这不是 bug，是预期行为。但业务上通常希望首行显示为 0 或原值，而不是一个突兀的 NULL。

处理方式有两种：

用 COALESCE() 包一层：COALESCE(visits - LAG(visits) OVER (...), 0)
或者写一个 CASE WHEN：CASE WHEN LAG(visits) OVER (...) IS NULL THEN 0 ELSE visits - LAG(visits) OVER (...) END

这里有个坑：不要在 LAG() 的默认值参数里填 visits（当前行值）。因为默认值只在没有上一行时使用，但那个默认值会被当作“上一行的值”，结果首行的差分会变成 visits - visits = 0，而后续行会正常用实际上一行计算。这会导致语义错误——首行显示 0 没问题，但后续行错误地用了当前行减当前行，结果全是 0。这种细节，真正踩过坑的人才会留意。