Oracle SQL实现带自动跳过规则的父子步骤关联查询

需求说明 #

基于STEPS表（含step步骤编号、auto_skip自动跳过标记两列）生成child（子步骤）和parent（父步骤）关联表，需满足：

1. 默认规则：每个步骤的父步骤为顺序下一个步骤（可通过LEAD实现）；
2. 自动跳过规则：若某步骤的下一个步骤属于连续相同的auto_skip标记组，需额外生成一条父子关系，让该步骤直接指向标记组之后的第一个不同标记/无标记步骤。

实现代码 #

WITH skip_blocks AS (
    -- 识别连续相同auto_skip标记的步骤块，记录块的起始、结束步骤，以及块结束后的第一个步骤
    SELECT
        block_start,
        block_end,
        (SELECT MIN(step) FROM STEPS WHERE step > block_end) AS next_step_after_block
    FROM STEPS
    MATCH_RECOGNIZE (
        ORDER BY step
        MEASURES
            FIRST(step) AS block_start,
            LAST(step) AS block_end
        PATTERN (same_skip+)
        DEFINE
            same_skip AS auto_skip IS NOT NULL AND (auto_skip = PREV(auto_skip) OR PREV(auto_skip) IS NULL)
    )
),
base_relations AS (
    -- 生成默认父子关系：每个步骤指向顺序下一个步骤
    SELECT
        step AS child,
        LEAD(step) OVER (ORDER BY step) AS parent
    FROM STEPS
),
extra_relations AS (
    -- 第一部分：标记块的前一个步骤，直接指向块结束后的第一个步骤
    SELECT
        (SELECT MAX(step) FROM STEPS WHERE step < sb.block_start) AS child,
        sb.next_step_after_block AS parent
    FROM skip_blocks sb
    WHERE EXISTS (SELECT 1 FROM STEPS WHERE step < sb.block_start)
    UNION ALL
    -- 第二部分：标记块的最后一个步骤，直接指向后续第一个非同标记的步骤
    SELECT
        sb.block_end AS child,
        COALESCE(sb2.next_step_after_block, sb.next_step_after_block) AS parent
    FROM skip_blocks sb
    LEFT JOIN skip_blocks sb2 ON sb.next_step_after_block = sb2.block_start
    WHERE COALESCE(sb2.next_step_after_block, sb.next_step_after_block) != LEAD(sb.block_end) OVER (ORDER BY sb.block_end)
)
-- 合并基础关系与额外关系，去重后排序
SELECT * FROM base_relations
UNION
SELECT * FROM extra_relations
ORDER BY child, parent NULLS LAST;

验证示例 #

示例1测试

原STEPS表：

执行结果：

示例2测试

原STEPS表：

执行结果：

逻辑说明 #

1. MATCH_RECOGNIZE块识别：精准捕获连续相同auto_skip标记的步骤组，为跳过逻辑提供基础；
2. 基础关系生成：通过LEAD函数快速生成默认的顺序父子关系；
3. 额外关系生成：分为两部分，一是给标记块的前序步骤添加跳过整个块的关系，二是给标记块的最后一步添加跳过后续同类型标记块的关系，完全匹配自动跳过规则。

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