LAG()识别状态切换需用IS DISTINCT FROM处理NULL和首行，必须结合PARTITION BY按业务主键分组、先过滤有效状态再开窗，并用状态段分组计算持续时间。
LAG() 是识别状态切换最直接的手段，但直接写 status != LAG(status) 会漏掉 NULL 场景、跳过首行、且无法区分“真变化”和“数据缺失”。
为什么 status != LAG(status) 在首行和 NULL 场景下不可靠
第一行的 LAG(status) 必然为 NULL，而任何值与 NULL 比较（!=、=、>）结果都是 UNKNOWN，在 WHERE 中会被过滤掉——看起来像“自动忽略”，实则掩盖了初始状态是否异常（比如一上来就是 'error'）。更麻烦的是，当 status 本身为 NULL 时：NULL != NULL 仍为 UNKNOWN，导致两次 NULL 状态之间切换被漏判。
必须用安全比较方式：
- PostgreSQL 推荐：使用
status IS DISTINCT FROM LAG(status)（它把NULL当作一个确定值处理） - MySQL / SQL Server 需拆解：
(status != LAG(status)) OR (status IS NULL) != (LAG(status) IS NULL) - 别把
LAG()放WHERE——窗口函数不能出现在WHERE子句，得套一层子查询或 CTE
如何确保切换点对应“业务上一次有效状态”，而非“物理上一行”
原始日志常含无效记录：比如重试写入、心跳保活、校验失败的中间态。如果直接对全量日志跑 LAG()，可能把 'pending' → 'pending'（重试）误标为无变化，或把 'pending' → 'timeout'（失败）当作有效切换。
正确做法是先筛出“有效快照”，再开窗：
- 用
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC)标记每组最新有效记录 - 或加业务条件过滤：如
WHERE status IN ('active', 'inactive', 'completed')，排除'retrying'、'init'等中间态 - 再对过滤后的结果集执行
LAG(status)，保证偏移对象是语义明确的状态节点
PARTITION BY 忘加就全乱套
不加 PARTITION BY 的 LAG(status) OVER (ORDER BY updated_at) 会把所有用户的日志混在一起排序，A 用户的 'completed' 可能和 B 用户的 'created' 比较，切换点完全失真。
必须按业务主键分区：
- 订单状态切换 →
PARTITION BY order_id ORDER BY updated_at - 用户登录态变更 →
PARTITION BY user_id ORDER BY event_time - 设备在线状态 →
PARTITION BY device_id ORDER BY reported_at - 排序字段要有唯一性：仅用
DATE(updated_at)易重复，建议补id或用updated_at, id复合排序
切换点时间差计算容易错位
想算“上次状态持续了多久”，常写 updated_at - LAG(updated_at) OVER (...)。问题在于：如果前一行不是同一状态的终点（比如上一行是 'pending'，当前是 'completed'），那这个差值就不是本次状态的持续时间，而是两个不同状态之间的间隔。
更稳妥的做法是先标记切换点，再对连续相同状态分组：
- 用
COUNT(*) FILTER (WHERE status IS DISTINCT FROM LAG(status)) OVER (...)或累计和方式生成状态段 ID - 再按该段 ID 分组，取
MIN(updated_at)和MAX(updated_at)得到真实区间 - 直接用
LAG()算差只适用于“每次变更都代表新状态开始”的强假设场景
真正难的不是写出 LAG()，而是确认你对比的“前一行”在业务上是否真的构成一次有意义的状态锚点——这取决于你怎么定义有效记录、怎么分组、以及是否容忍空缺和重复。
