MySQL使用UNION ALL结合LEAD/LAG出现异常值问题排查

我来帮你拆解下这个问题，这种情况在使用UNION ALL合并查询时其实挺常见的，咱们从最可能的原因开始排查：

最常见的原因：过滤条件位置错误 #

你提到单独执行查询时，act_in_sec < 0的结果不会返回，说明单独查询时应该有过滤逻辑（比如WHERE act_in_sec >= 0）。但如果合并查询时，你把过滤条件放在了整个UNION ALL的末尾，那这个条件只会作用于最后一个子查询，前面的子查询返回的负值会被保留下来。

举个错误示例：

-- 错误写法：WHERE只作用于最后一个子查询
SELECT Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act, TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec 
FROM (...) Y
UNION ALL
SELECT Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act, TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec 
FROM (...) Y
WHERE act_in_sec >= 0;

正确的做法有两种：

1. 给每个子查询都加上过滤条件：

-- 每个子查询单独过滤
(SELECT Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act, TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec 
 FROM (...) Y
 WHERE act_in_sec >= 0)
UNION ALL
(SELECT Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act, TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec 
 FROM (...) Y
 WHERE act_in_sec >= 0);

2. 把所有UNION ALL的结果包裹在一个子查询里，统一过滤：

-- 统一过滤整个合并后的结果
SELECT * FROM (
    SELECT Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act, TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec 
    FROM (...) Y
    UNION ALL
    SELECT Y.machine, Y.start_time, Y.end_time, TIMEDIFF(Y.end_time,Y.start_time) as act, TIME_TO_SEC(TIMEDIFF(Y.end_time,Y.start_time)) act_in_sec 
    FROM (...) Y
) combined_results
WHERE act_in_sec >= 0;

其他可能的排查方向 #

如果过滤条件位置没问题，再逐一检查以下几点：

• 验证子查询的原始数据：把UNION ALL的每个子查询单独执行，导出结果仔细检查是否真的没有start_time > end_time的情况——有时候可能你以为单独执行没问题，但某个机器的数据集里存在边缘异常数据（比如插入时的时间错误）。
• 检查时区和时间字段类型：确认所有机器的数据库时区设置一致，且start_time和end_time是相同的时间类型（比如都是DATETIME或TIMESTAMP），避免时区转换导致时间差计算出错。
• 检查next_ts的生成逻辑：如果你的子查询里是用窗口函数（比如LEAD(ts) OVER (PARTITION BY machine ORDER BY ts)）生成next_ts，要确保ORDER BY的字段正确，没有因为排序错误导致next_ts比当前ts小的情况。

内容的提问来源于stack exchange，提问作者mysticBliss