如何按分组获取最长时间路径并聚合重叠时间范围？

需要汇总原始数据，合并重叠时间区间并输出最小/最大日期。现有Oracle SQL代码可获取每个ID对应的最长PATH，但无法按分组筛选出组内的整体最长PATH，寻求解决方法。

原始表 #

预期结果表 #

修改后的解决方案代码 #

WITH CTE as (
            Select 1 as ID, 'Datensatz1A' as Daten, TO_DATE('2023-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 1 as Gruppe FROM DUAL
            UNION Select 2 as ID, 'Datensatz2A' as Daten, TO_DATE('2023-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-05-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 1 as Gruppe FROM DUAL
            UNION Select 3 as ID, 'Datensatz3A' as Daten, TO_DATE('2023-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-05-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 1 as Gruppe FROM DUAL
            UNION Select 4 as ID, 'Datensatz4A' as Daten, TO_DATE('2023-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-05-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 1 as Gruppe FROM DUAL
            UNION Select 5 as ID, 'Datensatz5A' as Daten, TO_DATE('2023-05-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-05-15 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 2 as Gruppe FROM DUAL
            UNION Select 6 as ID, 'Datensatz1B' as Daten, TO_DATE('2023-05-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-05-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 2 as Gruppe FROM DUAL
            UNION Select 7 as ID, 'Datensatz2B' as Daten, TO_DATE('2023-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 3 as Gruppe FROM DUAL
            UNION Select 8 as ID, 'Datensatz3B' as Daten, TO_DATE('2023-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 3 as Gruppe FROM DUAL
            UNION Select 9 as ID, 'Datensatz4B' as Daten, TO_DATE('2023-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2023-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 3 as Gruppe FROM DUAL
            UNION Select 10 as ID, 'Datensatz5B' as Daten, TO_DATE('2021-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Beginn_Zeitstempel, TO_DATE('2022-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') as Ende_Zeitstempel, 3 as Gruppe FROM DUAL
    ),
    RecursiveCTE (Start_ID, End_ID, Daten, Beginn_Zeitstempel, Ende_Zeitstempel, Gruppe, Path, Path_Length) AS (
        SELECT
            ID,
            ID,
            Daten,
            Beginn_Zeitstempel,
            Ende_Zeitstempel,
            Gruppe,
            CAST(ID AS VARCHAR2(100)) AS PATH,
            1 AS Path_Length
        FROM CTE
        UNION ALL
        SELECT
            r.Start_ID,
            c.ID,
            c.Daten,
            LEAST(r.Beginn_Zeitstempel, c.Beginn_Zeitstempel),
            GREATEST(r.Ende_Zeitstempel, c.Ende_Zeitstempel),
            c.Gruppe,
            r.Path || ' -> ' || c.ID AS PATH,
            r.Path_Length + 1 AS Path_Length
        FROM RecursiveCTE r
        JOIN CTE c ON r.Gruppe = c.Gruppe
                    AND c.ID NOT IN (SELECT regexp_substr(r.Path, '[0-9]+', 1, LEVEL) FROM dual CONNECT BY LEVEL <= regexp_count(r.Path, '->') + 1)
                    AND r.Beginn_Zeitstempel <= c.Ende_Zeitstempel
                    AND c.Beginn_Zeitstempel <= r.Ende_Zeitstempel
    ),
    GroupMaxPath AS (
        SELECT
            Gruppe,
            Beginn_Zeitstempel,
            Ende_Zeitstempel,
            MAX(PATH) KEEP (DENSE_RANK LAST ORDER BY Path_Length) AS MAX_PATH,
            MAX(Path_Length) AS Max_Length
        FROM RecursiveCTE
        GROUP BY Gruppe, Beginn_Zeitstempel, Ende_Zeitstempel
    ),
    FinalResult AS (
        SELECT
            r.Start_ID,
            r.Daten,
            r.Beginn_Zeitstempel,
            r.Ende_Zeitstempel,
            r.Gruppe,
            r.Path
        FROM RecursiveCTE r
        JOIN GroupMaxPath m 
            ON r.Gruppe = m.Gruppe
            AND r.Beginn_Zeitstempel = m.Beginn_Zeitstempel
            AND r.Ende_Zeitstempel = m.Ende_Zeitstempel
            AND r.Path = m.MAX_PATH
        GROUP BY r.Start_ID, r.Daten, r.Beginn_Zeitstempel, r.Ende_Zeitstempel, r.Gruppe, r.Path
    )
    SELECT * FROM FinalResult ORDER BY Gruppe, Start_ID;

关键改动说明 #

1. 修正递归起始条件：移除原有的NOT EXISTS过滤，让所有节点都作为递归起点，避免遗漏潜在的合并路径。
2. 优化重叠区间判断：改用标准区间重叠逻辑（r.Beginn_Zeitstempel <= c.Ende_Zeitstempel AND c.Beginn_Zeitstempel <= r.Ende_Zeitstempel），确保所有有交集的区间都能被合并。
3. 新增路径长度字段：添加Path_Length直接记录路径包含的节点数，比通过字符串长度判断更准确，避免ID位数差异导致的误差。
4. 按分组+合并区间筛选最长路径：新增GroupMaxPath CTE，先按分组和合并后的起止日期分组，提取每组内节点数最多的路径；再关联回递归CTE获取完整信息，确保每个合并区间只保留一条最长路径。
5. 改进重复节点判断：用正则表达式拆分路径中的ID，判断当前节点是否已在路径中，比原INSTR方法更可靠。

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