Oracle环境下关联入住记录折叠的递归SQL实现方法求助

实现逻辑：从每个关联链的根节点（prev_stay为null的记录）出发，向下递归匹配所有前置关联指向当前节点的下游记录，将同一条链的所有记录归为同一个分组，最后按规则聚合得到输出结果。

递归CTE写法（适配Oracle 11gR2及以上版本） #

WITH stay_chain (
    client_id, stay_id, prev_stay, 
    root_stay, start_date, end_date, lvl
) AS (
    -- 递归锚点：取所有关联链的根节点（无前置关联的记录）
    SELECT
        client_id,
        stay_id,
        prev_stay,
        stay_id AS root_stay,
        start_date,
        end_date,
        1 AS lvl
    FROM customer_stay
    WHERE prev_stay IS NULL

    UNION ALL

    -- 递归分支：沿关联链向下匹配所有子节点
    SELECT
        cs.client_id,
        cs.stay_id,
        cs.prev_stay,
        sc.root_stay,
        cs.start_date,
        cs.end_date,
        sc.lvl + 1 AS lvl
    FROM customer_stay cs
    INNER JOIN stay_chain sc
        ON cs.prev_stay = sc.stay_id
        AND cs.client_id = sc.client_id -- 强制同客户关联，避免跨客户ID重复导致的关联错误
)
SELECT
    client_id,
    -- 取链上最高层级的记录ID作为链末端标识
    MAX(CASE WHEN lvl = max_lvl THEN stay_id END) AS stay_id,
    -- 链长度>=2时返回根节点ID，单节点链返回null（匹配示例规则）
    CASE WHEN COUNT(*) >= 2 THEN root_stay ELSE NULL END AS first_stay,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date,
    COUNT(*) AS types
FROM (
    -- 为每个链组计算最大层级（即链末端的层级值）
    SELECT
        *,
        MAX(lvl) OVER (PARTITION BY client_id, root_stay) AS max_lvl
    FROM stay_chain
) chain_with_max_lvl
GROUP BY client_id, root_stay
ORDER BY client_id, start_date;

传统CONNECT BY写法（适配低版本Oracle） #

SELECT
    client_id,
    MAX(CASE WHEN lvl = max_lvl THEN stay_id END) AS stay_id,
    CASE WHEN COUNT(*) >=2 THEN root_stay ELSE NULL END AS first_stay,
    MIN(start_date) AS start_date,
    MAX(end_date) AS end_date,
    COUNT(*) AS types
FROM (
    SELECT
        client_id,
        stay_id,
        CONNECT_BY_ROOT stay_id AS root_stay,
        LEVEL AS lvl,
        MAX(LEVEL) OVER (PARTITION BY client_id, CONNECT_BY_ROOT stay_id) AS max_lvl
    FROM customer_stay
    -- 从根节点开始遍历
    START WITH prev_stay IS NULL
    -- 沿链向下关联，强制同客户，开启循环防护
    CONNECT BY NOCYCLE
        PRIOR stay_id = prev_stay
        AND PRIOR client_id = client_id
) connect_by_chain
GROUP BY client_id, root_stay
ORDER BY client_id, start_date;

关键注意事项 #

• 所有关联条件必须带上client_id匹配逻辑：不同客户的stay_id可能存在重复，不带客户ID过滤会出现跨客户的错误关联，这是生产环境最容易踩的坑。
• 递归实现中加入NOCYCLE防护：如果业务数据存在异常的循环关联（如记录A的前置是B，记录B的前置是A），可以避免无限递归导致的查询超时。
• 匹配示例的规则逻辑：单节点独立链（只有一条记录、无下游关联）的first_stay返回null，长度>=2的链返回链根节点ID。如果后续业务规则调整（比如单节点链的first_stay返回自身ID），只需要修改CASE WHEN的判断分支即可。
• 示例输出中第一条的结束日期2010-12-30为笔误：按业务逻辑取链内最大的end_date值（即示例数据中105记录的2020-12-30）。

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