基于三种标识符关联匹配生成统一客户ID的技术问询

要解决多标识符关联合并客户记录的问题，本质是找出所有连通分量（即通过任意ID关联的记录组），然后为每个分量分配唯一的新ID。以下是基于SQL Server的实现方案：

完整实现代码 #

DROP TABLE IF EXISTS #Test
CREATE TABLE #Test (PrimaryKey int, CustomerID1 varchar(15), CustomerID2 varchar(15), CustomerID3 varchar(15))
INSERT INTO #Test VALUES
     (1,'Alpha','Dog','Jeans')
    ,(2,'Alpha','Cat','Shirt')
    ,(3,'Beta','Dog','Dress')
    ,(4,'Gamma','Bear','Jeans')
    ,(5,'Alpha','Dog','Jeans')
    ,(6,'Epsilon','Bird','Boots')

-- 1. 生成所有双向关联对
DROP TABLE IF EXISTS #Matches
CREATE TABLE #Matches (PK1 int, PK2 int)
-- 先插入正向关联
INSERT INTO #Matches
SELECT t1.PrimaryKey, t2.PrimaryKey FROM #Test t1
JOIN #Test t2 ON t2.PrimaryKey != t1.PrimaryKey AND t1.CustomerID1 = t2.CustomerID1
UNION
SELECT t1.PrimaryKey, t2.PrimaryKey FROM #Test t1
JOIN #Test t2 ON t2.PrimaryKey != t1.PrimaryKey AND t1.CustomerID2 = t2.CustomerID2
UNION
SELECT t1.PrimaryKey, t2.PrimaryKey FROM #Test t1
JOIN #Test t2 ON t2.PrimaryKey != t1.PrimaryKey AND t1.CustomerID3 = t2.CustomerID3
-- 插入反向关联，确保递归能遍历所有连通节点
INSERT INTO #Matches
SELECT PK2, PK1 FROM #Matches

-- 2. 递归CTE找出所有连通分量
WITH ConnectedComponents AS (
    -- 初始：每个主键自身为一个组
    SELECT PrimaryKey AS PK, PrimaryKey AS GroupID
    FROM #Test
    UNION ALL
    -- 递归：合并关联的节点到同一组
    SELECT m.PK2, cc.GroupID
    FROM ConnectedComponents cc
    JOIN #Matches m ON cc.PK = m.PK1
    WHERE m.PK2 NOT IN (SELECT PK FROM ConnectedComponents)
)
-- 3. 生成最终映射表：每个原主键对应唯一新ID
SELECT 
    DISTINCT
    t.PrimaryKey,
    -- 用分量中最小的原主键作为新ID，也可替换为自增ID/GUID
    MIN(cc.GroupID) OVER (PARTITION BY t.PrimaryKey) AS NewCustomerID
FROM #Test t
JOIN ConnectedComponents cc ON t.PrimaryKey = cc.PK
ORDER BY t.PrimaryKey

预期输出 #

自定义新ID（可选） #

如果需要从1开始的连续自增新ID，可在上述基础上添加DENSE_RANK()：

-- 接上述ConnectedComponents CTE
WITH Grouped AS (
    SELECT 
        DISTINCT
        MIN(cc.GroupID) OVER (PARTITION BY t.PrimaryKey) AS OriginalGroupID,
        t.PrimaryKey
    FROM #Test t
    JOIN ConnectedComponents cc ON t.PrimaryKey = cc.PK
)
SELECT 
    PrimaryKey,
    DENSE_RANK() OVER (ORDER BY OriginalGroupID) AS NewCustomerID
FROM Grouped
ORDER BY PrimaryKey

此时输出为：

核心逻辑说明 #

1. 关联对生成：通过Union获取所有基于三个ID的正向关联，再添加反向关联，确保递归时能遍历整个连通分量，不会遗漏节点。
2. 递归连通分量：从每个主键出发，递归合并所有关联的节点，将同一连通组内的记录归为同一个GroupID。
3. 映射表生成：通过去重和取最小GroupID，确保每个原记录对应唯一的新客户ID，满足主键1-5归为同一客户、主键6单独为一个客户的需求。

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