生成SQL查询补全缺失时间段：区分故障与正常时段

现有表A，包含start_date和end_date字段，用于存储故障发生的时间段，表内数据如下：

需要编写SQL查询，以2021年8月1日为例，补全表中未被故障时段覆盖的时间段：原故障时段标记为failure，未覆盖的正常时段标记为normal，最终输出结果如下（注：示例中第5条start_date应为"2021-08-01 05:33:00"，属于笔误）：

解决方案（兼容主流关系型数据库） #

以下查询适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库：

WITH ordered_failures AS (
    -- 筛选当日故障数据并排序
    SELECT 
        start_date, 
        end_date,
        ROW_NUMBER() OVER (ORDER BY start_date) AS rn
    FROM A
    WHERE DATE(start_date) = '2021-08-01'
),
normal_periods AS (
    -- 生成相邻故障之间的正常时段
    SELECT 
        DATE_ADD(LAG(end_date) OVER (ORDER BY rn), INTERVAL 1 MINUTE) AS start_date,
        DATE_ADD(start_date, INTERVAL -1 MINUTE) AS end_date,
        'normal' AS type
    FROM ordered_failures
    UNION ALL
    -- 生成当日0点至首个故障前的正常时段
    SELECT 
        '2021-08-01 00:00:00' AS start_date,
        DATE_ADD((SELECT start_date FROM ordered_failures WHERE rn = 1), INTERVAL -1 MINUTE) AS end_date,
        'normal' AS type
    WHERE EXISTS (SELECT 1 FROM ordered_failures)
    UNION ALL
    -- 生成最后一个故障至当日结束的正常时段
    SELECT 
        DATE_ADD((SELECT end_date FROM ordered_failures ORDER BY rn DESC LIMIT 1), INTERVAL 1 MINUTE) AS start_date,
        '2021-08-01 23:59:00' AS end_date,
        'normal' AS type
    WHERE EXISTS (SELECT 1 FROM ordered_failures)
)
-- 合并故障与正常时段，排序后输出
SELECT 
    ROW_NUMBER() OVER (ORDER BY start_date) AS rows,
    start_date,
    end_date,
    type
FROM (
    SELECT start_date, end_date, 'failure' AS type FROM ordered_failures
    UNION ALL
    SELECT start_date, end_date, type FROM normal_periods
    WHERE start_date <= end_date -- 过滤无效时段
) AS combined
ORDER BY start_date;

逻辑说明 #

1. ordered_failures：筛选2021-08-01的故障数据，按时间排序并添加行号，方便关联前后时段。
2. normal_periods：
   • 生成两个故障之间的间隙时段：以上一个故障结束时间+1分钟为起始，当前故障起始时间-1分钟为结束。
   • 生成当日起始到第一个故障前的时段。
   • 生成最后一个故障结束到当日23:59的时段。
3. 最后合并两类时段，过滤掉起始时间晚于结束时间的无效数据，按时间排序并生成结果行号。

MySQL 5.x兼容版本（无窗口函数） #

如果使用不支持窗口函数的MySQL 5.x，可使用变量实现：

SELECT 
    @row := @row + 1 AS rows,
    start_date,
    end_date,
    type
FROM (
    -- 故障时段
    SELECT start_date, end_date, 'failure' AS type FROM A WHERE DATE(start_date) = '2021-08-01'
    UNION ALL
    -- 当日起始至首个故障前的正常时段
    SELECT 
        '2021-08-01 00:00:00' AS start_date,
        DATE_SUB((SELECT MIN(start_date) FROM A WHERE DATE(start_date) = '2021-08-01'), INTERVAL 1 MINUTE) AS end_date,
        'normal' AS type
    WHERE EXISTS (SELECT 1 FROM A WHERE DATE(start_date) = '2021-08-01')
    UNION ALL
    -- 故障之间的正常时段
    SELECT 
        DATE_ADD(a1.end_date, INTERVAL 1 MINUTE) AS start_date,
        DATE_SUB(a2.start_date, INTERVAL 1 MINUTE) AS end_date,
        'normal' AS type
    FROM (
        SELECT 
            start_date, 
            end_date,
            @rn := @rn + 1 AS rn
        FROM A, (SELECT @rn := 0) r
        WHERE DATE(start_date) = '2021-08-01'
        ORDER BY start_date
    ) a1
    JOIN (
        SELECT 
            start_date, 
            end_date,
            @rn2 := @rn2 + 1 AS rn
        FROM A, (SELECT @rn2 := 0) r
        WHERE DATE(start_date) = '2021-08-01'
        ORDER BY start_date
    ) a2 ON a1.rn = a2.rn - 1
    UNION ALL
    -- 最后一个故障至当日结束的正常时段
    SELECT 
        DATE_ADD((SELECT MAX(end_date) FROM A WHERE DATE(start_date) = '2021-08-01'), INTERVAL 1 MINUTE) AS start_date,
        '2021-08-01 23:59:00' AS end_date,
        'normal' AS type
    WHERE EXISTS (SELECT 1 FROM A WHERE DATE(start_date) = '2021-08-01')
) AS combined
WHERE start_date <= end_date
ORDER BY start_date;

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