基于手机号/邮箱/地址匹配为客户分配household_id的去重问题

问题本质 #

这是典型的图连通分量分组问题：把每个客户视作图的节点，两个客户只要phone、email、address任意字段匹配就视作节点间有边相连，所有连通的节点需要被划分到同一个household中。
你原有逻辑的缺陷在于仅处理了两两配对的去重场景，当3个及以上客户连通时（比如A匹配B、B匹配C、A匹配C），会出现多个parent_id都关联同个子客户的情况，自然会产生重复分组。

最优实现方案（递归CTE） #

绝大多数主流数据库（MySQL 8.0+、PostgreSQL、BigQuery、Snowflake等）都支持递归CTE，可以直接计算每个客户所属连通分量的最小ID，用这个最小ID生成household_id即可完全避免重复问题，代码如下：

WITH RECURSIVE match_edges AS (
  -- 第一步：提取所有去重的匹配客户对，统一保留小ID在前、大ID在后，避免双向重复边
  SELECT DISTINCT
    LEAST(c1.id, c2.id) AS low_id,
    GREATEST(c1.id, c2.id) AS high_id
  FROM customer c1
  INNER JOIN customer c2 
    ON c1.id != c2.id 
    AND (c1.phone = c2.phone OR c1.email = c2.email OR c1.address = c2.address)
),
connected_components AS (
  -- 递归初始态：每个客户的初始根节点为自身ID
  SELECT id AS customer_id, id AS root_id
  FROM customer
  UNION ALL
  -- 递归迭代：更新连通节点的根节点为整个连通分量的最小ID
  SELECT e.high_id AS customer_id, c.root_id
  FROM connected_components c
  INNER JOIN match_edges e 
    ON c.customer_id = e.low_id 
    AND e.high_id > c.root_id
)
-- 每个客户取最小的根ID，生成最终的household分组
SELECT 
  CONCAT('H-', MIN(root_id)) AS household_id,
  customer_id
FROM connected_components
GROUP BY customer_id
ORDER BY household_id, customer_id;

方案说明 #

1. 不需要额外的窗口函数或者去重逻辑，整个连通分量的根ID全局唯一，天然不会出现重复分组
2. 支持任意数量的客户连通场景，不管是2个还是10个客户匹配，都能正确归到同一个household下
3. 如果客户表量级较大（百万级以上），可以提前给phone、email、address字段加索引，大幅降低关联匹配的耗时

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