Дубли клиентов в SQL: как найти записи, связанные общими идентификаторами

В CRM один человек завёл два аккаунта, а затем третий. У первых двух совпадает подтверждённый email, у второго и третьего — идентификатор во внешней системе. Простая группировка по одному полю найдёт отдельные пары, но не соберёт всю группу. Разберём поиск связанных записей и отделим кандидатов на проверку от решения об объединении.

Совпадение должно иметь основание

Храним таблицу токенов: id клиента, тип идентификатора и нормализованное значение. Один и тот же текст email и внешнего id не означает связь, поэтому сравниваем и тип, и значение. Пробелы и регистр email нормализуют по принятому правилу до загрузки. Пустые значения сюда вообще не попадают.

Какие идентификаторы считать надёжными, решает владелец данных. Семейный телефон, общий офисный ящик и номер, переданный новому владельцу, способны связать разных людей. В примере используются условные подтверждённые токены. Даже для них результат запроса служит очередью разбора: контактная информация может быть общей или ошибочной.

Пары превращаются в цепочку

Если у A и B общий токен, рисуем связь A–B. Если B связан с C, все трое оказываются в одной связанной группе, даже когда у A и C нет прямого совпадения. При этом нужно сохранить и одиночные аккаунты без токенов: они остаются отдельными группами, а не исчезают из количества клиентов.

Сначала построим прямые связи самосоединением токенов. Затем рекурсивный CTE будет добавлять достижимые аккаунты. UNION удаляет уже встречавшиеся пары исходный–достижимый, поэтому цикл не заставит запрос бесконечно обходить те же записи. Для учебной базы это удобный способ увидеть сам механизм без сторонних библиотек.

Полный запрос SQLite

Добавим пять клиентов. Первые три связаны цепочкой, четвёртый имеет отдельный токен, пятый не имеет токенов. Минимальный id достижимой записи станет меткой группы. Это техническая метка текущего расчёта, а не новый постоянный клиентский идентификатор: после исправления данных состав группы может измениться.

CREATE TABLE clients(id INTEGER PRIMARY KEY);
CREATE TABLE tokens(client INTEGER,kind TEXT,value TEXT);
INSERT INTO clients VALUES (1),(2),(3),(4),(5);
INSERT INTO tokens VALUES
 (1,'email','a@example.test'),(2,'email','a@example.test'),
 (2,'external','ext-7'),(3,'external','ext-7'),
 (4,'email','b@example.test');
WITH RECURSIVE edges(a,b) AS (
 SELECT DISTINCT x.client,y.client FROM tokens x JOIN tokens y
 ON x.kind=y.kind AND x.value=y.value WHERE x.client<>y.client
), reach(root,node) AS (
 SELECT id,id FROM clients
 UNION
 SELECT reach.root,edges.b FROM reach JOIN edges ON edges.a=reach.node
)
SELECT root AS client_id,MIN(node) AS candidate_group
FROM reach GROUP BY root ORDER BY root;

Что проверять в найденной группе

Записи 1, 2 и 3 получили метку 1. Чтобы решить, можно ли их объединять, покажите проверяющему исходные совпадения и их происхождение. В группе могут оказаться конфликтующие договоры, разные юридические лица или несовместимые даты рождения. Такие признаки требуют остановить объединение и разобраться, даже когда цепочка формально построена правильно.

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

Как не сломать отчёт о клиентах

До принятия решений покажите два показателя: число исходных аккаунтов и число групп кандидатов. Второй показатель нельзя сразу переименовать в число реальных покупателей. Иначе ошибка общего телефона искусственно улучшит частоту покупок и среднюю выручку на клиента. Подтверждённые объединения храните в отдельной таблице соответствий с основанием и датой.

На больших массивах полный обход от каждого клиента создаёт много пар и может быть дорогим. Начните с индекса по типу и значению токена и оценки количества связей. Для миллионов записей стоит вынести поиск компонент в специализированную обработку, сохранив те же правила построения графа. Скорость не должна менять смысл связи.

Что передать владельцу CRM

Подготовьте список групп с исходными id, токенами и конфликтами. После ручного решения повторите бизнес-метрики на утверждённом соответствии и сравните с прежними значениями. Так можно объяснить, почему число клиентов изменилось без новых регистраций. История решений нужна, чтобы следующий импорт не создал те же дубли заново.

Сохраняем доказательства связи

Для пары 1–2 основанием служит email, для пары 2–3 — внешний id. Покажите эти две строки рядом с группой, а не только итоговую метку. Проверяющий сможет обнаружить ошибочный токен и понять, какая цепочка распадётся после исправления. Для длинных групп полезно сохранять краткий путь между спорными аккаунтами.

У токенов может быть срок действия. Общий телефон в разные годы не обязательно связывает действующих клиентов. Если источник хранит даты подтверждения и отвязки, формируйте связи с учётом принятого временного правила. Иначе рекурсия честно объединит записи по историческому совпадению, которое сегодня уже ничего не говорит о владельце аккаунта. Обновление нормализации тоже требует повторного пересчёта кандидатов.

Продолжить практику

Если уже знакомы с JOIN, следующий полезный шаг — научиться строить и проверять многошаговые запросы. В «Продвинутом SQL» есть рекурсивные CTE для деревьев и графов, проверка качества данных и задачи с автоматической проверкой. Откройте программу курса.