对客户数据进行重复数据删除和合并

Question

我正在尝试基于ClusterID合并记录，以获得丰富的客户记录数据。

如何使用MS SQL对以下内容进行分组？Coalesce不会工作，因为记录需要在同一行上才能工作，如果每个集群有2个以上的匹配，这将是一个乏味的处理。在所有列中使用max by ClusterId是我正在做的一种变通方法，但我希望有一种更有效的方法来做到这一点。

具备以下条件：

ClusterID,CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo
100,NULL,Person,person@email.com,NULL,OfficeAdd,12345,NULL,123
100,456,Person,person@email.com,98765,HomeAdd,34567,P12345,NULL

**这是SSIS DQS匹配节点(https://ssisdqsmatching.codeplex.com/)的结果。它可以进行匹配，但不能处理幸存部分来获得金牌记录。

想要：

ClusterID,CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo
100,456,Person,person@email.com,98765,OfficeAdd,12345,P12345,123

任何想法都将不胜感激。谢谢!

Answer 1

DECLARE @CLUSTERID VARCHAR(MAX),@CUSTOMERNO VARCHAR(MAX),@NAME VARCHAR(MAX),@EMAIL VARCHAR(MAX),@MOBILE VARCHAR(MAX)
DECLARE @POSTALCODE VARCHAR(MAX),@PASSPORT VARCHAR(MAX),@PROFILENO VARCHAR(MAX),@ADDRESS VARCHAR(MAX)

DECLARE @NCLUSTERID VARCHAR(MAX),@NCUSTOMERNO VARCHAR(MAX),@NNAME VARCHAR(MAX),@NEMAIL VARCHAR(MAX),@NMOBILE VARCHAR(MAX)
DECLARE @NPOSTALCODE VARCHAR(MAX),@NPASSPORT VARCHAR(MAX),@NPROFILENO VARCHAR(MAX),@NADDRESS VARCHAR(MAX)
DECLARE @NEW_TABLE TABLE (  ClusterID varchar(max) ,
    CustomerNo varchar(max) ,
    Name varchar(max) ,
    Email varchar(max) ,
    Mobile varchar(max) ,
    Address varchar(max) ,
    PostalCode varchar(max) ,
    Passport varchar(max) ,
    ProfileNo varchar(max) 
)

DECLARE C CURSOR FOR
SELECT DISTINCT CLUSTERID FROM CUSTOMER
OPEN C
FETCH NEXT FROM C INTO @CLUSTERID
WHILE @@FETCH_STATUS=0
BEGIN

    DECLARE D CURSOR FOR
    select CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo from customer where ClusterID=@CLUSTERID
    OPEN D
    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    WHILE @@FETCH_STATUS=0
    BEGIN   

    IF @CustomerNo is not null SET  @NCustomerNo=@CustomerNo
    IF @CustomerNo IS NOT NULL SET  @NCustomerNo= @CustomerNo ;
    IF @Name       IS NOT NULL SET  @NName      = @Name       ;
    IF @Email      IS NOT NULL SET  @NEmail     = @Email      ;
    IF @Mobile     IS NOT NULL SET  @NMobile    = @Mobile     ;
    IF @Address    IS NOT NULL SET  @NAddress   = @Address    ;
    IF @PostalCode IS NOT NULL SET  @NPostalCode= @PostalCode ;
    IF @Passport   IS NOT NULL SET  @NPassport  = @Passport   ;
    IF @ProfileNo  IS NOT NULL SET  @NProfileNo = @ProfileNo  ; 

    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    END
    CLOSE D
    DEALLOCATE D
        INSERT INTO @NEW_TABLE VALUES (
         @CLUSTERID,
         @NCustomerNo   ,
         @NName         ,
         @NEmail        ,
         @NMobile       ,
         @NAddress      ,
         @NPostalCode   ,
         @NPassport     ,
         @NPROFILENO    
        )



FETCH NEXT FROM C INTO @CLUSTERID
END
CLOSE C
DEALLOCATE C


SELECT * FROM @NEW_TABLE

Answer 2

我想这个查询解决了您的需求

DECLARE @CLUSTERID VARCHAR(MAX),@CUSTOMERNO VARCHAR(MAX),@NAME VARCHAR(MAX),@EMAIL VARCHAR(MAX),@MOBILE VARCHAR(MAX)
DECLARE @POSTALCODE VARCHAR(MAX),@PASSPORT VARCHAR(MAX),@PROFILENO VARCHAR(MAX),@ADDRESS VARCHAR(MAX)

DECLARE @NCLUSTERID VARCHAR(MAX)=NULL,@NCUSTOMERNO VARCHAR(MAX)=NULL,@NNAME VARCHAR(MAX)=NULL,@NEMAIL VARCHAR(MAX)=NULL,@NMOBILE VARCHAR(MAX)=NULL
DECLARE @NPOSTALCODE VARCHAR(MAX)=NULL,@NPASSPORT VARCHAR(MAX)=NULL,@NPROFILENO VARCHAR(MAX)=NULL,@NADDRESS VARCHAR(MAX)=NULL
DECLARE @NEW_TABLE TABLE (  ClusterID varchar(max) ,
    CustomerNo varchar(max) ,
    Name varchar(max) ,
    Email varchar(max) ,
    Mobile varchar(max) ,
    Address varchar(max) ,
    PostalCode varchar(max) ,
    Passport varchar(max) ,
    ProfileNo varchar(max) 
)

DECLARE C CURSOR FOR
SELECT DISTINCT CLUSTERID FROM CUSTOMER
OPEN C
FETCH NEXT FROM C INTO @CLUSTERID
WHILE @@FETCH_STATUS=0
BEGIN

    DECLARE D CURSOR FOR
    select CustomerNo,Name,Email,Mobile,Address,PostalCode,Passport,ProfileNo from customer where ClusterID=@CLUSTERID
    OPEN D
    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    WHILE @@FETCH_STATUS=0
    BEGIN   

    IF @CustomerNo is not null SET  @NCustomerNo=@CustomerNo
    IF @CustomerNo IS NOT NULL SET  @NCustomerNo= @CustomerNo ;
    IF @Name       IS NOT NULL SET  @NName      = @Name       ;
    IF @Email      IS NOT NULL SET  @NEmail     = @Email      ;
    IF @Mobile     IS NOT NULL SET  @NMobile    = @Mobile     ;
    IF @Passport   IS NOT NULL SET  @NPassport  = @Passport   ;
    IF @ProfileNo  IS NOT NULL SET  @NProfileNo = @ProfileNo  ; 

    IF (@ADDRESS IS NOT NULL AND @NADDRESS IS NOT NULL)
    BEGIN
    SET @NAddress   = @Address    ;
    SET  @NPostalCode= @PostalCode ;
    END
    ELSE IF(@ADDRESS IS NOT NULL AND @NADDRESS IS NULL)
    BEGIN
    SET @NAddress   = @Address    ;
    SET  @NPostalCode= NULL ;
    END
    ELSE IF(@ADDRESS IS NULL AND @NADDRESS IS NOT NULL)
    BEGIN
    SET @NAddress   = NULL    ;
    SET  @NPostalCode= @PostalCode ;
    END


    FETCH NEXT FROM D INTO @CustomerNo,@Name,@Email,@Mobile,@Address,@PostalCode,@Passport,@ProfileNo
    END
    CLOSE D
    DEALLOCATE D

        INSERT INTO @NEW_TABLE VALUES (
         @CLUSTERID,
         @NCustomerNo   ,
         @NName         ,
         @NEmail        ,
         @NMobile       ,
         @NAddress      ,
         @NPostalCode   ,
         @NPassport     ,
         @NPROFILENO    
        )



FETCH NEXT FROM C INTO @CLUSTERID
END
CLOSE C
DEALLOCATE C


SELECT * FROM @NEW_TABLE