Oracle SQL实现无放回式分区最小索引行匹配查询

需求：从表中选取满足以下条件的行：

• 按ID1分区，优先选择分区内index值最小的行
• 选中行的ID2不能被之前选中的任何行占用，实现类似无放回的抽取机制

示例数据与预期结果 #

示例表 #

ID1   ID2    index
foo   qux    1
foo   quux   2
foo   corge  3
bar   qux    4
bar   quux   5
bar   corge  6
baz   quux   7
baz   corge  8

预期结果 #

ID1   ID2   index
foo   qux    1
bar   quux   5
baz   corge  8 

补充测试数据 #

ID1   ID2    index
a1    b1     1
a1    b2     2
a2    b3     3
a4    b4     4

补充测试预期结果 #

ID1   ID2   index
a1    b1     1
a2    b3     3
a4    b4     4

解决方案：递归CTE实现 #

由于需要动态跟踪已占用的ID2集合，依赖之前的抽取结果进行后续筛选，使用Oracle的递归CTE（Common Table Expression）是最适合的方案：

WITH recursive_selection AS (
    -- 初始步骤：选取全局第一个符合条件的行
    SELECT 
        td.ID1,
        td.ID2,
        td."INDEX",
        CAST(td.ID2 AS VARCHAR2(4000)) AS used_id2s
    FROM (
        -- 先找出每个ID1分区内index最小的行
        SELECT 
            ID1,
            ID2,
            "INDEX",
            ROW_NUMBER() OVER (PARTITION BY ID1 ORDER BY "INDEX" ASC) AS rn
        FROM test_data
    ) td
    WHERE rn = 1
    -- 选全局最小index的行作为起始
    ORDER BY "INDEX" ASC
    FETCH FIRST 1 ROW ONLY

    UNION ALL

    -- 递归步骤：逐步选取剩余分区的符合条件行
    SELECT 
        next_row.ID1,
        next_row.ID2,
        next_row."INDEX",
        rs.used_id2s || ',' || next_row.ID2 AS used_id2s
    FROM recursive_selection rs
    CROSS JOIN LATERAL (
        -- 筛选未处理的ID1分区，且ID2未被占用的行
        SELECT 
            td.ID1,
            td.ID2,
            td."INDEX"
        FROM (
            SELECT 
                td.ID1,
                td.ID2,
                td."INDEX",
                ROW_NUMBER() OVER (PARTITION BY td.ID1 ORDER BY td."INDEX" ASC) AS rn
            FROM test_data td
            -- 排除已经处理过的ID1分区
            WHERE td.ID1 NOT IN (SELECT ID1 FROM recursive_selection)
            -- 排除已被选中的ID2
            AND td.ID2 NOT IN (
                SELECT REGEXP_SUBSTR(rs.used_id2s, '[^,]+', 1, LEVEL) 
                FROM dual 
                CONNECT BY LEVEL <= REGEXP_COUNT(rs.used_id2s, ',') + 1
            )
        ) td
        WHERE rn = 1
        -- 选当前候选中最小index的行
        ORDER BY "INDEX" ASC
        FETCH FIRST 1 ROW ONLY
    ) next_row
)
-- 输出最终结果
SELECT ID1, ID2, "INDEX" 
FROM recursive_selection 
ORDER BY "INDEX";

方案说明 #

1. 初始步骤：先为每个ID1分区筛选出index最小的行，再从中选取全局index最小的行作为第一个抽取结果，同时记录已占用的ID2。
2. 递归步骤：每次从尚未处理的ID1分区中，筛选出ID2未被占用的行，每个分区保留index最小的行，再从中选取全局index最小的行加入结果集，并更新已占用的ID2集合。
3. 终止条件：当没有符合条件的行可以选取时，递归自动停止。

注意事项 #

• 如果ID2的数量较多，可将used_id2s的类型调整为CLOB以容纳更长的字符串。
• 若某个ID1分区内所有ID2都已被占用，该分区会被跳过，不会出现在结果中。

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