多数据源关联生成唯一Master ID的SQL分组异常问题

你的问题本质是连通分量识别：部分记录通过多个join condition形成关联网络，直接按单个join condition分组会割裂本应同属一组的数据。以下是两种可行的解决方法：

问题示例（整理后） #

方法1：SQL递归CTE（适用于数据库端处理） #

利用递归CTE遍历所有关联节点，识别连通分量，再基于分量生成统一的master_id：

-- 假设原表名为your_table
WITH all_links AS (
    -- 建立System1_id与join condition的关联
    SELECT System1_id AS node1, join_condition AS node2
    FROM your_table
    WHERE System1_id IS NOT NULL AND System1_id != '-'
    UNION ALL
    -- 建立System2_id与join condition的关联
    SELECT System2_id AS node1, join_condition AS node2
    FROM your_table
    WHERE System2_id IS NOT NULL AND System2_id != '-'
),
recursive_components AS (
    -- 初始化递归：每个节点作为自身的根
    SELECT node1 AS root, node1 AS node
    FROM all_links
    UNION ALL
    -- 递归遍历：从当前节点关联到其他节点
    SELECT rc.root, al.node2 AS node
    FROM recursive_components rc
    JOIN all_links al ON rc.node = al.node1
    WHERE al.node2 NOT IN (SELECT node FROM recursive_components WHERE root = rc.root)
    UNION ALL
    SELECT rc.root, al.node1 AS node
    FROM recursive_components rc
    JOIN all_links al ON rc.node = al.node2
    WHERE al.node1 NOT IN (SELECT node FROM recursive_components WHERE root = rc.root)
),
component_groups AS (
    -- 给每个节点分配所属分量的唯一标识（取分量中最小的节点值作为根）
    SELECT node, MIN(root) AS component_id
    FROM recursive_components
    GROUP BY node
),
master_id_mapping AS (
    -- 收集每个分量下的所有System1/System2 ID，去重后拼接
    SELECT 
        cg.component_id,
        STRING_AGG(DISTINCT t.System1_id, '') AS sys1_ids,
        STRING_AGG(DISTINCT t.System2_id, '') AS sys2_ids
    FROM your_table t
    LEFT JOIN component_groups cg ON t.System1_id = cg.node
    LEFT JOIN component_groups cg2 ON t.System2_id = cg2.node
    GROUP BY COALESCE(cg.component_id, cg2.component_id)
)
-- 最终关联生成理想的master_id
SELECT 
    t.*,
    CONCAT(m.sys1_ids, m.sys2_ids) AS ideal_master_id
FROM your_table t
LEFT JOIN component_groups cg ON t.System1_id = cg.node
LEFT JOIN component_groups cg2 ON t.System2_id = cg2.node
LEFT JOIN master_id_mapping m ON COALESCE(cg.component_id, cg2.component_id) = m.component_id;

逻辑说明 #

1. all_links：将系统ID与对应的join condition转化为节点间的关联边。
2. recursive_components：递归遍历所有连通的节点，构建每个节点的连通路径。
3. component_groups：给每个节点标记所属的连通分量ID，确保同一分量的节点共享相同ID。
4. master_id_mapping：收集每个分量下的所有系统ID，拼接成唯一的master_id。

方法2：Python NetworkX图算法（适用于脚本/ETL场景） #

用图结构来建模所有关联关系，找出连通分量后生成master_id：

import networkx as nx
import pandas as pd

# 加载你的数据（替换为实际数据读取逻辑）
df = pd.DataFrame([
    ["item_1", "abc", "-", "a_unique_id", "abc123", "abcxyz123"],
    ["item_2", "-", "123", "a_unique_id", "abc123", "abcxyz123"],
    ["item_1", "abc", "-", "another_unique_id", "abc987", "abcxyz123"],
    ["item_3", "-", "987", "another_unique_id", "abc987", "abcxyz123"]
], columns=["Item", "System1_id", "System2_id", "join_condition", "master_id", "ideal_master_id"])

# 初始化无向图
G = nx.Graph()

# 为每条记录添加关联边
for _, row in df.iterrows():
    sys1_id = row["System1_id"]
    sys2_id = row["System2_id"]
    join_key = row["join_condition"]
    
    if sys1_id != "-":
        G.add_edge(sys1_id, join_key)
    if sys2_id != "-":
        G.add_edge(sys2_id, join_key)

# 获取所有连通分量
connected_components = list(nx.connected_components(G))

# 构建分量到master_id的映射
component_to_master = {}
for comp in connected_components:
    # 提取分量中的所有系统ID（排除join condition）
    system_ids = [node for node in comp if node not in df["join_condition"].unique()]
    # 去重并排序保证master_id的一致性
    sorted_ids = sorted(list(set(system_ids)))
    master_id = "".join(sorted_ids)
    # 分量内的所有节点都映射到这个master_id
    for node in comp:
        component_to_master[node] = master_id

# 为每条记录分配master_id
df["generated_master_id"] = df.apply(
    lambda row: component_to_master.get(row["System1_id"], component_to_master.get(row["System2_id"], row["System1_id"] + row["System2_id"])),
    axis=1
)

print(df)

逻辑说明 #

1. 把系统ID和join condition作为图的节点，每条记录中的关联关系作为边，构建整个关联网络。
2. 用connected_components找出所有相互连通的节点集合，每个集合对应一组重复数据。
3. 收集集合内的所有系统ID，拼接成唯一的master_id，再映射回每条记录。

核心思路总结 #

• 不要局限于单个join condition分组，要从关联网络的角度识别重复数据：只要两个记录通过任意路径（共享ID/join condition）关联，就属于同一组。
• 无论是SQL递归还是图算法，本质都是识别这些连通的节点集合，再基于集合生成统一的master_id。

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