Oracle层级查询：末端交汇的两条路径合并问题

表结构与示例数据 #

CREATE TABLE mappings 
(
    from_name varchar(20),
    to_name varchar(20),
    start_date date
);

INSERT INTO mappings VALUES ('node 1', 'node 1-2', date'2023-1-1');
INSERT INTO mappings VALUES ('node 1-2', 'node 1-3', date'2023-2-1');
INSERT INTO mappings VALUES ('node 1-3', 'Final Node 1', date'2023-3-1');
INSERT INTO mappings VALUES ('node 2', 'Final Node 2', date'2023-1-15');
INSERT INTO mappings VALUES ('node 3', 'Node 3-1', date'2023-2-1');
INSERT INTO mappings VALUES ('Node 3-1', 'Final Node 1', date'2023-2-15');

映射路径说明 #

• 路径1：Node 1 → Node 1-2 → Node 1-3 → Final Node 1
• 路径2：Node 2 → Final Node 2
• 路径3：Node 3 → Node 3-1 → Final Node 1

两条路径最终交汇到Final Node 1，需要为每个节点计算到最终节点的生效日期区间。

期望输出 #

注：原期望输出中的node 3-2为笔误，应为node 3。

现有代码问题 #

原代码使用PARTITION BY end_node计算end_date，但不同路径的节点会被分到同一分区，导致LEAD函数错误获取其他路径的节点日期，比如路径3的node3会取到路径1中node1-2的start_date，而非自身路径下Node3-1的start_date。

WITH map AS 
(
    SELECT 
       connect_by_root(from_name) AS begin_node,
       (to_name) AS end_node,
       connect_by_root(start_date) AS start_date,
       LEVEL
   FROM 
       mappings
   WHERE 
       connect_by_isleaf = 1 
   CONNECT BY nocycle from_name = PRIOR to_name
)
SELECT 
    m.begin_node,
    m.end_node,
    start_date,
    LEAD(start_date - 1, 1) OVER (PARTITION BY end_node ORDER BY start_date) AS end_date
FROM 
    map m

解决方案 #

需要按每条完整路径分组计算日期区间，而非按最终节点。通过CONNECT BY遍历每个节点的完整路径，用路径根节点区分不同路径，再用LEAD获取同一路径中下一个节点的生效日期，以此计算当前节点的结束日期。

WITH path_nodes AS (
    SELECT
        connect_by_root(from_name) AS path_root,
        from_name AS begin_node,
        connect_by_root(to_name) AS end_node,
        start_date,
        LEVEL AS path_level
    FROM mappings
    CONNECT BY NOCYCLE from_name = PRIOR to_name
    UNION ALL
    -- 补充最终节点的自映射记录，确保末端节点显示完整
    SELECT
        to_name AS path_root,
        to_name AS begin_node,
        to_name AS end_node,
        start_date,
        1 AS path_level
    FROM mappings
    WHERE to_name NOT IN (SELECT from_name FROM mappings)
)
SELECT
    begin_node AS "From",
    end_node AS "To",
    start_date AS "Start_Date",
    -- 同一路径下，用下一个节点的start_date减1作为当前节点的end_date
    CASE
        WHEN LEAD(start_date) OVER (PARTITION BY path_root ORDER BY path_level) IS NOT NULL
        THEN LEAD(start_date) OVER (PARTITION BY path_root ORDER BY path_level) - 1
        ELSE NULL
    END AS "End_Date"
FROM path_nodes
ORDER BY end_node, start_date;

代码说明 #

1. path_nodes CTE：通过CONNECT BY遍历所有节点的路径，用connect_by_root(from_name)标记每条路径的根节点，确保同一路径的节点归为一组；同时补充最终节点的自映射记录，保证末端节点的信息完整。
2. 主查询：按path_root分组，用LEAD获取同一路径中下一个节点的生效日期，减1后得到当前节点的结束日期；路径末端节点无后续节点，end_date设为空。

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