时间戳

首页 > 解决方案 > 查找链中的最后一条记录 - 客户合并流程

sql - 查找链中的最后一条记录 - 客户合并流程

问题描述

我正在从另一个供应商的系统导入客户数据，我们有合并流程，用于识别潜在的重复客户账户，如果它们满足某些条件（例如相同的名字、姓氏、SSN 和 DOB），它们会合并它们。在这个过程中，我看到了我们在哪里创建链——例如，客户 A 被合并到客户 B，然后客户 B 被合并到客户 C。

我希望做的是识别这些链并更新客户记录以指向链中的最后一条记录。所以在我上面的例子中，客户 A 和客户 B 在他们合并的 To 字段中都有客户 C 的 id。

CustID FName   LName     CustStatusType  isMerged  MergedTo
1      Kevin   Smith     M               1         2 
2      Kevin   Smith     M               1         3
3      Kevin   Smith     M               1         4
4      Kevin   Smith     O               0         NULL
5      Mary    Jones     O               0         NULL
6      Wyatt   Earp      M               1         7
7      Wyatt   Earp      O               1         NULL
8      Bruce   Wayn      M               1         10
9      Brice   Wayne     M               1         10
10     Bruce   Wane      M               1         11
11     Bruce   Wayne     O               1         NULL

CustStatusType 指示客户帐户是打开（“O”）还是合并（“M”）。然后我们有一个 isMerged 字段作为 BIT 字段，指示帐户是否已合并，最后是 MergedTo 字段，指示记录合并到哪个客户帐户。

通过提供的示例，我想要实现的是让 1 和 2 的 CustID 记录将其 MergedTo 记录设置为 3 - 而 CustID 3 可以更新或保持原样。对于客户 ID 4、5 和 6 - 可以找到这些记录，无需更新。但在客户 ID 8 - 10 上，我希望将这些记录设置为 11 - 如下表所示。

CustID FName   LName     CustStatusType  isMerged  MergedTo
1      Kevin   Smith     M               1         4 
2      Kevin   Smith     M               1         4
3      Kevin   Smith     M               1         4
4      Kevin   Smith     O               0         NULL
5      Mary    Jones     O               0         NULL
6      Wyatt   Earp      M               1         7
7      Wyatt   Earp      O               1         NULL
8      Bruce   Wayn      M               1         11
9      Brice   Wayne     M               1         11
10     Bruce   Wane      M               1         11
11     Bruce   Wayne     O               1         NULL

我无法弄清楚如何使用 TSQL 来实现这一点 - 建议？

测试数据：

DROP TABLE IF EXISTS #Customers;

CREATE TABLE #Customers
    (
        CustomerID INT ,
        FirstName VARCHAR (25) ,
        LastName VARCHAR (25) ,
        CustomerStatusTypeID VARCHAR (1) ,
        isMerged BIT ,
        MergedTo INT
    );
INSERT INTO #Customers
VALUES ( 1, 'Kevin', 'Smith', 'M', 1, 2 ) ,
       ( 2, 'Kevin', 'Smith', 'M', 1, 3 ) ,
       ( 3, 'Kevin', 'Smith', 'M', 1, 4 ) ,
       ( 4, 'Kevin', 'Smith', 'O', 0, NULL ) ,
       ( 5, 'Mary', 'Jones', 'O', 0, NULL ) ,
       ( 6, 'Wyatt', 'Earp', 'M', 1, 7 ) ,
       ( 7, 'Wyatt', 'Earp', 'O', 1, NULL ) ,
       ( 8, 'Bruce', 'Wayn', 'M', 1, 10 ) ,
       ( 9, 'Brice', 'Wayne', 'M', 1, 10 ) ,
       ( 10, 'Bruce', 'Wane', 'M', 1, 11 ) ,
       ( 11, 'Bruce', 'Wayne', 'O', 1, NULL );

SELECT *
FROM   #Customers;

DROP TABLE #Customers;

标签： sqltsqlsql-server-2016

解决方案

对于你的例子soundex()似乎足够好。它返回一个代码，该代码基于单词在英语中的发音。在名字和姓氏上使用它来连接客户表和查询客户表的子查询，添加row_number()由名称的 Soundex 分区并按 ID 降序排列的顺序 - 将“最新”记录编号为 1。对于连接条件使用名称的 Soundex、行号 1，当然还有 ID 的不等式。

UPDATE c1
       SET c1.mergedto = x.customerid
       FROM #customers c1
            LEFT JOIN (SELECT c2.customerid,
                              soundex(c2.firstname) sefn,
                              soundex(c2.lastname) seln,
                              row_number() OVER (PARTITION BY soundex(c2.firstname),
                                                              soundex(c2.lastname)
                                                 ORDER BY c2.customerid DESC) rn
                              FROM #customers c2) x
                      ON x.sefn = soundex(c1.firstname)
                         AND x.seln = soundex(c1.lastname)
                         AND x.rn = 1
                         AND x.customerid <> c1.customerid;

db<>小提琴

我真的不明白customerstatustypeidandismerged列背后的概念。据我了解，它们都源自是否mergedto为空。但是样本数据既不是预期的结果也不支持这一点。但是由于这些列显然在您的示例输入和输出之间没有变化，我想没关系，我只是不理会它们。

如果 Soundex 证明不足以满足您的需求，您可能需要寻找其他字符串距离指标，例如Levenshtein distance。AFAIK 没有包含在 SQL Server 中的实现，但搜索引擎可能会吐出第三方的实现，或者可能有一些可以通过 CLR 使用的东西。或者你自己滚动，当然。

推荐阅读