SQL — найти все связанные записи по id/phone/mail (рекурсивный CTE)

Условие

Дана таблица с колонками:

По заданному значению поля (id, phone или mail) нужно вернуть все связанные записи — то есть транзитивно склеенный «островок» записей, которые так или иначе делят id/phone/mail друг с другом.

Например, для phone = 87778885566:

• запись 2 матчится напрямую (phone = 87778885566);
• запись 4 матчится тем же телефоном;
• запись 5 разделяет mail = four@mail.ru с записью 4 → подтягивается;
• запись 6 разделяет id = 34567 с записью 5 → тоже подтягивается.

Итог: записи 2, 4, 5, 6.

Задача — типичная дедупликация / антифрод: нужно построить «связные компоненты» по графу записей.

Решение

Подход

Это задача поиска связной компоненты в графе, где:

• вершины — записи (key);
• ребро — две записи, у которых совпадает хотя бы одно из значений id, phone, mail.

Делается через рекурсивный CTE (поддерживается Postgres 8.4+, MS SQL, Oracle 11g+, MySQL 8+, ClickHouse 22.3+).

Идея:

1. Стартуем с записи (или записей), которые матчатся условию phone = '87778885566'.
2. На каждой итерации находим записи, у которых id, phone или mail совпадает с уже найденными.
3. Останавливаемся, когда новых записей нет.

Реализация (Postgres / стандартный SQL)

WITH RECURSIVE
seed AS (
  -- шаг 0: явный стартовый матч
  SELECT key, id, phone, mail
  FROM records
  WHERE phone = '87778885566'
),
visited AS (
  SELECT key, id, phone, mail FROM seed
  UNION
  SELECT r.key, r.id, r.phone, r.mail
  FROM records r
  JOIN visited v
    ON r.id    = v.id
    OR r.phone = v.phone
    OR r.mail  = v.mail
)
SELECT DISTINCT key, id, phone, mail
FROM visited
ORDER BY key;

UNION (без ALL) важен — он отбрасывает дубли, и рекурсия завершается, когда новых строк не появляется.

Параметризация по любому из трёх полей

Чтобы запрос работал «по любому ключу», достаточно сделать seed гибким:

WITH RECURSIVE
seed AS (
  SELECT * FROM records
  WHERE id = :search_id           -- или
     OR phone = :search_phone     -- или
     OR mail = :search_mail
),
visited AS (
  SELECT * FROM seed
  UNION
  SELECT r.*
  FROM records r
  JOIN visited v ON r.id = v.id OR r.phone = v.phone OR r.mail = v.mail
)
SELECT DISTINCT * FROM visited;

В клиенте подставляется только нужный параметр, остальные = NULL и отфильтруются.

Обход через self-join (без рекурсии)

В простых случаях (когда максимальная глубина связей маленькая, скажем 2–3) можно обойтись повторными JOIN:

SELECT DISTINCT r3.*
FROM records r1
JOIN records r2 ON r1.id = r2.id  OR r1.phone = r2.phone OR r1.mail = r2.mail
JOIN records r3 ON r2.id = r3.id  OR r2.phone = r3.phone OR r2.mail = r3.mail
WHERE r1.phone = '87778885566';

Но при глубине 4–5+ это уже кошмарно — рекурсивный CTE универсальнее.

Подводные камни

1. UNION vs UNION ALL. В рекурсивной части обязательно UNION — иначе дубли будут плодиться бесконечно и запрос не остановится.
2. NULL в ключевых полях. Если у записи mail IS NULL и у другой тоже NULL, условие r.mail = v.mail не сматчит (NULL = NULL → NULL). Это, скорее всего, правильно (нельзя считать «нет почты» признаком связи), но поведение надо явно обсудить.
3. «Грязные» значения. 'three@mail' (без .ru), ' test@mail.ru ' (пробелы) — это разные значения. На входе нужно нормализовать: LOWER(TRIM(mail)), REGEXP_REPLACE(phone, '[^0-9]', '').
4. Циклы и производительность. На большой таблице (>100M записей) self-join по OR — катастрофа. Нужны индексы на id, phone, mail отдельно, и переписать OR через UNION трёх отдельных запросов:

   SELECT r.* FROM records r JOIN visited v ON r.id = v.id
   UNION
   SELECT r.* FROM records r JOIN visited v ON r.phone = v.phone
   UNION
   SELECT r.* FROM records r JOIN visited v ON r.mail = v.mail

   Оптимизатор тогда использует индексы.
5. Глубина рекурсии. Postgres лимита по умолчанию нет, но запрос может «задумиваться». В MS SQL стоит OPTION (MAXRECURSION 0) для снятия лимита 100.
6. Не путать с UNION FIND. На очень больших данных (антифрод-граф) рекурсивный CTE медленный — там пишут пакетный Union-Find на Spark/Python (см. парную задачу sber-python-connected-records-graph).

Эталонный ответ

WITH RECURSIVE visited AS (
  SELECT key, id, phone, mail FROM records WHERE phone = '87778885566'
  UNION
  SELECT r.key, r.id, r.phone, r.mail
  FROM records r JOIN visited v
    ON r.id = v.id OR r.phone = v.phone OR r.mail = v.mail
)
SELECT * FROM visited ORDER BY key;

Возвращает связную компоненту записей: для тестовых данных — key in (2, 4, 5, 6).