Oracle中日期重叠识别及重叠区间最小最大日期计算

嘿，这个日期区间重叠的需求在Oracle里用窗口函数就能轻松搞定，我帮你拆解一下步骤，直接上可运行的代码和解释：

第一步：先模拟测试数据 #

先按照你描述的场景创建示例表，插入对应的数据，方便你直接验证：

CREATE TABLE date_intervals (
    s_no NUMBER,
    start_date DATE,
    end_date DATE
);

INSERT INTO date_intervals VALUES (1, TO_DATE('2023-01-01', 'YYYY-MM-DD'), TO_DATE('2023-01-10', 'YYYY-MM-DD'));
INSERT INTO date_intervals VALUES (2, TO_DATE('2023-02-01', 'YYYY-MM-DD'), TO_DATE('2023-02-15', 'YYYY-MM-DD'));
INSERT INTO date_intervals VALUES (3, TO_DATE('2023-02-10', 'YYYY-MM-DD'), TO_DATE('2023-02-20', 'YYYY-MM-DD'));
INSERT INTO date_intervals VALUES (4, TO_DATE('2023-02-18', 'YYYY-MM-DD'), TO_DATE('2023-02-25', 'YYYY-MM-DD'));
INSERT INTO date_intervals VALUES (5, TO_DATE('2023-02-22', 'YYYY-MM-DD'), TO_DATE('2023-03-05', 'YYYY-MM-DD'));
INSERT INTO date_intervals VALUES (6, TO_DATE('2023-04-01', 'YYYY-MM-DD'), TO_DATE('2023-04-10', 'YYYY-MM-DD'));
INSERT INTO date_intervals VALUES (7, TO_DATE('2023-04-05', 'YYYY-MM-DD'), TO_DATE('2023-04-15', 'YYYY-MM-DD'));
COMMIT;

第二步：核心SQL实现需求 #

这里用CTE（公共表表达式）分两步处理：先识别重叠分组，再计算每组的统计值，最后关联输出结果：

WITH grouped_intervals AS (
    SELECT
        s_no,
        start_date,
        end_date,
        -- 标记是否开启新组：如果当前行的开始日期 <= 上一行的结束日期，说明属于同一组，标记0；否则标记1
        CASE 
            WHEN start_date <= LAG(end_date) OVER (ORDER BY s_no) THEN 0
            ELSE 1
        END AS new_group_flag,
        -- 累计标记值得到唯一组ID
        SUM(CASE 
                WHEN start_date <= LAG(end_date) OVER (ORDER BY s_no) THEN 0
                ELSE 1
            END) OVER (ORDER BY s_no) AS group_id
    FROM date_intervals
),
group_summary AS (
    -- 计算每组的最小开始日期和最大结束日期
    SELECT
        group_id,
        MIN(start_date) AS min_start_date,
        MAX(end_date) AS max_end_date
    FROM grouped_intervals
    GROUP BY group_id
)
SELECT
    gi.s_no,
    gi.start_date,
    gi.end_date,
    gs.group_id,
    gs.min_start_date,
    gs.max_end_date,
    -- 标记该行是否属于重叠组
    CASE 
        WHEN (SELECT COUNT(*) FROM grouped_intervals WHERE group_id = gs.group_id) > 1 THEN '重叠行'
        ELSE '无重叠'
    END AS overlap_status
FROM grouped_intervals gi
JOIN group_summary gs ON gi.group_id = gs.group_id
ORDER BY gi.s_no;

结果说明 #

运行上面的SQL后，你会得到完全符合需求的结果：

• S.NO=1单独为一组（group_id=1），overlap_status显示“无重叠”
• S.NO=2到5属于同一重叠组（group_id=2），min_start_date是2023-02-01，max_end_date是2023-03-05，所有行都标记为“重叠行”
• S.NO=6到7属于另一重叠组（group_id=3），min_start_date是2023-04-01，max_end_date是2023-04-15，所有行标记为“重叠行”

小提示 #

如果你的数据不是按S.NO排序的，而是需要按日期顺序来判断重叠，只需要把窗口函数里的ORDER BY s_no改成ORDER BY start_date即可，这样分组会更准确，避免因为序号和日期顺序不一致导致的错误。

内容的提问来源于stack exchange，提问作者Yatindra Kumar Janghel