LEAD函数需配合PARTITION BY task_id和ORDER BY event_time, id使用，防止跨任务混算和时间重复导致排序不稳定；计算停留时长须用CASE过滤NULL和负值；按stage分组求平均前应先剔除NULL时长并优先用中位数；结合LEAD与状态机可识别阶段流转异常。
LEAD函数怎么写才能正确获取下一阶段时间
直接用 LEAD 取下一行的 event_time 是常见做法，但容易忽略排序逻辑和分区边界——如果任务有多个实例（比如不同 task_id），不加 PARTITION BY task_id 会导致跨任务混算。必须确保：同一任务内按时间严格升序，且每个 task_id 独立计算。
典型写法是：
SELECT
task_id,
stage,
event_time,
LEAD(event_time) OVER (
PARTITION BY task_id
ORDER BY event_time, id -- 加 id 防止时间重复时排序不稳定
) AS next_event_time
FROM task_events;
注意：ORDER BY 中只用 event_time 不够稳妥；若存在并发写入或精度为秒的时间戳，可能产生相同值，此时不加次级排序字段（如自增 id）会导致 LEAD 结果不可预期。
计算停留时长时 NULL 和负值怎么处理
LEAD 在最后一行返回 NULL，直接做减法会得 NULL；更隐蔽的问题是：如果数据乱序或人工补录导致后一事件时间早于前一事件，差值会是负数——这不该当作有效停留时长。
推荐用 CASE 显式过滤：
SELECT
task_id,
stage,
event_time,
next_event_time,
CASE
WHEN next_event_time IS NOT NULL
AND next_event_time > event_time
THEN EXTRACT(EPOCH FROM (next_event_time - event_time))::INT
ELSE NULL
END AS duration_sec
FROM (...);
-
EXTRACT(EPOCH FROM ...)是 PostgreSQL 写法；MySQL 用TIMESTAMPDIFF(SECOND, event_time, next_event_time)；SQL Server 用DATEDIFF(second, event_time, next_event_time) - 别用
COALESCE(LEAD(...), NOW())填充 NULL——未完结任务强行按“当前时间”算，会严重高估平均停留时长
为什么按 stage 分组再算平均会出错
直接对 duration_sec 按 stage 做 AVG() 是错的：一个任务从 A→B→C，B 阶段停留时长是 B.event_time 到 C.event_time 的差，但如果你把所有 B 阶段记录（无论前后阶段是否完整）都拉出来平均，就会混入大量“B 后无后续事件”的脏数据（即 next_event_time IS NULL 被你忽略或误填了默认值）。
正确做法分两步：
- 先过滤掉
duration_sec IS NULL的行（即排除末尾阶段和异常时间倒置） - 再按
stage分组聚合，且优先用PERCENTILE_CONT(0.5)替代AVG——中位数对异常长尾更鲁棒 - 如果业务要求“每个任务只贡献一个 B 阶段时长”，需先用窗口函数标出每个任务中 B 阶段的唯一有效行（例如
ROW_NUMBER() OVER (PARTITION BY task_id, stage ORDER BY event_time)= 1）
LEAD 配合状态机校验能提前发现数据断点
单纯算时长不够，还要确认阶段流转是否合规。比如合法路径是 created → processing → done，但数据库里可能出现 created → done 跳过 processing，或者重复出现 processing。这时可在 LEAD 基础上加状态检查：
SELECT *,
LEAD(stage) OVER (PARTITION BY task_id ORDER BY event_time) AS next_stage,
CASE
WHEN stage = 'created' AND next_stage != 'processing' THEN 'missing_processing'
WHEN stage = 'processing' AND next_stage != 'done' THEN 'missing_done'
END AS anomaly_flag
FROM task_events;
这种标记能快速定位埋点缺失、状态更新漏发、或补偿脚本重复执行等问题——比盯着平均时长下降 2 秒更有诊断价值。
真正难的不是写对 LEAD，而是搞清“哪个时间才算阶段起点”“谁有权定义阶段终点”“下游系统是否真的按这个顺序触发事件”。函数只是镜子，照出的是业务流程本身的清晰度。
