用时间戳行替代状态标签
订单状态不要用一列 state 原地覆盖，而是按状态变化追加事实行，每行带发生时间戳，形如（order_id、state、ts）。这样既能取最新状态，也能回看完整轨迹，还能统计每个状态停留了多久。取当前状态用窗口函数按 ts 倒序取第一条即可。实现上要特别处理重复上报：同状态同时间戳被投递多次时，必须先按业务键去重，否则状态时长会被重复累计。
基本信息
| 数据形态 | 状态事件行 + 发生时间戳 |
|---|---|
| 当前状态取值 | 按 order_id 分组取 max(ts) 对应的状态 |
| 去重键 | order_id + state + ts 组合去重 |
| 适用场景 | 需要轨迹回放或状态时长分析的流程 |
典型使用场景
- 状态时长分析：统计订单在各状态的平均停留时长，定位卡点环节
- 轨迹回放：客诉排查时还原订单从创建到完成的完整状态序列
- 状态流转漏斗：统计进入各状态的数量，观察异常回退比例
- 增量补数：上游重发历史状态时按去重键幂等写入，不影响统计
- 审计留痕：状态变更时间可追溯到具体时点，满足合规要求
取最新状态与去重
取最新状态与去重
-- 取每个订单的最新状态（等价于状态列原地覆盖的结果）
SELECT order_id, state
FROM (
SELECT order_id, state,
row_number() OVER (PARTITION BY order_id ORDER BY ts DESC) AS rn
FROM fact_order_state
) t
WHERE rn = 1;
-- 去重：同一状态在同一时间戳重复上报时只保留一行
-- SELECT order_id, state, ts, count(1) FROM fact_order_state
-- GROUP BY 1,2,3 HAVING count(1) > 1; -- 结果非空说明需要去重
-- 状态时长：相邻两条状态事件的时间差
-- lead(ts) OVER (PARTITION BY order_id ORDER BY ts) - ts两种形态对照
| 对比项 | 状态事件行 | 状态列覆盖 |
|---|---|---|
| 当前状态 | 取 max(ts) 一行，逻辑清晰 | 直接读一列，最快 |
| 历史轨迹 | 完整可回放 | 永久丢失 |
| 状态时长 | 可由相邻事件计算 | 只能记录本次状态起始时间 |
| 去重要求 | 必须按业务键加时间戳去重 | 需处理并发覆盖竞态 |
去重粒度要精确到状态与时间戳：只按 order_id 与 state 去重会误删合法的重复进入（比如先取消再下单），造成轨迹缺失。
状态事件表通常按天增量累积，量大时可按业务时间分区，取当前状态时先裁剪到近若干天的分区，避免全表扫描。