Создание единого идентификатора для любой комбинации совпадений между тремя разными идентификаторами

Question

У меня есть данные о клиентах, поступающие из нескольких разных систем, с тремя потенциальными уникальными идентификаторами. Нам нужно создать один новый идентификатор, который можно использовать для связи ЛЮБОГО совпадения между тремя идентификаторами.

Данные имеют следующую структуру:

Я попытался создать список всех возможных совпадений, как показано ниже, но я не уверен, как объединить его в единый идентификатор для каждого клиента.

      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')

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

Я чувствую, что решение очевидно, но я застрял, поэтому любая помощь приветствуется! Спасибо!

Answer 1

Это немного сложно сделать за один выбор (по крайней мере, для меня). Я обычно делаю что-то вроде этого:

      SELECT  *, CAST(NULL AS INT) AS ID_To
INTO #t
FROM
(
    VALUES  (1, N'Alpha', N'Dog', N'Jeans')
    ,   (2, N'Alpha', N'Cat', N'Shirt')
    ,   (3, N'Beta', N'Dog', N'Dress')
    ,   (4, N'Gamma', N'Bear', N'Jeans')
    ,   (5, N'Alpha', N'Dog', N'Jeans')
    ,   (5, N'Alpha', N'Bonanza', N'Boots')
    ,   (6, N'Epsilon', N'Bird', N'Boots')
    ,   (7, N'zz', N'dog', N'Bird')
    ,   (8, N'zzz', N'bye', N'hi')
    ,   (9, N'zzzz', N'bear', N'hi ho silver')
) t (ID,CustomerID1,CustomerID2,CustomerID3)

WHILE @@rowcount > 0
BEGIN
    UPDATE  t2
    SET ID_to = ISNULL(t.ID_To, t.ID)
    FROM    #t t
    LEFT JOIN #t tTo
        ON  tTo.ID = t.ID_To
    CROSS APPLY (
        VALUES (t.Customerid1), (t.Customerid2), (t.Customerid3)
        ) v(externalId) 
    CROSS JOIN #t t2
    CROSS APPLY (
        VALUES (t2.Customerid1), (t2.Customerid2), (t2.Customerid3)
        ) v2(externalId) 
    WHERE   ISNULL(t.id_To, t.id) < ISNULL(t2.ID_to, t2.id)
    AND v.externalId = v2.externalId
    AND t.ID <> t2.ID
END

SELECT  *
FROM    #t

SELECT  ISNULL(ID_TO, ID) AS groups
FROM    #t
GROUP BY ISNULL(ID_TO, ID)

Чтобы объяснить код:

1. Я создаю два столбца в вашей таблице, содержащие идентификатор строки (ID) и идентификатор возможного совпадения (ID_to). Это позволяет нам консолидировать то, как каждый клиент сопоставляется с другими клиентами.
2. Потом делаю петлю. В цикле я пытаюсь сопоставить ключи между двумя разными клиентами. Что я хочу сделать, так это сопоставить каждого клиента с другим идентификатором клиента1,2,3 (И v.externalId = v2.externalId )
3. Чтобы убедиться, что цикл завершен, я хочу только сопоставить, если новый идентификатор ниже текущего ISNULL(t.id_To, t.id) < ISNULL(t2.ID_to, t2.id). Если соответствующий клиент уже сопоставлен с кем-то другим, я беру его ID_to. Это гарантирует, что если есть длинная цепочка совпадающих идентификаторов, мы будем следовать всей цепочке.
4. Трюк WHILE @@ROWCOUNT > 0 весьма полезен для зацикливания до тех пор, пока не будет что делать. Но на самом деле важно иметь условие прерывания, иначе цикл будет работать вечно.

Вы можете поместить выборку в цикл while, чтобы отслеживать, что происходит с #t-таблицей.

Когда цикл завершен, каждый клиент должен иметь либо ID_to = NULL, что означает, что он является «главным», либо ID_to > 0, что означает, что этот клиент сопоставляется с другим основным клиентом.