Oracle SQL中查找2024年日期区间内的缺口

问题背景 #

给定一张存储零件日期区间的表，表中包含单个或多个可能重叠、连续的日期区间。需针对2024年检查每个零件的日期覆盖情况，输出存在缺口的零件及其缺失的日期区间。

原始表数据 #

part    start       end
a       1.1.2023    31.3.2024
a       1.1.2023    31.12.2025
a       1.7.2024    31.12.2025
b       1.1.2024    30.6.2024
b       1.7.2024    31.12.2024
c       1.10.2023   30.9.2024
d       1.1.2023    30.6.2024
d       1.12.2024   31.12.2025

各零件覆盖情况 #

• 零件a：存在覆盖2024全年的区间，无缺口，排除；
• 零件b：两个区间连续覆盖2024全年，无缺口，排除；
• 零件c：区间仅覆盖至2024年9月30日，缺失2024-10-01至2024-12-31；
• 零件d：两个区间间存在断层，缺失2024-07-01至2024-11-30。

期望输出 #

part    start       end
c       1.10.2024   31.12.2024
d       1.7.2024    30.11.2024

解决方案（MySQL实现） #

核心思路是先合并每个零件的重叠/连续区间，再与2024年的完整区间对比，找出未覆盖的缺口。

WITH 
-- 转换日期格式为标准日期类型
formatted_dates AS (
    SELECT 
        part,
        STR_TO_DATE(start, '%d.%m.%Y') AS start_date,
        STR_TO_DATE(end, '%d.%m.%Y') AS end_date
    FROM part_dates
),
-- 合并同一零件的重叠/连续区间
merged_intervals AS (
    SELECT 
        part,
        start_date,
        MAX(end_date) AS end_date
    FROM (
        SELECT 
            part,
            start_date,
            end_date,
            SUM(
                CASE WHEN start_date <= DATE_ADD(prev_end, INTERVAL 1 DAY) THEN 0 ELSE 1 END
            ) OVER (PARTITION BY part ORDER BY start_date) AS group_id
        FROM (
            SELECT 
                part,
                start_date,
                end_date,
                LAG(end_date) OVER (PARTITION BY part ORDER BY start_date) AS prev_end
            FROM formatted_dates
        ) t1
    ) t2
    GROUP BY part, group_id, start_date
),
-- 定义2024年的完整时间范围
year_2024 AS (
    SELECT STR_TO_DATE('01.01.2024', '%d.%m.%Y') AS year_start, STR_TO_DATE('31.12.2024', '%d.%m.%Y') AS year_end
),
-- 找出所有可能的缺口
gaps AS (
    -- 处理区间之间的缺口
    SELECT 
        mi.part,
        CASE 
            WHEN LAG(LEAST(mi.end_date, y.year_end)) OVER (PARTITION BY mi.part ORDER BY mi.start_date) IS NULL 
                THEN y.year_start
            ELSE DATE_ADD(LAG(LEAST(mi.end_date, y.year_end)) OVER (PARTITION BY mi.part ORDER BY mi.start_date), INTERVAL 1 DAY)
        END AS gap_start,
        CASE 
            WHEN mi.start_date > y.year_start 
                THEN DATE_SUB(mi.start_date, INTERVAL 1 DAY)
            ELSE NULL
        END AS gap_end
    FROM merged_intervals mi
    CROSS JOIN year_2024 y
    WHERE mi.start_date <= y.year_end AND mi.end_date >= y.year_start
    
    UNION ALL
    
    -- 处理区间未覆盖2024年末的情况
    SELECT 
        mi.part,
        DATE_ADD(LEAST(mi.end_date, y.year_end), INTERVAL 1 DAY) AS gap_start,
        y.year_end AS gap_end
    FROM merged_intervals mi
    CROSS JOIN year_2024 y
    WHERE mi.end_date < y.year_end 
        AND NOT EXISTS (
            SELECT 1 FROM merged_intervals mi2 
            WHERE mi2.part = mi.part AND mi2.start_date <= y.year_end AND mi2.end_date >= DATE_ADD(mi.end_date, INTERVAL 1 DAY)
        )
),
-- 过滤有效缺口并转换回原日期格式
valid_gaps AS (
    SELECT 
        part,
        DATE_FORMAT(gap_start, '%d.%m.%Y') AS start,
        DATE_FORMAT(gap_end, '%d.%m.%Y') AS end
    FROM gaps
    WHERE gap_start <= gap_end
)
SELECT * FROM valid_gaps ORDER BY part;

代码说明 #

1. formatted_dates：将字符串日期转换为数据库可计算的日期类型，避免字符串操作出错；
2. merged_intervals：使用窗口函数LAG和分组求和，把同一零件的重叠/连续区间合并为单个区间；
3. year_2024：明确2024年的时间范围，作为对比基准；
4. gaps：分两种情况识别缺口：一是合并后区间之间的断层，二是区间未覆盖到2024年末的尾部缺口；
5. valid_gaps：过滤掉无效缺口（如起始日期晚于结束日期），并将日期格式转回原始的dd.mm.yyyy格式。

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