基于公共中间组生成最终Item分组的SQL实现求助

你的原SQL仅完成了每个Item的中间组聚合，但未实现“共享中间组的Item归为同一最终组”的核心逻辑。下面是完善后的SQL实现，通过递归CTE构建连通分量来完成关联分组：

完整SQL实现 #

WITH RECURSIVE
-- 步骤1：拆分得到Item与单个中间组的映射（修正原SQL语法错误）
item_group_mapping AS (
  SELECT
    item,
    -- 替换为你表中存储多个中间组的实际字段名
    TRIM(unnest(split(intermediate_groups, ','))) AS intermediate_group
  FROM my_table
),
-- 步骤2：聚合每个Item的所有中间组（用于最终展示）
item_all_groups AS (
  SELECT
    item,
    STRING_AGG(intermediate_group, ', ') AS all_intermediate_groups
  FROM item_group_mapping
  GROUP BY item
),
-- 递归构建连通分量：关联所有共享中间组的Item
connected_items AS (
  -- 初始层：每个Item自身作为初始组节点
  SELECT
    item AS root_item,
    item AS member_item,
    intermediate_group
  FROM item_group_mapping

  UNION ALL

  -- 递归层：通过共享中间组，将未关联的Item并入当前组
  SELECT
    ci.root_item,
    igm.item AS member_item,
    igm.intermediate_group
  FROM connected_items ci
  JOIN item_group_mapping igm
    ON ci.intermediate_group = igm.intermediate_group
    AND ci.member_item != igm.item
  -- 避免循环，仅加入未被当前组关联过的Item
  WHERE NOT EXISTS (
    SELECT 1 FROM connected_items ci2
    WHERE ci2.root_item = ci.root_item AND ci2.member_item = igm.item
  )
),
-- 生成最终分组：为每个Item分配唯一的最终组标识
final_groups AS (
  SELECT
    member_item AS item,
    -- 用组内最小的Item作为分组唯一标识，确保同组Item标识一致
    MIN(root_item) AS final_group_id,
    -- 关联该Item的所有中间组列表
    (SELECT all_intermediate_groups FROM item_all_groups WHERE item = member_item) AS all_intermediate_groups
  FROM connected_items
  GROUP BY member_item
)
-- 最终输出结果
SELECT
  item,
  final_group_id,
  all_intermediate_groups
FROM final_groups
ORDER BY final_group_id;

关键说明 #

1. 数据拆分修正：item_group_mapping将每个Item对应的多中间组拆分为一行一个的映射关系，用TRIM处理拆分后可能存在的空格，需替换intermediate_groups为你表中的实际字段名。
2. 递归关联逻辑：connected_items通过递归，把所有共享同一中间组的Item归入同一连通集合，避免循环的条件确保不会重复处理同一Item。
3. 最终分组标识：final_groups用组内最小的ItemID作为最终分组的唯一标识，保证同一组内的Item拥有相同的分组ID，同时保留每个Item的所有中间组信息。

适配调整 #

• 如果你的my_table已经是每行一个Item对应一个中间组的结构，可以直接跳过item_group_mapping，用原表作为connected_items的初始层。
• 若需要用其他形式的分组标识（如UUID、自增ID），可在final_groups中修改final_group_id的生成逻辑。

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