Как найти повторяющиеся строки в SQL (и решить, что считать одной)

1. Решите, что считать дубликатом, прежде чем писать что-либо.

Перед пояснением: клиент 103 появляется три раза, причем все столбцы идентичны. Клиент 105 появляется дважды с двумя разными городами. Электронная почта Бена Ортиса отображается под двумя разными идентификаторами. Какие из этих трех являются дубликатами?

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

• Весь ряд одинаковый. Каждый столбец совпадает, как клиент 103. Почти всегда это случайная загрузка: один и тот же файл импортирован дважды, повторная вставка, копипаста. Дополнительные копии не несут никакой информации, и их можно безопасно удалить после пометки.
• Тот же естественный ключ, остальное отличается. Естественный ключ — это столбец, который идентифицирует вещь в реальном мире, например customer_id. У клиента 105 один идентификатор и два города, поэтому одна строка устарела, а другая — текущая. Удаление неправильной копии уничтожает реальную информацию, поэтому здесь решается, какую копию оставить, а не сколько удалить.
• Тот же человек, другое написание. Бен Ортис и Бенджамин Ортис обмениваются электронными письмами, но ничего GROUP BY может точно совпасть. Ни один запрос на этой странице не обнаружит эту пару как совпадение имени. Эта проблема называется разрешением сущностей: она решает, когда две записи, написанные по-разному, являются одной и той же реальной вещью. Свой собственный гид.

Итак, развилка: какие столбцы определяют «то же самое»? Все это означает, что вы охотитесь за несчастными случаями при погрузке. Естественный ключ означает, что вы ищете противоречивые версии. Ни то, ни другое не означает, что вы можете охотиться на людей, и это другой инструмент. Все, что ниже, работает для первых двух, а запросы отличаются только тем, что вы ставите после GROUP BY.

2. Десятисекундный тест на любом столе

Прежде чем объяснить: не оглядываясь на таблицу, как бы вы получили одно число, которое говорит о том, появляется ли какой-либо идентификатор дважды?

Посчитайте ряды двумя способами. COUNT(*) считает каждую строку. COUNT(DISTINCT customer_id) подсчитывает, сколько существует различных значений идентификаторов. Если каждый идентификатор появляется один раз, два числа совпадают. Любой разрыв между ними — это количество дополнительных копий.

SELECT COUNT(*)                    AS total_rows,
       COUNT(DISTINCT customer_id) AS distinct_ids
FROM customers;

Четырнадцать строк, одиннадцать различных идентификаторов, поэтому три строки — это дополнительные копии чего-то. Это запрос, который я выполняю для каждой таблицы, которую мне передают, перед любым соединением и перед любым отчетом, потому что он занимает около десяти секунд и меняет то, чему я доверяю. Он не сообщает вам, какие строки являются копиями. Оно говорит вам, нужна ли охота вообще, а 14 против 11 говорят, что да.

3. Перечислите дубликаты с помощью GROUP BY и HAVING.

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

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

SELECT customer_id, COUNT(*) AS copies
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Два покупателя, пять рядов между ними, трое из них статисты. Это как раз и объясняет пробел на втором этапе: 14 строк минус 11 идентификаторов — это 3, и вот они, две дополнительные копии 103 плюс одна дополнительная копия 105.

Чтобы вместо этого искать дубликаты всей строки, поместите каждый столбец в GROUP BY. Теперь сегмент формируется только тогда, когда строки совпадают во всем.

SELECT customer_id, email, full_name, city, signup_date,
       COUNT(*) AS copies
FROM customers
GROUP BY customer_id, email, full_name, city, signup_date
HAVING COUNT(*) > 1;

Обратите внимание, что изменилось. Клиент 105 исчез из этого результата, поскольку ее две строки различаются по городам, поэтому они никогда не попадают в одну и ту же корзину. Прежде чем читать дальше, скажите своими словами, почему эти два запроса расходятся по поводу 105. Если вы можете это объяснить, у вас есть весь метод: GROUP BY список — это ваше определение дубликата, записанное в виде кода.

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

Перед пояснением: в сводке говорится, что у клиента 105 есть две копии, но чтобы решить, какую оставить, вам нужно увидеть обе строки рядом. В сгруппированном результате их невозможно отобразить, поскольку группировка их свернула. Как их вернуть?

Используйте сводку в качестве списка покупок. Сначала найдите идентификаторы с дубликатами, затем извлеките каждую строку, идентификатор которой есть в этом списке. IN это самый простой способ сказать это.

SELECT *
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    GROUP BY customer_id
    HAVING COUNT(*) > 1
)
ORDER BY customer_id, signup_date;

Пять рядов, и теперь две проблемы выглядят совершенно разными. Клиент 103 — три одинаковые строки: авария при погрузке, нечего решать. Клиент 105 — это две версии одного человека: Даллас в феврале, Форт-Уэрт в марте, и кто-то должен сказать, какой город настоящий. Обратное соединение с той же сводкой дает идентичный результат, и я запустил оба варианта, чтобы убедиться: в любом случае одни и те же пять строк. Используйте то, что вам больше нравится; SQL-СОЕДИНЕНИЯ охватывает форму присоединения.

5. Отметьте хранителей и статистов с помощью ROW_NUMBER.

Перед пояснением: теперь вы можете видеть все пять повторяющихся строк. Какой столбец, добавленный в таблицу, позволит кому-либо еще действовать с ним, не переделывая вашу работу?

Номер копии. ROW_NUMBER() — это оконная функция, то есть она вычисляет значение для каждой строки, ничего не сворачивая. PARTITION BY customer_id перезапускает нумерацию для каждого клиента, и ORDER BY signup_date решает, кто получит номер один. Итак, копия номер 1 — ваш хранитель, а все, что выше 1, — лишнее, согласно записанному вами правилу.

SELECT customer_id, city, signup_date,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY signup_date
       ) AS copy_number
FROM customers
ORDER BY customer_id, copy_number;

Дублированные клиенты возвращаются под таким номером.

Каждый второй клиент просто получает копию номер 1. Оберните ее в подзапрос и сохраните. copy_number > 1 и у вас есть ровно три дополнительных ряда, которые я проверил, чтобы подтвердить: два дополнительных ряда для 103 и ряд Форт-Уэрта для 105.

Вот ответвление варианта «сохранить против удаления», потому что это реальное решение. Удаление дополнительных данных делает таблицу чистой, но уничтожает улики, и если ваше правило хранителя было неверным, правильная строка исчезнет. При выборе дополнительных элементов сохраняется каждая строка и добавляется столбец, в котором указано, какую из них следует использовать в отчетах. Маркировка стоит одного столбца. Удаление стоит возможности изменить свое мнение. Вот почему работающие аналитики сначала отмечают и редко удаляют: фильтруют отчеты по copy_number = 1 и вы получите все преимущества удаления без какого-либо риска. Обратите внимание, что в запросе видно правило маркировки: для 105 ORDER BY signup_date сделал новый ряд в Форт-Уэрте дополнительным. Если компания говорит о новых победах, переверните заказ на signup_date DESC и вратарь тоже переворачивается. Правило представляет собой код, поэтому его можно просматривать и оспаривать.

Семейство оконных функций идет гораздо дальше этого трюка. Оконные функции проходит через него правильно. Если вам нужен узор на пальцах, а не в закладках, введите запрос самостоятельно несколько раз в SQL-тренировка.

6. Схема удаления, указанная один раз, с ограждением

Иногда ответ действительно заключается в удалении строк, обычно в случае аварийной загрузки всей строки, например, клиента 103. Вот шаблон, один раз. В SQLite каждая строка имеет скрытую rowid, поэтому оставьте самый маленький rowid на каждого клиента и удалите остальные. Другие базы данных используют ctid, идентификатор системы или ROW_NUMBER подзапрос, но форма та же.

DELETE FROM customers
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM customers
    GROUP BY customer_id
);

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

• Сначала запустите его как SELECT. Изменять DELETE FROM customers к SELECT * FROM customers с тем же самым WHEREи прочитайте строки, которые умрут. На этой таблице показаны именно три статиста и ничего больше. Если выбор вас удивил, то удаление тоже произойдет, за исключением того, что удаление не позволяет вам смотреть дважды.
• Сохраните исходную таблицу. Выполните удаление копии или сначала запишите удаленные строки в боковую таблицу. Обратите внимание, что этот шаблон удаления сохраняет произвольную самую раннюю загруженную строку, а не выбранную, поэтому в случае конфликтов с одинаковыми ключами, таких как клиент 105, он может молча сохранять устаревший город. Именно поэтому маркировка стояла на первом месте.

7. Полное до и после

Та же таблица, тот же вопрос: у каких клиентов есть повторяющиеся строки, и какая копия должна стоять?

До

SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;

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

после

-- ============================================================
-- STEP 2: Which customer rows are duplicates, and which stands?
-- WHY: 14 rows but only 11 distinct customer_ids, so 3 rows
--      are extra copies. Reports must count customers once.
-- RULE: keeper = earliest signup_date per customer_id.
--      105's city conflict (Dallas vs Fort Worth) goes to the
--      data owner; nothing is deleted here.
-- ============================================================
--SELECT each customer row with its copy number:
--   ROW_NUMBER restarts at 1 for every customer_id,
--   ordered by signup_date, so copy_number 1 = keeper
SELECT customer_id, email, full_name, city, signup_date,
       ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY signup_date
       ) AS copy_number
FROM customers
ORDER BY customer_id, copy_number;

Присутствует каждая строка, записано правило хранителя, с которым рецензент может не согласиться, известный конфликт назван, а последующие запросы просто фильтруются, чтобы copy_number = 1. Формат комментариев тот же, что и у Как комментировать SQL, чтобы он обучал.

Теперь представьте, как выполняется запрос маркировки для вашей самой большой таблицы, секционированной по ее естественному ключу. Какой столбец вы бы поместили в ORDER BY принять решение о хранителе, и могли бы вы защитить этот выбор перед лицом, владеющим данными?

8. Крайние случаи, из-за которых поиск дубликатов идет не так, как надо

Перед объяснением: ваш дублирующийся запрос возвращает ноль строк, но следующий отчет по-прежнему учитывает клиентов дважды. Каким образом оба этих утверждения могут быть правдой одновременно?

Пять, каждая из которых стоила кому-то реального времени.

Вы сгруппировали по неправильному определению. Это ответ с нулевыми строками. Группировка по каждому столбцу не находит дубликатов в таблице, где один и тот же идентификатор встречается дважды в разных городах, точно так же, как клиент 105. Когда запрос всей строки возвращается чистым, запустите версию с естественным ключом, прежде чем объявлять таблицу чистой.

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

Близкие совпадения ускользают от каждой точной группы. Конечные пробелы, заглавные буквы и варианты написания — это разные значения. GROUP BY. На моем столе специально стоит один из них: Бен Ортис и Бенджамин Ортис. Группировка по электронной почте выявляет эту пару, и я запустил ее: ben.ortiz@mail.com показывает 2 копии под 2 разными идентификаторами. Но группировка по имени никогда не поможет, и истинное нечеткое сопоставление Разрешение объекта, а не больший GROUP BY.

NULL группируются вместе. Две строки с отсутствующим адресом электронной почты попадают в одну корзину, поскольку при группировке все NULL-значения рассматриваются как одно значение. Ведро заготовок со счетом 40 – это не 40 дубликатов. В 40 строках отсутствует ключ, и это отдельная находка, о которой стоит сообщить отдельно.

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