← Все курсы · Курс SQL

Дедупликация списка клиентов, никого не теряя

Майкл Носито · Обновлено в августе 2026 г. · Все числа на этой странице были обработаны до их публикации.

Вы нашли 340 повторяющихся клиентов. Инстинкт состоит в том, чтобы сохранить самое новое и удалить остальное, и это неверно дважды: самая новая запись часто имеет самые пустые поля, а удаление строк разрушает все, что на них указывает.

Что вы делаете: сначала напишите правила выживания, объедините поле за полем, сохраните проигрышные строки помеченными, а не удаленными, и докажите четырьмя счетами, что ничего не было потеряно.

Краткая версия. Сохранившаяся запись обычно не является одной из исходных строк. Он собран из них.

Три дубликата, которые вы найдете

Третий ряд — это место нанесения урона. Правило, которое объединяет только адрес электронной почты, объединит два законных контакта в один и незаметно потеряет отношения, от которых зависит кто-то в бизнесе.

Правила выживания, поле за полем

Запишите их, прежде чем что-либо запускать. Это бизнес-решения, а не технические, и им нужен кто-то, кто будет ими владеть:

Строка согласия не является обязательной. Объединение записи, которая отказалась от участия, с записью, которая согласилась, и сохранение разрешающего значения создает проблему соответствия требованиям в процессе очистки данных.

Выбор выжившего в SQL

WITH ranked AS (
  SELECT customer_id, match_key, updated_at, created_at,
         ROW_NUMBER() OVER (PARTITION BY match_key
                            ORDER BY created_at ASC, customer_id ASC) AS rn
  FROM customers
)
SELECT match_key,
       MIN(CASE WHEN rn = 1 THEN customer_id END) AS surviving_id
FROM ranked
GROUP BY match_key
HAVING COUNT(*) > 1;

ROW_NUMBER здесь правильная функция, а не RANK, потому что при ничьей все равно должен остаться ровно один выживший. Начался тай-брейк customer_id это то, что гарантирует это, а также делает результат воспроизводимым, что важно при повторном запуске задания. Три ранговые функции сравниваются в RANK, DENSE_RANK и ROW_NUMBER.

Затем соберите оставшиеся значения поле за полем, взяв первые непустые значения в выбранном вами порядке, а не все из сохранившейся строки.

Никогда не удаляйте проигравших

ALTER TABLE customers ADD COLUMN merged_into INT;
ALTER TABLE customers ADD COLUMN merged_at    DATE;

набор merged_into на проигрышных строках и отфильтровывать их из представлений отчетов. Все продолжает работать: старый заказ, указывающий на клиента 4102, все еще разрешается, и его можно выполнить до выжившего. Удаленная строка оставляет «сироту», а «сироты» — это то, как общий доход теряет несколько процентов между двумя отчетами.

Четыре чека, которые доказывают, что ничего не было потеряно

1. Количество записей. Активное после плюс объединенное равно общему количеству до. Точно.
2. Доход. Общий доход после равен общему доходу до, с точностью до копейки. Объединение клиентов перемещает заказы между родительскими компаниями; он никогда не удаляет заказ.
3. Сироты. Подсчитайте дочерние строки, родительский идентификатор которых отсутствует в таблице клиентов. Оно должно быть равно нулю до и после.
4. Образец обзора. Двадцать объединенных пар, прочитанных человеком, в том числе пять из диапазона высокой достоверности, который обычно никто не проверяет.

-- check 3, the one that catches a delete you did not mean
SELECT COUNT(*) FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c
                  WHERE c.customer_id = o.customer_id);

NOT EXISTS скорее, чем NOT IN, по причине, изложенной в анти-соединение: один NULL и NOT IN возвращает ноль строк, что считается чистым результатом.

Журнал слияния

Одна строка для каждого слияния: потерянный идентификатор, выживший идентификатор, ключ совпадения, метод, оценка, кто утвердил его, когда. Это то, что позволяет вам ответить на вопрос «почему эти двое — один и тот же клиент» шесть месяцев спустя, и это то, что делает неправильное слияние обратимым, а не постоянным.

Как применить это в своей работе

1. Напишите правила выживания и согласуйте их с тем, кто владеет отношениями с клиентами, а не с тем, кто владеет базой данных.
2. Сначала запустите весь процесс на копии и сравните четыре проверки копии и оригинала.
3. Перед автоматическим объединением необходимо указать два совпадающих поля.
4. Марк, никогда не удаляй. Прежде чем начать, добавьте два столбца.
5. Ознакомьтесь с примером автоматического слияния, а также с полосой вручную.

Единственная привычка, которую нужно сохранить

Согласуйте деньги. Количество записей намеренно изменяется во время дедупликации, поэтому они не могут сказать вам, произошло ли что-то не так. Выручка вообще не должна меняться, поэтому именно проверка выявляет ошибки.

Если завтра слияние в вашей CRM окажется неправильным, сможет ли кто-нибудь его отменить?