如何在Snowflake SQL中合并关联的家庭成员ID至同一数组？

问题场景 #

需要将所有存在关联的家庭成员合并为同一个集合：若两个家庭共享至少一个成员，则它们的所有成员要合并成一组。

基础表数据 #

当前查询结果（按family_id聚合） #

期望输出（合并关联家庭的成员） #

当前代码 #

with family1 as (
    select 111222 as member_id, 'abc' as family_id union
    select 333444 as member_id, 'abc' as family_id union
    select 111222 as member_id, 'xyz' as family_id union
    select 555666 as member_id, 'xyz' as family_id union
    select 777888 as member_id, 'def' as family_id union
    select 999000 as member_id, 'def' as family_id
)

select
   family_id
   ,array_to_string(array_agg(member_id) within group (order by member_id asc), ',') as member_list
from family1
group by family_id

解决方案 #

这个问题本质是连通分量计算：通过成员关联的家庭属于同一组，需要把这些组内的所有成员聚合。以下是两种可行的Snowflake SQL实现方式：

方法1：使用Snowflake图函数（GRAPH_TABLE） #

利用图函数快速找出所有连通的节点（成员+家庭），再按连通分量聚合成员：

with family1 as (
    select 111222 as member_id, 'abc' as family_id union
    select 333444 as member_id, 'abc' as family_id union
    select 111222 as member_id, 'xyz' as family_id union
    select 555666 as member_id, 'xyz' as family_id union
    select 777888 as member_id, 'def' as family_id union
    select 999000 as member_id, 'def' as family_id
),
-- 构建双向边：成员连接家庭，家庭连接成员
graph_edges as (
    select member_id::varchar as src, family_id as dest from family1
    union all
    select family_id as src, member_id::varchar as dest from family1
),
-- 识别每个节点所属的连通分量
connected_components as (
    select 
        distinct gt.src as node,
        gt.root_id as component_id
    from graph_table(
        graph_edges,
        'src', 'dest',
        start with member_id::varchar in (select distinct member_id::varchar from family1)
        depth first
    ) gt
)
-- 按分量聚合所有成员
select 
    array_to_string(array_agg(distinct f.member_id order by f.member_id asc), ',') as member_list
from family1 f
join connected_components cc on f.member_id::varchar = cc.node
group by cc.component_id;

方法2：使用递归CTE #

通过递归遍历找出所有关联的成员和家庭，再聚合分组：

with family1 as (
    select 111222 as member_id, 'abc' as family_id union
    select 333444 as member_id, 'abc' as family_id union
    select 111222 as member_id, 'xyz' as family_id union
    select 555666 as member_id, 'xyz' as family_id union
    select 777888 as member_id, 'def' as family_id union
    select 999000 as member_id, 'def' as family_id
),
-- 建立成员与家庭的关联映射
member_family as (
    select member_id, family_id from family1
),
-- 递归遍历所有连通的成员和家庭
recursive_cte as (
    select 
        member_id, 
        family_id,
        member_id as component_id  -- 用初始成员ID作为分组标识
    from member_family
    union all
    select 
        mf.member_id, 
        mf.family_id,
        rc.component_id
    from recursive_cte rc
    join member_family mf 
        on rc.family_id = mf.family_id or rc.member_id = mf.member_id
    where not exists (
        select 1 from recursive_cte rc2 
        where rc2.member_id = mf.member_id and rc2.family_id = mf.family_id
    )
),
-- 去重得到每个成员所属的分量
unique_components as (
    select distinct member_id, component_id from recursive_cte
)
-- 按分量聚合成员
select 
    array_to_string(array_agg(distinct member_id order by member_id asc), ',') as member_list
from unique_components
group by component_id;

两种方法都能得到期望的输出，其中GRAPH_TABLE方法在处理大数据量时效率更高。

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