Oracle SQL：如何生成多Code的共同日期区间（可用MATCH_RECOGNIZE？）

需求说明 #

需要计算不同code对应的日期区间的共同重叠时间段。现有临时表table_gtt包含id、count、code、date_from、date_to字段，示例数据中code=201有两个不重叠的日期区间，code=4013有一个长区间，目标是得到所有code日期区间的共同重叠部分，示例预期结果为两个时间段。

替代场景验证 #

• 若code=4013的区间为10/08/2022 - 08/09/2022，预期结果为两个时间段；
• 若code=4013的区间为15/09/2022 - 22/09/2022，预期结果为两个时间段；
• 若code=4013的区间为11/09/2022 - 12/09/2022，预期结果为空。

当前使用MATCH_RECOGNIZE的方法在code=4013同时覆盖code=201的两个区间时无法正确计算，需重新设计解决方案。

测试数据创建语句 #

CREATE GLOBAL TEMPORARY TABLE table_gtt
ON COMMIT PRESERVE ROWS
AS
select 4364 id, 2 count, 201 code, TO_DATE('01/08/2022 15:00', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('10/09/2022 22:00', 'DD/MM/YYYY HH24:MI') date_to from dual
union all
select 4364 id, 2 count, 201 code, TO_DATE('13/09/2022 05:20', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('30/09/2022 17:00', 'DD/MM/YYYY HH24:MI') date_to from dual
union all
select 4364 id, 2 count, 4013 code, TO_DATE('29/08/2022 04:48', 'DD/MM/YYYY HH24:MI') date_from, TO_DATE('19/11/2022 13:43', 'DD/MM/YYYY HH24:MI') date_to from dual;

现有问题代码 #

SELECT * 
   FROM table_gtt
 MATCH_RECOGNIZE (PARTITION BY id
                  ORDER     BY date_from, date_to
                  MEASURES
                      MIN(date_from)         date_from
                     ,MAX(date_to)           date_to
                  PATTERN (overlap* last_row)
                  DEFINE
                    overlap AS MAX(date_to) >= NEXT(date_from)
                  ) 

解决方案 #

方法一：通用事件点统计法（支持任意数量Code） #

核心思路是将所有区间的起止时间拆分为事件点，按时间排序后统计当前被覆盖的Code数量，当覆盖数等于该ID下的总Code数时，记录有效重叠区间。

WITH event_points AS (
    SELECT id, date_from AS event_time, 1 AS delta, code
    FROM table_gtt
    UNION ALL
    SELECT id, date_to AS event_time, -1 AS delta, code
    FROM table_gtt
),
ranked_events AS (
    SELECT 
        id,
        event_time,
        delta,
        code,
        -- 同时间点优先处理结束事件，避免端点重复计算
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY event_time, delta DESC) AS rn
    FROM event_points
),
code_counts AS (
    SELECT 
        id,
        event_time,
        -- 统计当前活跃的不同Code数量
        COUNT(DISTINCT CASE WHEN SUM(delta) OVER (PARTITION BY id ORDER BY rn) > 0 THEN code END) AS active_codes
    FROM ranked_events
    GROUP BY id, event_time
),
total_codes AS (
    SELECT id, COUNT(DISTINCT code) AS total_code_count
    FROM table_gtt
    GROUP BY id
),
overlap_intervals AS (
    SELECT 
        cc.id,
        cc.event_time AS interval_start,
        LEAD(cc.event_time) OVER (PARTITION BY cc.id ORDER BY cc.event_time) AS interval_end,
        tc.total_code_count
    FROM code_counts cc
    JOIN total_codes tc ON cc.id = tc.id
    WHERE cc.active_codes = tc.total_code_count
)
SELECT 
    id,
    interval_start AS date_from,
    interval_end AS date_to
FROM overlap_intervals
WHERE interval_start < interval_end; -- 过滤零长度无效区间

方法二：固定Code交集法（仅适用于已知Code数量场景） #

如果Code数量固定（如示例中仅2个），可直接计算不同Code区间的所有可能交集，再输出有效结果：

WITH code_201 AS (
    SELECT date_from, date_to FROM table_gtt WHERE code = 201
),
code_4013 AS (
    SELECT date_from, date_to FROM table_gtt WHERE code = 4013
),
intersections AS (
    SELECT 
        GREATEST(a.date_from, b.date_from) AS date_from,
        LEAST(a.date_to, b.date_to) AS date_to
    FROM code_201 a
    CROSS JOIN code_4013 b
    WHERE GREATEST(a.date_from, b.date_from) < LEAST(a.date_to, b.date_to)
)
SELECT date_from, date_to FROM intersections;

方案说明 #

• 方法一通用性强，能适配所有替代场景：无论Code数量多少、区间如何变化，都能准确输出所有Code共同覆盖的重叠时间段；
• 方法二更简洁，但仅适用于固定数量的Code场景，后续新增Code需修改代码逻辑。

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