lag取前一行值，lead取后一行值；二者均须搭配order by定义行序，缺则报错；默认偏移为1，越界返回null（可设第三参数为默认值）；排序字段需具确定性（如加id防重复），分组计算须用partition by隔离。
LEAD 和 LAG 函数的基本行为差异
LAG 取前一行的值，LEAD 取后一行的值，两者都依赖 ORDER BY 定义的行序。没有显式 ORDER BY 的窗口定义是无效的，MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都强制要求。默认偏移量为 1，但可指定为 2、3 等整数；若越界（如第一行调用 LAG），返回 NULL，除非提供第三个参数作为默认值。
-
LAG(value, 1, 0)：取上一行value，越界时填0 -
LEAD(value, 2)：跳过下一行，取下下一行的value，越界返回NULL - 排序字段必须稳定 —— 若
ORDER BY time中存在重复时间，结果可能非确定；建议追加主键如ORDER BY time, id
计算相邻行差值的典型写法
连续数据差值通常指“当前行减上一行”，即用 LAG 拿前值再相减。例如温度表 sensor_readings(time, temp) 中计算每小时温差：
SELECT time, temp, temp - LAG(temp) OVER (ORDER BY time) AS delta_temp FROM sensor_readings;
注意：LAG(temp) 在首行返回 NULL，导致首行 delta_temp 也为 NULL。这不是错误，而是语义正确 —— 没有“前一个温度”可比。若业务要求首行为 0，改用 temp - LAG(temp, 1, temp) OVER (...)，但需确认是否掩盖了真实缺失。
- 别用
LEAD做“当前减前一个” —— 那得写成LEAD(temp) OVER (...) - temp，实际算的是“下一个减当前”，方向反了 - 差值列名别叫
diff这类模糊名，明确用delta_*或change_from_prev - WHERE 条件不能直接过滤窗口函数结果（如
WHERE delta_temp > 5），需套一层子查询或 CTE
处理分组内连续差值（如按设备分组）
多设备共存时，差值必须限制在各设备内部计算，否则会拿设备 B 的末尾值减设备 A 的开头值。关键是在 OVER 子句中加入 PARTITION BY：
SELECT device_id, time, value, value - LAG(value) OVER (PARTITION BY device_id ORDER BY time) AS delta FROM metrics;
这里 PARTITION BY device_id 保证每个设备独立排序、独立取前值。漏掉 PARTITION BY 是最常见错误，尤其当数据按设备混合插入时，差值会跨设备错乱，且难以肉眼察觉。
- 分区字段类型要一致 —— 若
device_id是字符串但含空格或大小写混用，可能意外分成多个分区 - 复合分区如
PARTITION BY region, device_type要确保业务逻辑确实需要该粒度 - 窗口函数不改变原始行数，所以分组差值结果行数 = 原表行数，首行每组都是
NULL
性能与 NULL 处理的实操细节
LAG/LEAD 本身开销小，但排序代价高。若基表无合适索引，ORDER BY 字段未建索引，大表执行会慢。另外，差值列含大量 NULL 时，聚合或后续计算易出错。
- 在
ORDER BY字段上建索引（如CREATE INDEX idx_time ON sensor_readings(time);）能显著加速 - 避免在差值列上直接
SUM(delta)——NULL会被忽略，但若想把首行当作 0，应显式写SUM(COALESCE(delta, 0)) - PostgreSQL 支持
IGNORE NULLS（如LAG(value) IGNORE NULLS），但 MySQL 和 SQL Server 不支持，跨数据库迁移时需注意
真正容易被忽略的，是排序字段的业务含义是否真的支持“连续” —— 比如日志时间戳精度到秒，但两条记录同秒，ORDER BY time 就无法保证它们物理顺序，差值可能每次执行都不一样。这时候必须补一个确定性字段，比如自增 id 或带微秒的时间戳。
