如何在SQL中为两列关联形成的连通行组分配唯一ID

实现方案 #

这个需求本质是计算无向图的连通分量：你可以把ID1、ID2的所有取值看作图的节点，每行ID1和ID2的对应关系看作连接两个节点的边，所有连通的节点对应的行都会被分配到同一个组。

以下是支持递归CTE的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Hive 2.1+等）通用的实现代码，示例中你的源表名为id_relation，包含id1、id2两个字段：

WITH RECURSIVE edges AS (
    -- 构建双向边，支持双向遍历关联关系
    SELECT id1 AS node, id2 AS neighbor FROM id_relation
    UNION ALL
    SELECT id2 AS node, id1 AS neighbor FROM id_relation
),
connected_components AS (
    -- 初始节点，每个节点的初始根节点为自身
    SELECT DISTINCT node, node AS root FROM edges
    UNION ALL
    -- 递归更新根节点，取更小的节点作为统一根，避免无限递归
    SELECT e.node, cc.root
    FROM edges e
    INNER JOIN connected_components cc 
        ON e.neighbor = cc.node
    WHERE cc.root < e.root
),
-- 去重得到每个节点对应的最终根节点
final_component AS (
    SELECT node, MIN(root) AS group_id
    FROM connected_components
    GROUP BY node
)
-- 关联回原表得到每行的分组ID
SELECT 
    t.*,
    f.group_id,
    -- 如果需要连续的数字类型分组ID，可以加上下面这行
    DENSE_RANK() OVER(ORDER BY f.group_id) AS numeric_group_id
FROM id_relation t
INNER JOIN final_component f 
    ON t.id1 = f.node;

性能优化说明 #

• 万级以下小数据量：上面的递归CTE方案足够高效，执行耗时一般在秒级。
• 十万级以上大数据量：
  • Spark SQL环境可以直接调用GraphX内置的connectedComponents算子，性能比纯SQL高一个数量级。
  • PostgreSQL可以使用图扩展插件优化递归查询，也可以预先拆分关联链减少递归深度。

注意事项 #

• 若存在超长关联链，需要提前调整数据库递归深度参数：MySQL执行SET cte_max_recursion_depth = 你的最大链长度;，PostgreSQL执行SET max_recursion_depth = 你的最大链长度;。
• 若ID1和ID2存在取值重复的情况，上述代码也可正常兼容，不需要额外处理。

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