sql - 使用 "Merge"语法合并重复记录

Question

我正在使用 SQL Server 2014。我目前正在尝试将数百万条人事申请记录合并到一个人事记录中。

记录包含以下列:

ID, First_Name, Last_Name, DOB, Post_Code, Mobile, Email

一个人可以多次输入他们的详细信息，但由于手指粗或欺诈，他们有时会输入错误的详细信息。

在我的例子中，Christopher 填写了 5 次详细信息， First_Name , Last_Name , DOB总是正确的， Post_Code , Mobile和 Email包含各种内涵。

在这种情况下，我想要做的是获取与该组关联的 min(id) 84015283 并将其放入一个新表中，这将是主键，然后您将看到与其关联的其他 id。

例子

NID       CID
------------------
84015283  84015283
84015283  84069198
84015283  84070263
84015283  84369603
84015283  85061159

有点复杂的是，两个不同的人可以拥有相同的 First_Name , Last_Name和 DOB ，至少有一个其他字段必须与“ post_code 、 mobile 或 email ”(按照我的示例)与组中的另一条记录匹配。

虽然 first_name , last_name , DoB ID 的 84015283、84069198、84070263 之间的匹配。84015283、84069198 是相同的，因此它们匹配不会有问题，84070263 匹配邮政编码，84369603 匹配移动设备上的前一​​个记录，但不匹配 a1 和以前的移动设备上的邮政编码。

如果将 NID 放在原始数据集中更容易，我可以使用它而不是将其全部放在单独的表中。

经过一些谷歌搜索并试图解决这个问题后，我相信使用“合并”可能是实现我所追求的目标的好方法，但我担心由于涉及的记录数量而需要很长时间。

此外，任何例程都必须在后续新记录上运行。

如果有人可以提供帮助，我已经列出了示例的代码

DROP TABLE customer_dist

CREATE TABLE [dbo].customer_dist
(
    [id] [int] NOT NULL,
    [First_Name] [varchar](50) NULL,
    [Last_Name] [varchar](50) NULL,
    [DoB] [date] NULL,
    [post_code] [varchar](50) NULL,
    [mobile] [varchar](50) NULL,
    [Email] [varchar](100) NULL,
)

INSERT INTO customer_dist (id, First_Name, Last_Name, DoB, post_code, mobile, Email)
VALUES ('84015283', 'Christopher', 'Higg', '1956-01-13', 'CH2 3AZ', '07089559829', 'CH@hotmail.com'),
       ('84069198', 'Christopher', 'Higg', '1956-01-13', 'CH2 3AZ', '07089559829', 'CH@hotmail.com'),
       ('84070263', 'Christopher', 'Higg', '1956-01-13', 'CH2 3AZ', '07089559822', 'CHigg@AOL.com'),
       ('84369603', 'Christopher', 'Higg', '1956-01-13', 'CH2 3ZA', '07089559829', 'Higg@emailme.com'),
       ('85061159', 'CHRISTOPHER', 'Higg', '1956-01-13', 'CH2 3RA', '07089559829', 'CH@hotmail.com'),
       ('87065122', 'Matthew', 'Davis', '1978-05-10', 'CH5 1TS', '07077084692', 'Matt@gamil.com')

SELECT * FROM customer_dist

以下是预期的结果，对不起，我应该在最后更清楚地说明我想要什么。

输出表结果

    NID         id          First_Name  Last_Name   DoB         post_code   mobile          Email
    84015283    84015283    Christopher Higg            1/13/1956   CH2 3AZ         7089559829  CH@hotmail.com
    84015283    84069198    Christopher Higg            1/13/1956   CH2 3AZ         7089559829  CH@hotmail.com
    84015283    84070263    Christopher Higg            1/13/1956   CH2 3AZ         7089559822  CHigg@AOL.com
    84015283    84369603    Christopher Higg            1/13/1956   CH2 3ZA         7089559829  Higg@emailme.com
    84015283    85061159    CHRISTOPHER Higg            1/13/1956   CH2 3RA         7089559829  CH@hotmail.com
    78065122    87065122    Matthew Davis               05/10/1978  CH5 1TS

7077084692 Matt@gamil.com

OR                          

NID         id
84015283    84015283
84015283    84069198
84015283    84070263
84015283    84369603
84015283    85061159
87065122    87065122

对 react 缓慢表示歉意。

我已经更新了我需要的输出，我被要求包含一个与其他记录不匹配的额外记录，但没有包含在我需要的输出中。

HABO's response不幸的是，在使用其他样本数据进行进一步测试时，最接近所需的数据，创建了重复项并且逻辑崩溃了。其他样本数据将是:-

declare @customer_dist as Table (
    [id] [int] NOT NULL,
    [First_Name] [varchar](50) NULL,
    [Last_Name] [varchar](50) NULL,
    [DoB] [date] NULL,
    [post_code] [varchar](50) NULL,
    [mobile] [varchar](50) NULL,
    [Email] [varchar](100) NULL );


INSERT INTO @customer_dist (id, First_Name, Last_Name, DoB, post_code, mobile, Email)
VALUES ('32006455', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07706212920',  'nastie220@yahoo.com'),
       ('35963960', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07484863324',  'nastie@hotmail.com'),
       ('38627975', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07484863478',  'nastie2001@yahoo.com'),
       ('46653041', 'Mary', 'WILSON',   '1983-09-20',   'BT62JA',   '07483888179',  'nastie2010@yahoo.com'),
       ('48023677', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07483888179',  'nastie@hotmail.com'),
       ('49560434', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07849727199',  'nastie@hotmail.com'),
       ('49861032', 'Mary', 'WILSON',   '1983-09-20',   'BT62JA',   '07849727199',  'nastie2001@yahoo.com'),
       ('53130969', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07849727199',  'Nastie@hotmail.cm'),
       ('33843283', 'Mary', 'Wilson',   '1983-09-20',   'BT148HU',  '07484863478',  'nastie2010@yahoo.co.uk'),
       ('38627975', 'Mary', 'Wilson',   '1983-09-20',   'BT62JA',   '07484863478',  'nastie2001@yahoo.com')

SELECT * FROM @customer_dist;

Answer 1

我曾经在一家非常古老的保险公司工作，他们在数据方面也有类似的问题。

我在这里的主要尝试是缩小包含重复项的结果集，寻找将重复项联系在一起的原因。一旦你得到了这个，其余的解决方案就会很快出现。

逻辑是:基于共享相同值(Fname、Lname、DOB)和偶尔相同值(post_code、mobile、email)和更重要的 ids 不匹配的列将表连接到自身，这确保排除非重复记录只留下重复。

在只有 dups 之后，找到 MIN(id) 并将其放入 cte，加入原始表，然后就可以了。非重复记录不需要 min-id，因为它的 id 是 min-id。

;WITH DUPS AS
(
SELECT DISTINCT
    MIN(C1.ID) OVER(PARTITION BY C1.First_Name, 
C1.Last_Name, C1.DoB) AS minid,
    C1.id, C1.First_Name, C1.Last_Name, C1.DoB
FROM customer_dist c1 
INNER join customer_dist c2 
ON
c1.First_Name = c2.First_Name
AND c1.Last_Name = c2.Last_Name
AND c1.DoB = c2.DoB
AND (c1.post_code = c2.post_code OR c1.mobile = c2.mobile 
OR 
c1.Email = c2.Email)
AND C1.ID <> C2.ID
)

SELECT ISNULL(D.minid, C.ID) AS NID,
        C.*
FROM customer_dist C
LEFT JOIN DUPS D ON C.id = D.id