LAG 取前第 N 行的值
LAG(expr[, offset[, default]]) 返回当前行前面第 offset 行的值，越界时返回 default。七个引擎写法相同，差异在 default 是否必须是常量、是否支持 IGNORE NULLS，以及 offset 能否为 0。
各引擎写法
| 引擎 | 支持 | 写法 | 说明 | 依据 |
|---|---|---|---|---|
| MaxCompute | 支持 | LAG(expr[, offset[, default]]) OVER (...) | offset 是大于等于 0 的 BIGINT 常量，0 表示当前行，默认 1。default 是常量，默认 NULL，类型要与 expr 相同。 | 官方文档 |
| Hologres | 支持 | LAG(value[, offset[, default]]) OVER (...) | Hologres V1.1.71 起支持。执行引擎为 PQE。default 默认为空。 | 官方文档 |
| StarRocks | 支持 | LAG(expr [IGNORE NULLS] [, offset[, default]]) OVER (...) | offset 必须是正整数，默认 1；default 默认 NULL。IGNORE NULLS 3.0 起支持。4.0 起 default 可以是与 expr 类型兼容的任意表达式。 | 官方文档 |
| Doris | 支持 | LAG(expr[, offset[, default]]) OVER (...) | offset 默认 1，返回类型与 expr 相同。 | 官方文档已实测 4.1.5 |
| Hive | 支持 | lag(col[, n[, default]]) OVER (...) | 不指定行数时偏移 1 行。越过窗口开头时返回 NULL。 | 官方文档已实测 4.2.1 |
| Spark SQL | 支持 | lag(input[, offset[, default]]) OVER (...) | offset 默认 1，default 默认 NULL。偏移到的行值为 NULL 时返回 NULL，只有该行不存在时才返回 default。 | 官方文档 |
| Flink SQL | 支持 | LAG(expression[, offset][, default]) | offset 默认 1，default 默认 NULL。 | 官方文档已实测 2.3.0 |
迁移注意事项
- default 只在偏移的行不存在时生效。偏移到的行存在但值为 NULL 时，结果仍是 NULL，Spark 文档明确说明了这一点。需要把 NULL 也替换掉时，外层再套 COALESCE。
- IGNORE NULLS 只有 StarRocks 3.0 起支持；其他引擎需要用 FIRST_VALUE、LAST_VALUE 加忽略 NULL 参数或子查询改写。
- MaxCompute 要求 default 是常量且类型与 expr 一致，例如对 BIGINT 列写 LAG(x, 1, '0') 会报类型错误；StarRocks 4.0 起才允许表达式作为 default。
- MaxCompute 允许 offset 为 0 表示当前行，StarRocks 要求 offset 为正整数。
示例
计算与上一笔订单的金额差
SELECT order_id, amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;SELECT order_id, amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;SELECT order_id, amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;SELECT order_id, amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;SELECT order_id, amount - lag(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;SELECT order_id, amount - lag(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;SELECT order_id, amount - LAG(amount, 1, 0) OVER (PARTITION BY user_id ORDER BY order_time) AS diff FROM orders;