LAG()和LEAD()在不规则时间序列中更实用，因其基于真实时间戳ORDER BY精确取上下行值，而ROW_NUMBER()仅按固定行号排序导致业务逻辑错位；二者仅支持整数偏移，时间跨度需用自连接模拟。
为什么 LAG() 和 LEAD() 在不规则时间序列里比 ROW_NUMBER() 更实用
因为不规则时间序列没有固定步长，按行号分组或排序会错位——比如两条记录间隔 2 分钟和 17 分钟，在业务上意义完全不同，但 ROW_NUMBER() 一律当成“相邻第 1 行”和“相邻第 2 行”。LAG() 和 LEAD() 允许你基于真实时间戳偏移，直接取上一条/下一条的值，且能指定 ORDER BY timestamp 精确对齐时序逻辑。
实操建议：
- 必须显式写
ORDER BY timestamp（不能只靠表物理顺序），否则结果不可预测 - 慎用默认的
LAG(value)：它等价于LAG(value, 1)，但若某条记录缺失、或需跨多条找前一个有效状态，得写LAG(value, 2)并配合WHERE过滤空值 - 如果时间戳有重复，
ORDER BY timestamp, id补充二级排序，避免窗口内顺序抖动
INTERVAL 类型参数在 LAG() 中不被支持，怎么办
SQL 标准和主流数据库（PostgreSQL、MySQL 8.0+、Snowflake）的 LAG() 只接受整数偏移量，不支持类似 LAG(value, INTERVAL '5 minutes') 这种时间跨度查找。这不是 bug，是设计限制——窗口函数作用域是“行位置”，不是“时间距离”。
替代方案：
- 用自连接 + 时间范围过滤模拟：例如
ON t2.timestamp > t1.timestamp - INTERVAL '5 minutes' AND t2.timestamp ，再GROUP BY t1.id+MAX(t2.value) - 在 PostgreSQL 中可用
ARRAY_AGG()+FILTER构造时间邻域：先ARRAY_AGG(value) FILTER (WHERE timestamp BETWEEN t1.timestamp - '5min'::interval AND t1.timestamp)，再取数组末尾元素 - 如果查询频次高、数据量大，提前建物化视图或带时间索引的辅助表，避免每次运行都扫描全量时间窗
用 LAST_VALUE() 做滚动状态时，RANGE 和 ROWS 的行为差异很关键
想获取“截止当前行、过去 1 小时内最后出现的 status”，用 LAST_VALUE(status) OVER (ORDER BY timestamp RANGE BETWEEN INTERVAL '1 hour' PRECEDING AND CURRENT ROW) 才正确；若误写成 ROWS，就变成“最近 N 行里的最后一个”，和时间无关。
注意点：
- PostgreSQL 和 Oracle 支持
RANGE配合INTERVAL，但 MySQL 目前仅支持ROWS，强行用RANGE会报错Window 'w' uses frame clause with unsupported frame unit -
LAST_VALUE()默认忽略 NULL，但如果你的状态字段本身允许 NULL，需加RESPECT NULLS显式控制（如LAST_VALUE(status) RESPECT NULLS） - 在 Spark SQL 或 BigQuery 中，
RANGE对时间列的支持依赖类型是否为TIMESTAMP或DATETIME，STRING类型的时间字段必须先CAST否则静默降级为ROWS
当时间序列存在高频采样 + 大量空值，IGNORE NULLS 不是万能解
有些引擎（如 BigQuery、PostgreSQL 14+）支持 LAG(value) IGNORE NULLS，看起来能跳过中间空值直接取上一个非空值。但它只在当前窗口帧内生效，而窗口帧由 ORDER BY 和帧定义决定——如果空值集中出现在某段时间段，且该段超出帧范围，IGNORE NULLS 就失效了。
更稳的做法：
- 先用
COALESCE()或CASE WHEN把原始空值转为有意义的占位符（如'unknown'），再用普通LAG()，避免逻辑断裂 - 对超高频数据（如每秒数百条），考虑预聚合：按分钟/5 分钟分桶，用
MAX(timestamp)和FIRST_VALUE(value IGNORE NULLS)提取每个桶内最新有效值，再对桶结果做窗口计算 - 注意 Hive 和旧版 Trino 不支持
IGNORE NULLS，会直接报错Unsupported frame clause，此时只能用子查询 +ROW_NUMBER()+ 关联找上一个非空行
时间精度、空值分布、目标数据库版本三者一不匹配，窗口函数就容易返回意料之外的结果——别只盯着语法对不对，先确认你的 timestamp 列有没有隐式时区转换、是否被索引覆盖、以及执行计划里窗口帧实际扫描了多少行。
