Oracle中如何通过递归关联嵌套行，为关联行生成统一KEY？

Got it, this sounds like a classic connected components problem—you're trying to group together all IDs linked through shared PARAM values, even indirectly. Simple GROUP BY or MIN()/MAX() won’t cut it here because the relationships are transitive (A connects to B, B connects to C, so A, B, C all belong to the same group). Let’s work through an Oracle SQL solution tailored to this need.

First, let’s define a concrete sample dataset matching your description (since you mentioned nested associations):

WITH sample_data AS (
    SELECT 1 AS ID, 'A' AS PARAM FROM DUAL UNION ALL
    SELECT 2 AS ID, 'A' AS PARAM FROM DUAL UNION ALL
    SELECT 2 AS ID, 'D' AS PARAM FROM DUAL UNION ALL
    SELECT 3 AS ID, 'D' AS PARAM FROM DUAL UNION ALL
    SELECT 4 AS ID, 'D' AS PARAM FROM DUAL UNION ALL
    SELECT 5 AS ID, 'B' AS PARAM FROM DUAL UNION ALL
    SELECT 6 AS ID, 'B' AS PARAM FROM DUAL UNION ALL
    SELECT 6 AS ID, 'C' AS PARAM FROM DUAL UNION ALL
    SELECT 7 AS ID, 'C' AS PARAM FROM DUAL
)

The expected result should have IDs 1-4 sharing one KEY, and IDs 5-7 sharing another.

Solution 1: Recursive CTE (Union-Find Approach) #

This method efficiently traces all connected IDs and assigns a consistent KEY (we’ll use the smallest ID in each connected group as the KEY for uniformity):

WITH sample_data AS (
    SELECT 1 AS ID, 'A' AS PARAM FROM DUAL UNION ALL
    SELECT 2 AS ID, 'A' AS PARAM FROM DUAL UNION ALL
    SELECT 2 AS ID, 'D' AS PARAM FROM DUAL UNION ALL
    SELECT 3 AS ID, 'D' AS PARAM FROM DUAL UNION ALL
    SELECT 4 AS ID, 'D' AS PARAM FROM DUAL UNION ALL
    SELECT 5 AS ID, 'B' AS PARAM FROM DUAL UNION ALL
    SELECT 6 AS ID, 'B' AS PARAM FROM DUAL UNION ALL
    SELECT 6 AS ID, 'C' AS PARAM FROM DUAL UNION ALL
    SELECT 7 AS ID, 'C' AS PARAM FROM DUAL
),
-- Map each PARAM to all IDs it's associated with
param_id_mappings AS (
    SELECT PARAM, COLLECT(ID) AS linked_ids FROM sample_data GROUP BY PARAM
),
-- Recursive CTE to traverse all connected IDs
connected_groups (root_id, current_id, visited_ids) AS (
    -- Anchor: Start with each unique ID as its own root
    SELECT 
        DISTINCT ID AS root_id, 
        ID AS current_id, 
        CAST(ID AS VARCHAR2(2000)) AS visited_ids
    FROM sample_data
    UNION ALL
    -- Recursive step: Find new IDs linked via shared PARAMs that haven't been visited
    SELECT 
        cg.root_id, 
        sd.ID, 
        cg.visited_ids || ',' || sd.ID
    FROM connected_groups cg
    JOIN sample_data sd 
        ON sd.PARAM IN (SELECT PARAM FROM sample_data WHERE ID = cg.current_id)
    WHERE 
        -- Avoid revisiting IDs to prevent cycles
        INSTR(cg.visited_ids, ',' || sd.ID || ',') = 0
        AND INSTR(cg.visited_ids, sd.ID || ',') = 0
        AND INSTR(cg.visited_ids, ',' || sd.ID) = 0
),
-- Assign the smallest root ID as the group KEY for each ID
group_keys AS (
    SELECT current_id AS ID, MIN(root_id) AS KEY
    FROM connected_groups
    GROUP BY current_id
)
-- Join back to original data to apply the KEY to every row
SELECT sd.ID, sd.PARAM, gk.KEY
FROM sample_data sd
JOIN group_keys gk ON sd.ID = gk.ID
ORDER BY sd.ID, sd.PARAM;

How This Works:

1. param_id_mappings: Groups IDs by their PARAM to quickly see which IDs share a parameter.
2. connected_groups: Uses recursion to traverse all IDs linked through shared PARAMs, tracking visited IDs to avoid loops.
3. group_keys: For each ID, we take the smallest root ID from all possible traversal paths—this ensures a consistent KEY across the entire connected group.
4. Final join: Attaches the KEY to every row in your original dataset.

Solution 2: Hierarchical Query (Simpler for Smaller Datasets) #

If your dataset isn’t massive, you can use Oracle’s CONNECT_BY_ROOT to achieve the same result:

WITH sample_data AS (
    -- Same sample data as above
),
-- Generate all direct ID links via shared PARAMs
direct_links AS (
    SELECT DISTINCT a.ID AS id1, b.ID AS id2
    FROM sample_data a
    JOIN sample_data b ON a.PARAM = b.PARAM AND a.ID != b.ID
),
-- Hierarchical query to find the root ID for each connected ID
hierarchy AS (
    SELECT id1 AS ID, CONNECT_BY_ROOT(id1) AS root_id
    FROM direct_links
    CONNECT BY NOCYCLE PRIOR id2 = id1
    UNION ALL
    -- Add IDs with no links (if any)
    SELECT ID, ID AS root_id
    FROM (SELECT DISTINCT ID FROM sample_data)
    WHERE ID NOT IN (SELECT id1 FROM direct_links)
),
group_keys AS (
    SELECT ID, MIN(root_id) AS KEY
    FROM hierarchy
    GROUP BY ID
)
SELECT sd.ID, sd.PARAM, gk.KEY
FROM sample_data sd
JOIN group_keys gk ON sd.ID = gk.ID
ORDER BY sd.ID, sd.PARAM;

Key Notes:

• The NOCYCLE keyword prevents infinite loops if your data has cycles (e.g., ID1 linked to ID2, ID2 linked back to ID1).
• Using MIN(root_id) guarantees that every ID in the same group gets the same, consistent KEY.

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