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

差分变化量是当前行与上一行（或下一行）某字段的差值，需用lag()/lead()配合partition by和order by实现；必须排序以定义“前后”，重复值需二级排序；首行lag返回null，应显式处理；默认值参数和索引对结果准确性与性能至关重要。

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

差分变化量，就是当前行和上一行（或下一行）某个字段的差值。在 SQL 里，它不是靠 GROUP BY + 聚合实现的，而是用窗口函数 LAG() 或 LEAD() 拿到相邻行值后手动相减。关键点在于：必须先按业务逻辑排序（比如时间、序号），否则“上一行”没意义。

最常见需求是：每个用户每天的访问量，求“比前一天增长多少”。这时要先按 user_id 分组，再按 date 排序，然后取前一行的值做减法。

实操建议：

LAG() 第二个参数填 1 表示跳 1 行（默认就是 1，可省略）；第三个参数是当没有上一行时的默认值，建议显式写成 0 或 NULL，避免隐式类型转换出错
OVER 子句里必须同时有 PARTITION BY（分组）和 ORDER BY（排序），漏掉 ORDER BY 会导致结果不可预测
如果原始数据里存在重复时间戳，ORDER BY 要加二级排序（如 ORDER BY date, id），否则窗口顺序不唯一

示例：

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

首行计算 LAG() 必然返回 NULL（因为没“上一行”），导致整个 diff 列首行为 NULL。这不是 bug，是预期行为。但业务常要求首行显示为 0 或原值。

求职侠

求职侠是一款由智联招聘推出的AI求职托管工具。

下载

解决方法：

用 COALESCE() 包一层：COALESCE(visits - LAG(visits) OVER (...), 0)
或者把差分逻辑改写成：CASE WHEN LAG(visits) OVER (...) IS NULL THEN 0 ELSE visits - LAG(visits) OVER (...) END
注意：别直接在 LAG() 的默认值里填 visits——那是当前行值，不是上一行，会破坏差分语义