Snowflake SQL：基于eid/phone/email关联记录生成分组ID

需求：将test_data表中eid、phone或email任一字段匹配的记录归为同一组，生成唯一且连续的group_id（最终应得到3个唯一分组ID）。

解决方案：递归CTE + 密度排名 #

通过递归CTE识别所有关联的记录（连通分量），再使用DENSE_RANK()生成连续分组ID：

WITH connected_records AS (
    -- 锚点：初始记录，以自身UUID作为分组根节点
    SELECT 
        uuid, 
        eid, 
        phone, 
        email,
        uuid AS group_root
    FROM test_data
    WHERE NOT (eid IS NULL AND phone IS NULL AND email IS NULL)
    
    UNION ALL
    
    -- 递归：关联所有匹配eid/phone/email的记录，继承根节点
    SELECT 
        td.uuid, 
        td.eid, 
        td.phone, 
        td.email,
        cr.group_root
    FROM test_data td
    JOIN connected_records cr
        ON (td.eid = cr.eid AND td.eid IS NOT NULL)
        OR (td.phone = cr.phone AND td.phone IS NOT NULL)
        OR (td.email = cr.email AND td.email IS NOT NULL)
    WHERE td.uuid NOT IN (SELECT uuid FROM connected_records)
),
-- 去重并统一每个记录的根节点
distinct_groups AS (
    SELECT DISTINCT
        uuid,
        MIN(group_root) OVER (PARTITION BY uuid) AS final_root
    FROM connected_records
)
-- 生成连续的group_id
SELECT 
    td.*,
    DENSE_RANK() OVER (ORDER BY dg.final_root) AS group_id
FROM test_data td
LEFT JOIN distinct_groups dg ON td.uuid = dg.uuid
ORDER BY group_id, create_ts;

代码说明 #

1. 递归CTE connected_records：

   • 锚点成员：选取所有非全空字段的记录，以自身UUID作为分组根节点
   • 递归成员：通过eid/phone/email匹配规则，将关联记录并入同一分组，继承根节点
   • 过滤条件td.uuid NOT IN (...)避免重复处理已加入分组的记录

2. distinct_groups 去重：

   • 确保每个记录只保留唯一的根节点（处理递归过程中可能出现的重复根节点）

3. 生成连续group_id：

   • 使用DENSE_RANK()对根节点排序，生成连续的分组ID，满足“唯一且连续”的要求

预期结果 #

最终会生成3个group_id，对应三组关联记录：

• group_id 1：包含eid=1、eid=2的所有记录（通过phone 555-555-5555关联）
• group_id 2：包含eid=3、eid=4的所有记录（通过email a@email.com关联）
• group_id 3：包含eid=5、eid=6的所有记录（通过phone 888-888-8888和email h@email.com关联）

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