Могу ли я группировать данные по взаимным полям?

Question

Привет, в настоящее время я пытаюсь создать запрос для группировки клиентов.

В настоящее время я хочу сгруппировать по «Номеру телефона», «Электронной почте» и, возможно, по некоторым другим полям в будущем.

Моя проблема в том, что я могу захотеть сгруппировать строки, которые не связаны напрямую, но имеют взаимные строки.

Например, на этой диаграмме мы видим, что Клиент 2 и Клиент 5 вообще никак не связаны между собой, однако они оба имеют общие связи с Клиентом 1, что позволило бы им сгруппироваться вместе.

Диаграмма Венна, описывающая мой идеальный набор данных

Вот еще одно представление:CustomerId | Полное имя | Номер телефона | Электронная почта --|--|--|---------------------------------------- ---------------------------------- 1 | Билл Смит | 01612345678 | [e-mail]2 | Келли Смит | 01612345678 | [e-mail]3 | Кевин Роуч | 07111111111 | [e-mail]4 | Крис Рональд | 07222222222 | [e-mail]5 | Билл Смит | 07987654321 | [e-mail]

Мы видим, что Клиент 2 подключен к Клиенту 1 через общий номер телефона, а Клиент 5 подключен к 1 через общий адрес электронной почты. Однако Клиент 2 и Клиент 5 не передают никакой информации, которая могла бы их изначально сгруппировать.

Моя причина необходимости в этой функции заключается в том, что один человек/домохозяйство может иметь несколько учетных записей клиентов с разной информацией, поэтому я стараюсь сгруппировать их как можно лучше, используя как можно больше общей информации.

я уже пробовал использоватьDENSE_RANK()иGROUP BY, но они, похоже, дополнительно разделяют группы при добавлении более 1 столбца, я хочу, чтобы группа создавалась исключительно на основе того, что одно поле данных соответствует другому.

Answer 1

Я принял отличный ответ из раздела « Как объединить идентификаторы групп в одну группу?»от GMB по вашему вопросу:

      SELECT  *
INTO    #data
FROM    (
    VALUES  (1, N'Bill Smith', N'01612345678', N'[e-mail]', NULL)
    ,   (2, N'Kelly Smith', N'01612345678', N'[e-mail]', NULL)
    ,   (3, N'Kevin Roach', N'07111111111', N'[e-mail]', '12345')
    ,   (4, N'Chris Ronald', N'07222222222', N'[e-mail]', NULL)
    ,   (5, N'Bill Smith', N'07987654321', N'[e-mail]', NULL)
    ,   (6, N'Gill Smith', N'07987654322', NULL, 12345)
    ,   (7, N'Gill McGill', N'07987654322', NULL, NULL)
    ,   (8, N'Gillian Smith', NULL, NULL, 12345)
    ,   (9, N'Smith W', NULL, '[e-mail]', 12345)
) t (CustomerId,FullName,PhoneNumber,Email, Fax)

;WITH edges AS (
    SELECT  d.customerid, d2.customerid AS custIdTo
    FROM    #data d
    INNER JOIN #data d2
        ON  (
            d2.PhoneNumber = d.PhoneNumber
        OR  d2.email = d.email
        OR  d2.fax = d.fax
        )
        --AND   d2.customerid <> d.customerid -- remove unmatched customers by uncommenting this row
    ) 
, rec AS (
    SELECT  customerid, custidto, cast(concat(customerid, ',', custidto) AS nvarchar(max)) AS path
    FROM    edges
    UNION ALL
    SELECT  c.customerid, e.custidto, concat(path, ',', e.custidto)
    FROM    rec c
    INNER JOIN edges e
        ON  e.customerid = c.custidto
    WHERE   ',' + path + ',' NOT LIKE '%,' + cast(e.custidto AS nvarchar(max)) + ',%'
    
    )
SELECT  customerid, CAST(MIN(x.value) AS INT) AS baseCustomer
FROM    rec 
CROSS apply string_split(path, ',') x
GROUP BY customerid
option(maxrecursion 0);

В качестве примера я добавил поле факса, и клиенты подбираются по телефону, электронной почте или факсу.

Немного сложно объяснить, как это работает.

Сначала я создаю образец данных и помещаю его в таблицу #data.

Затем мы делаем два CTE:

• Edges содержит все совпадения между клиентами.
• рекурсивный cte под названием Rec работает, сначала выбирая все совпадения. Затем для каждого совпадения он сохраняет исходный идентификатор клиента, а затем проверяет, какие еще совпадения существуют между идентификатором customerIdTo и его собственными совпадениями.
• cte построит вещь, называемую path, которая является «дорогой», которую он взял из каждого совпадения, путем объединения между custIdFrom, «,» и custIdTo.

Например, если у нас есть следующие данные о краях:

        from to
  1  2
  2  3
  3  1

он возьмет 1 => 2, а затем найдет совпадение 2 в данных ребра и найдет 2 => 3. Затем он будет искать совпадения 3 и найдет 3 => 1.

Таким образом, для клиента 1 он создаст три строки: 1 => 2, 1 => 3, 3 => 1, но затем он проверит, чтобы он не возвращался к уже выполненной строке, поэтому 3 => 1 будет быть отброшено, потому что 1 уже было в пути 1 => 2.

Эта проверка выполняется здесь:WHERE ',' + path + ',' NOT LIKE '%,' + cast(e.custidto AS nvarchar(max))

Если мы не выполним эту проверку, цикл будет продолжаться вечно, что не очень хорошо.

Наконец, у нас есть множество строк с клиентом и путем к каждому узлу. Чтобы получить соответствующий узел, мы выполняем string_split и получаем минимальное значение «custIdTo» для каждого идентификатора клиента.

РЕДАКТИРОВАТЬ: Производительность этих вещей невелика из-за огромного количества потенциальных циклов и объединений. Альтернативой может быть что-то вроде этого: /questions/65027208/sozdanie-edinogo-identifikatora-dlya-lyuboj-kombinatsii-sovpadenij-mezhdu-tremya/65818878#65818878 , что немного более тривиально, но означает циклы.