如何用SQL基于邮箱识别同一客户并统一其邮箱字段？

核心思路 #

这是典型的连通分量识别问题——把共享任意邮箱的行归为同一连通分量（即同一客户），再将分量内所有行的邮箱字段替换为该分量的全部邮箱集合。

分步实现（以SQL为例，兼容多数关系型数据库） #

步骤1：拆分邮箱字段为行级数据 #

先给原表生成唯一行ID，再把每行的email1-email5拆成单独行，建立邮箱与原行的关联：

WITH unpivoted_emails AS (
    SELECT 
        row_id,
        email
    FROM (
        SELECT 
            ROW_NUMBER() OVER () AS row_id,
            email1, email2, email3, email4, email5, date
        FROM input_table
    ) t
    UNPIVOT (
        email FOR email_col IN (email1, email2, email3, email4, email5)
    ) up
    WHERE email IS NOT NULL -- 过滤空邮箱
),

步骤2：识别同一客户的连通分量 #

用递归CTE找出所有关联的邮箱，给每个客户分配唯一标识：

connected_components AS (
    -- 初始化：每个邮箱作为单独节点
    SELECT 
        email AS root_email,
        email
    FROM unpivoted_emails
    UNION ALL
    -- 递归：关联所有共享同一行的邮箱
    SELECT 
        cc.root_email,
        ue2.email
    FROM connected_components cc
    JOIN unpivoted_emails ue ON cc.email = ue.email
    JOIN unpivoted_emails ue2 ON ue.row_id = ue2.row_id
    WHERE cc.root_email <> ue2.email
),
-- 去重并确定每个邮箱对应的客户ID
customer_mapping AS (
    SELECT 
        email,
        MIN(root_email) AS customer_id -- 用最小邮箱作为客户唯一标识
    FROM connected_components
    GROUP BY email
)

步骤3：聚合邮箱并回写原表 #

把每个客户的所有邮箱聚合成列表，再关联回原表，保持输出行数与输入一致：

SELECT 
    t.row_id,
    STRING_AGG(DISTINCT cm_all.email, ', ') AS all_customer_emails, -- 聚合该客户所有邮箱
    t.date
FROM (
    SELECT 
        ROW_NUMBER() OVER () AS row_id,
        email1, email2, email3, email4, email5, date
    FROM input_table
) t
JOIN unpivoted_emails ue ON t.row_id = ue.row_id
JOIN customer_mapping cm ON ue.email = cm.email
JOIN customer_mapping cm_all ON cm.customer_id = cm_all.customer_id
GROUP BY t.row_id, t.date
ORDER BY t.row_id;

其他工具实现思路 #

• Python（Pandas + NetworkX）：把邮箱作为节点，共享同一行的邮箱之间连边，用NetworkX的connected_components()找出连通分量，再映射回原表。
• Spark：用GraphFrames库处理图结构，识别连通分量后关联原表，适合大数据场景。

注意：原表必须先生成唯一行ID（自增列或哈希组合字段均可），确保后续关联准确。

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