如何在Snowflake中关联user_id与逗号分隔列表并筛选双向参与记录

解决方案思路 #

要实现需求，核心步骤是拆分逗号分隔字段、建立用户关联对、筛选双向存在的配对，最终输出单行记录。以下是针对单表场景的完整实现（若涉及两表，只需调整关联逻辑）：

完整SQL代码 #

假设你的表名为 user_participations，包含字段 user_id（主用户ID）和 participant_user_id_list（逗号分隔的参与用户ID字符串）：

-- 1. 拆分逗号分隔的参与用户ID为独立行
WITH split_participants AS (
    SELECT
        up.user_id AS main_user_id,
        -- 转换为对应数据类型，这里假设是INT，若为字符串则用::VARCHAR
        sp.value::INT AS participant_user_id
    FROM user_participations up,
         TABLE(SPLIT_TO_TABLE(up.participant_user_id_list, ',')) sp
),
-- 2. 整理所有有效的单向用户关联对
all_pairs AS (
    SELECT main_user_id, participant_user_id
    FROM split_participants
    WHERE participant_user_id IS NOT NULL -- 过滤拆分后产生的空值
),
-- 3. 筛选双向存在的关联对（a→b 且 b→a 同时存在）
mutual_pairs AS (
    SELECT a.main_user_id, a.participant_user_id
    FROM all_pairs a
    JOIN all_pairs b
        ON a.main_user_id = b.participant_user_id
        AND a.participant_user_id = b.main_user_id
)
-- 4. 输出最终结果，每个双向关联对单独一行
SELECT main_user_id, participant_user_id
FROM mutual_pairs;

关键步骤解释 #

• 拆分字段：使用Snowflake内置的SPLIT_TO_TABLE函数，将逗号分隔的字符串拆分为多行记录，把每个参与用户ID和主用户ID一一对应。
• 过滤无效数据：在all_pairs中过滤拆分后可能出现的空值，避免无效关联。
• 筛选双向配对：通过自连接all_pairs表，匹配满足a是b的参与用户且b是a的参与用户的记录，得到双向参与的账户对。

扩展说明 #

1. 两表关联场景：如果主用户ID来自表A，参与用户列表来自表B，只需调整split_participants中的关联逻辑，补充两表的关联条件（比如A.user_id = B.related_id）。
2. 避免重复配对：若不需要同时显示111-222和222-111，可在mutual_pairs的SELECT语句中添加条件a.main_user_id < a.participant_user_id，仅保留每组双向配对的一条记录。
3. 数据类型适配：如果用户ID是字符串类型，将::INT改为::VARCHAR即可。

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