SQL Server中基于多规则识别相似客户记录并生成唯一ID

前置说明 #

我们先对规则中的「相似」做业务落地，你可以根据实际需求调整匹配逻辑：

• 完全匹配规则：first_name、last_name、email、mobile四个字段值完全相等
• 相似匹配规则：非空字段匹配即判定为相似，空值不做强制校验：
  • 规则2：非空的last_name相等、非空的mobile相等、非空的email相等
  • 规则3：非空的first_name相等、非空的last_name相等、非空的email相等
    如果需要实现拼写纠错类的模糊相似，可以替换为SOUNDEX()、DIFFERENCE()或自定义编辑距离函数判断。

实现代码 #

步骤1：计算连通分量（所有相似记录归为同一组） #

用递归CTE处理分组的传递依赖（比如A匹配B、B匹配C，则A/B/C归为同一组）：

WITH matched_pairs AS (
    -- 找出所有符合匹配规则的记录对
    SELECT 
        t1.customer_id AS id1,
        t2.customer_id AS id2
    FROM dbo.customer t1
    JOIN dbo.customer t2 ON t1.customer_id < t2.customer_id
    WHERE 
        -- 规则1：四个字段完全相同
        (t1.first_name = t2.first_name AND t1.last_name = t2.last_name AND t1.email = t2.email AND t1.mobile = t2.mobile)
        -- 规则2：姓氏、手机号、邮箱相似
        OR (
            (t1.last_name = t2.last_name OR t1.last_name = '' OR t2.last_name = '')
            AND (t1.mobile = t2.mobile OR t1.mobile = '' OR t2.mobile = '')
            AND (t1.email = t2.email OR t1.email = '' OR t2.email = '')
        )
        -- 规则3：名字、姓氏、邮箱相似
        OR (
            (t1.first_name = t2.first_name OR t1.first_name = '' OR t2.first_name = '')
            AND (t1.last_name = t2.last_name OR t1.last_name = '' OR t2.last_name = '')
            AND (t1.email = t2.email OR t1.email = '' OR t2.email = '')
        )
),
recursive_groups AS (
    -- 初始每个记录自身为一个组，组ID取最小的customer_id
    SELECT 
        customer_id AS root_id,
        customer_id
    FROM dbo.customer
    UNION ALL
    -- 递归合并关联的记录
    SELECT 
        rg.root_id,
        mp.id2 AS customer_id
    FROM recursive_groups rg
    JOIN matched_pairs mp ON rg.customer_id = mp.id1
    WHERE mp.id2 NOT IN (SELECT customer_id FROM recursive_groups WHERE root_id = rg.root_id)
),
-- 给每个记录分配最小的root_id作为分组标识
final_groups AS (
    SELECT 
        customer_id,
        MIN(root_id) AS unique_id
    FROM recursive_groups
    GROUP BY customer_id
)
-- 回写unique_id到原表
UPDATE c
SET c.unique_id = fg.unique_id
FROM dbo.customer c
JOIN final_groups fg ON c.customer_id = fg.customer_id

步骤2：验证结果 #

执行完后查询原表即可看到分组结果：

SELECT * FROM dbo.customer ORDER BY unique_id, customer_id

效果说明 #

针对你提供的测试数据，最终分组结果和预期效果一致：

• 所有email为john.smith@fakeemail.com、mobile为123456的记录分到同一组
• 所有email为john.smith@fakeemail.com、mobile为987654的记录分到同一组
• 所有email为bill@microsoft.com的记录分到同一组

可调优点 #

1. 数据量大于百万级时，可以先对email、last_name字段建索引，减少匹配时的全表扫描开销
2. 如果需要调整相似判定逻辑，只需要修改matched_pairs CTE里的WHERE条件即可，比如增加手机号去掉前缀+86后匹配、邮箱前缀模糊匹配等规则

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