直接用row_number()会错乱时间顺序，是因为仅按order_id分组而未用精确、非空、唯一的时间字段（如update_time）排序，且未处理null、重复、时区等问题；lag/lead需配合partition by order_id和order by update_time才能准确提取状态跳变。
为什么直接用 ROW_NUMBER() 会错乱时间顺序？
物流订单的状态变更往往存在多条记录，同一订单号（order_id）可能在不同时间点插入多条状态（如“已下单”→“已发货”→“已签收”），但数据库表本身不保证插入顺序就是业务发生顺序。如果只按 order_id 分组、用 ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY id)，而 id 是自增主键，那确实能反映写入顺序——但一旦有补录、重试或分库写入，id 就不可靠了。
必须依赖明确的时间字段，比如 update_time 或 event_time。更关键的是：该字段是否允许重复？是否带时区？是否为 NULL？这些都会让 ORDER BY 子句行为异常。
- 确保排序字段非空：
WHERE update_time IS NOT NULL或用COALESCE(update_time, created_time) - 处理时间相同时的确定性：
ORDER BY update_time, event_id（加一个唯一辅助字段） - 避免时区隐式转换：确认字段类型是
TIMESTAMP WITH TIME ZONE或统一转为 UTC 后再排序
如何用 LAG() 和 LEAD() 提取状态跳变？
物流状态轨迹的核心不是罗列所有记录，而是识别“从 A 到 B”的跃迁。比如“已付款 → 已发货”需要知道前一状态和当前状态；“超时未发货”则需判断“已付款”后 24 小时内无“已发货”记录。
LAG(status, 1) OVER (PARTITION BY order_id ORDER BY update_time) 能拿到上一条状态，但要注意：
- 默认窗口帧是
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，对LAG没影响，但若后续要算持续时长就得显式定义ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - 如果某订单只有 1 条记录，
LAG()返回NULL，需用COALESCE(lag_status, 'initial')做兜底 - 不要用
LAG(status) OVER (ORDER BY update_time)（漏掉PARTITION BY order_id），否则跨订单混排，结果完全不可信
示例：查出所有“已发货 → 已签收”跳变及耗时
SELECT order_id,
LAG(status) OVER (PARTITION BY order_id ORDER BY update_time) AS prev_status,
status AS curr_status,
update_time - LAG(update_time) OVER (PARTITION BY order_id ORDER BY update_time) AS duration
FROM order_events
WHERE status = 'signed' AND LAG(status) OVER (PARTITION BY order_id ORDER BY update_time) = 'shipped';
MAX() OVER 和 COUNT() OVER 在轨迹分析中容易被误用的场景
有人想快速知道“每个订单最新状态”，就写 MAX(status) OVER (PARTITION BY order_id) ——这在字典序下可能返回“waiting”而非“signed”，因为 MAX() 比的是字符串大小，不是业务顺序。同理，COUNT(*) OVER (PARTITION BY order_id) 只告诉你变更次数，但无法区分“反复退单又重下”和“线性推进”。
真正需要的是：
- 最新状态：用
FIRST_VALUE(status) OVER (PARTITION BY order_id ORDER BY update_time DESC ROWS UNBOUNDED PRECEDING) - 是否完成履约：用
BOOL_OR(status = 'signed') OVER (PARTITION BY order_id)（PostgreSQL）或MAX(CASE WHEN status = 'signed' THEN 1 ELSE 0 END) OVER (...) - 首次异常：配合
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY update_time)找第 1 条status IN ('cancelled', 'abnormal')
为什么 WINDOW 命名在复杂查询里不是可选项？
当一个查询里要同时计算“上一状态”“下一状态”“首次时间”“末次时间”“变更次数”，全部写成内联 OVER 子句会让 SQL 膨胀且难维护。比如：
SELECT order_id,
LAG(status) OVER w1 AS prev,
LEAD(status) OVER w2 AS next,
FIRST_VALUE(update_time) OVER w3 AS first_time,
LAST_VALUE(update_time) OVER w4 AS last_time
FROM order_events
WINDOW w1 AS (PARTITION BY order_id ORDER BY update_time),
w2 AS (PARTITION BY order_id ORDER BY update_time),
w3 AS (PARTITION BY order_id ORDER BY update_time ROWS UNBOUNDED PRECEDING),
w4 AS (PARTITION BY order_id ORDER BY update_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
虽然 w1 和 w2 看似一样，但 w3 和 w4 的 ROWS 定义不同，不能复用。漏写 WINDOW 或复用错误帧定义，会导致结果静默偏差——比如 LAST_VALUE 不加 ROWS BETWEEN ... 默认只看当前行，永远等于 update_time。
实际开发中，状态字段命名不一致（status / event_type / state_code）、时间字段精度不统一（秒级 vs 毫秒级）、甚至同一个状态有多个别名（“delivered” 和 “signed” 并存），这些细节比窗口语法本身更容易导致轨迹还原失败。
