Amazon Redshift SQL实现关联ID行合并与去重

这本质是**连通分量（Connected Components）**问题——把所有存在交集的ID组合并成一个完整集合，同时去重。以下是基于Redshift递归CTE的实现方案：

假设你的原始表结构 #

假设表名为id_groups，其中id_list列存储用逗号分隔的多个ID（比如"1,2,3"）；如果你的表是每行单个ID，直接跳过第一步的拆分逻辑。

完整SQL代码 #

-- 1. 拆分多行ID为单个ID行
WITH split_ids AS (
    SELECT 
        row_id AS original_row_id, -- 原始行唯一标识，用于关联同一行的ID
        TRIM(split_part(id_list, ',', n)) AS single_id
    FROM id_groups,
         -- 生成足够的序列数，覆盖最长ID列表的元素个数
         GENERATE_SERIES(1, (SELECT MAX(LENGTH(id_list) - LENGTH(REPLACE(id_list, ',', '')) + 1) FROM id_groups)) n
    WHERE TRIM(split_part(id_list, ',', n)) <> '' -- 过滤空值
),
-- 2. 生成ID之间的关联对（同一行的ID两两配对）
id_pairs AS (
    SELECT 
        a.single_id AS id1,
        b.single_id AS id2
    FROM split_ids a
    JOIN split_ids b 
        ON a.original_row_id = b.original_row_id 
        AND a.single_id < b.single_id -- 避免重复配对
),
-- 3. 递归遍历所有关联ID，找出连通分量
recursive_components AS (
    SELECT id1, id2 FROM id_pairs
    UNION ALL
    SELECT rc.id1, p.id2
    FROM recursive_components rc
    JOIN id_pairs p ON rc.id2 = p.id1
    WHERE NOT EXISTS (SELECT 1 FROM recursive_components WHERE id1 = rc.id1 AND id2 = p.id2)
),
-- 4. 为每个ID确定其所属分量的根节点（最小ID作为根）
component_roots AS (
    SELECT 
        COALESCE(rc.id1, si.single_id) AS id,
        MIN(COALESCE(rc.id2, si.single_id)) AS root_id
    FROM split_ids si
    LEFT JOIN recursive_components rc ON si.single_id = rc.id1
    GROUP BY COALESCE(rc.id1, si.single_id)
),
-- 5. 确保所有节点最终指向同一个根（处理多层关联）
final_roots AS (
    SELECT id, root_id FROM component_roots
    UNION ALL
    SELECT fr.id, cr.root_id
    FROM final_roots fr
    JOIN component_roots cr ON fr.root_id = cr.id
    WHERE fr.root_id <> cr.root_id
)
-- 6. 合并同一分量下的所有ID，去重并排序
SELECT 
    root_id AS component_identifier,
    LISTAGG(DISTINCT id, ', ') WITHIN GROUP (ORDER BY id) AS merged_associated_ids
FROM final_roots
GROUP BY root_id;

关键说明 #

• 如果你的表每行只有单个ID（比如行之间通过共享ID关联），直接把split_ids替换为SELECT id AS single_id FROM your_table即可。
• Redshift的LISTAGG默认有最大长度限制（通常为65535字符），如果合并后的ID串太长，需要调整max_length参数：LISTAGG(DISTINCT id, ', ') WITHIN GROUP (ORDER BY id) MAXLEN 1000000。
• 递归CTE的性能取决于数据量，如果数据极大，可能需要考虑分步处理或者使用Redshift的其他特性优化。

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