如何在SQL中实现Item的递归分组（可扩展方案）

现有表结构与数据 #

创建表及插入数据的SQL如下：

CREATE OR REPLACE test.mytable (item STRING(1), I_groupe STRING(1));

INSERT INTO test.mytable (item, I_groupe)
values
('A', '1'),
('B', '1'),
('B', '2'),
('C', '2'),
('D', '3');

对应数据表格：

需求说明 #

需要将Item按共享Intermediate_group递归分组：

• 若两个Item共享同一中间分组，或通过其他Item间接共享中间分组，则归为同一最终组
• 预期结果：

逻辑解释：A与B共享分组1，B与C共享分组2，因此A、B、C属于同一最终组；D无关联的其他Item，单独成组。

当前代码的问题 #

现有手动编写的SQL需要重复执行多次才能得到最终结果，无法应对数据量增大或关联层级变多的真实场景，缺乏扩展性。

递归SQL实现自动分组 #

可以利用**递归CTE（WITH RECURSIVE）**自动处理这种连通分量分组问题，无需手动重复执行。核心思路是将Item和中间分组视为图中的节点，通过关联关系建立边，递归遍历所有连通节点，最终聚合同一连通分量的Item：

WITH RECURSIVE
-- 建立item和中间分组的双向映射，将两者视为同一连通图的节点
nodes AS (
  SELECT item AS node, I_groupe AS link FROM test.mytable
  UNION ALL
  SELECT I_groupe AS node, item AS link FROM test.mytable
),
-- 递归遍历所有连通节点，标记每个节点所属的根组（用最小节点值作为组标识）
connected_components AS (
  SELECT 
    node AS original_node,
    node AS root_node,
    ARRAY[node] AS visited_nodes
  FROM nodes
  WHERE node REGEXP '^[A-Z]$' -- 仅从Item节点开始遍历
  UNION ALL
  SELECT
    cc.original_node,
    LEAST(cc.root_node, n.link) AS root_node, -- 用最小节点保证组标识唯一
    ARRAY_CONCAT(cc.visited_nodes, [n.link]) AS visited_nodes
  FROM connected_components cc
  JOIN nodes n ON cc.node = n.node
  WHERE NOT n.link IN UNNEST(cc.visited_nodes)
),
-- 去重，保留每个Item对应的根组信息
item_groups AS (
  SELECT DISTINCT
    original_node AS item,
    root_node AS group_id
  FROM connected_components
  WHERE original_node REGEXP '^[A-Z]$'
),
-- 聚合同一根组下的所有Item，生成连续的最终组编号
final_groups AS (
  SELECT
    STRING_AGG(DISTINCT item ORDER BY item) AS item,
    DENSE_RANK() OVER (ORDER BY MIN(group_id)) AS Final_group
  FROM item_groups
  GROUP BY group_id
)
SELECT * FROM final_groups;

代码逻辑说明 #

1. nodes CTE：构建Item和中间分组的双向关联，形成图的双向边，确保能通过中间分组关联所有相关Item。
2. connected_components CTE：递归遍历每个Item节点，找到所有连通节点（含中间分组），用最小节点值标记根组，保证组标识唯一。
3. item_groups CTE：去重后提取每个Item对应的根组信息。
4. final_groups CTE：按根组聚合Item，用DENSE_RANK()生成连续的最终组编号，匹配预期结果格式。

该方案可自动处理任意层级的关联关系，无需手动重复执行，完全支持业务扩展。

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