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

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

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

差分变化量的核心定义很简单：当前行的值，往上走一行（或往下走一行），减一减，结果就是这个差分值。但关键不在于数学，而在于 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。这种细节，真正踩过坑的人才会留意。

性能和兼容性：容易忽略的那些事

窗口函数本身一般不触发临时表或文件排序，但 ORDER BY 这部分是实打实的排序操作。如果 PARTITION BY a ORDER BY b 没有联合索引 (a, b)，大表上的性能会明显下降。排序的成本在那里，单靠 SQL 语法优化也省不了。

兼容性方面：

• MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持标准语法，写法几乎一致
• SQLite 3.25+ 支持，但不支持 ROWS BETWEEN 这类高级帧子句，不过对基本的差分计算没影响
• 旧版 MySQL（5.7 及以前）完全不支持窗口函数，只能用自连接或用户变量模拟，语法复杂且易出错，很难保证正确性

还有一个容易误踩的坑：差分结果不能直接用于后续的窗口聚合，比如再在外面套一层 SUM() OVER。窗口函数不允许嵌套，必须先把差分列算出来，通过 CTE 或子查询“落地”，然后再做下一轮窗口计算。顺序错了，结果就是语法错误或逻辑混乱。