SQL怎样实现跨行数据差值计算_利用LAG函数对比前行-SQL-PHP中文网

LAG函数能算跨行差值是因为它作为窗口函数可向前取指定偏移量的行值，用当前行减LAG(value,1)即得与前一行差值；必须配合ORDER BY保证排序确定性，否则结果未定义。

sql怎样实现跨行数据差值计算_利用lag函数对比前行

LAG 是窗口函数，它能从当前行“往回看”指定偏移量的行，取对应列的值。只要把当前行的数值减去 LAG(value, 1) 返回的前一行值，就得到与前一行的差值。关键在于：它不依赖自连接或子查询，也不要求主键连续，只要排序逻辑明确（ORDER BY 正确），结果就可靠。

常见错误是漏写 ORDER BY —— 没有显式排序时，LAG 行为未定义，可能返回任意“上一行”，差值完全不可信。

LAG 最小可用形式是 LAG(column_name, offset, default_value)，其中：

offset：整数，表示往前跳几行，默认为 1；设为 2 就是跟前两行比
default_value：当前行是分区首行、无前行可取时返回的值，建议显式写（如 0 或 NULL），避免隐式 NULL 导致整个差值变 NULL
必须配合 OVER (ORDER BY ...)，且 ORDER BY 字段应具备业务意义（如时间戳、序列号）

示例（按日期计算销售额日环比）：

SELECT 
  sale_date,
  amount,
  amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS daily_diff
FROM sales;

如果数据要按用户、设备、地区等分组，各自从头算差值，就必须加 PARTITION BY。否则所有数据混在一起排，A 用户最后一行会和 B 用户第一行相减，结果错乱。

典型场景：每个用户的每日登录时长变化

错误写法：OVER (ORDER BY login_time) → 全局排序，跨用户污染
正确写法：OVER (PARTITION BY user_id ORDER BY login_time) → 每个 user_id 内单独排序、单独拉前值
注意：PARTITION BY 和 ORDER BY 的字段类型要支持比较，比如 login_time 不能是字符串格式的 “2024-01-01 10:30” 而没转成 datetime，否则排序失效

首行的 LAG 必然没前值，若没设 default_value，返回 NULL，导致 amount - NULL = NULL。这不是 bug，是 SQL 三值逻辑决定的。所以实际使用中：

用 COALESCE(LAG(...), 0) 包一层，强制补零（适合累加类指标）
或用 CASE WHEN ROW_NUMBER() OVER (...) = 1 THEN 0 ELSE amount - LAG(...) END，首行显式标 0
警惕浮点数精度：差值列若用于后续聚合（如 SUM(daily_diff)），确认原始 amount 是 DECIMAL 而非 FLOAT，否则微小误差会累积

真正容易被忽略的是：当 ORDER BY 字段存在重复值（比如同秒多条记录），数据库可能任意排序，导致 LAG 取到的“前一行”不稳定。此时应在 ORDER BY 中追加唯一字段（如 id）保序。