Как найти дубликаты в таблице с помощью SQL?
Чтобы найти дубликаты в таблице с помощью SQL, необходимо определить, по каким полям считается дублирование. Дубликатом считается строка, которая повторяется по значению одного или нескольких столбцов. Чаще всего дубликаты ищутся по одному или нескольким ключевым столбцам, но могут включать все поля.
Основная стратегия поиска дубликатов
-
Использовать GROUP BY, чтобы сгруппировать строки по нужным столбцам.
-
Применить агрегатную функцию COUNT(*) к каждой группе.
-
Использовать HAVING, чтобы отфильтровать только те группы, где количество строк больше одного.
Примеры
Найти дубликаты по одному столбцу (например, email)
SELECT email, COUNT(\*) AS count
FROM users
GROUP BY email
HAVING COUNT(\*) > 1;
Пояснение:
-
GROUP BY email — группирует строки по email.
-
COUNT(*) — подсчитывает количество строк в каждой группе.
-
HAVING COUNT(*) > 1 — отбирает только те группы, где есть дубликаты.
Найти дубликаты по нескольким столбцам (например, first_name, last_name, birth_date)
SELECT first_name, last_name, birth_date, COUNT(\*) AS count
FROM people
GROUP BY first_name, last_name, birth_date
HAVING COUNT(\*) > 1;
Найти полные дубликаты (все столбцы идентичны)
SELECT \*, COUNT(\*) AS count
FROM my_table
GROUP BY column1, column2, column3, column4
HAVING COUNT(\*) > 1;
Важно: В некоторых СУБД (например, PostgreSQL) для GROUP BY * нужно явно указать все столбцы, если они не аггрегированы.
Найти сами строки-дубликаты
Предыдущие примеры выводили только уникальные значения дубликатов, а не сами строки. Чтобы вывести все строки, соответствующие найденным дубликатам, можно использовать JOIN или подзапрос с IN.
Пример через JOIN
SELECT u.\*
FROM users u
JOIN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(\*) > 1
) dup ON u.email = dup.email;
Пример через IN
SELECT \*
FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(\*) > 1
);
Как удалить дубликаты (оставив только одну строку)
Удаление дубликатов требует осторожности. Есть несколько подходов:
С помощью CTE и ROW_NUMBER() (например, в PostgreSQL, SQL Server, Oracle)
WITH Ranked AS (
SELECT \*, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
)
DELETE FROM users
WHERE id IN (
SELECT id FROM Ranked WHERE rn > 1
);
Пояснение:
-
ROW_NUMBER() присваивает каждой строке номер в пределах группы email.
-
rn > 1 — дубликаты (все, кроме первой строки в группе).
-
Удаляются только повторные записи.
Примеры для других СУБД
MySQL с INNER JOIN
DELETE u1 FROM users u1
JOIN users u2
ON u1.email = u2.email AND u1.id > u2.id;
Этот запрос удаляет строку с более высоким id, если существует строка с тем же email и меньшим id.
Учет NULL в дубликатах
Важно понимать, что в SQL NULL <> NULL. То есть строки, в которых значение одного из столбцов равно NULL, не считаются равными друг другу при использовании =. В таких случаях:
- Используйте IS NULL или специальные функции (COALESCE, NVL, IFNULL).
Например:
python
SELECT name, COALESCE(phone, 'N/A') AS phone, COUNT(\*)
FROM contacts
GROUP BY name, COALESCE(phone, 'N/A')
HAVING COUNT(\*) > 1;
Использование индексов
Для ускорения поиска дубликатов имеет смысл создавать индексы на поля, по которым выполняется GROUP BY или JOIN. Например:
CREATE INDEX idx_email ON users(email);
Отладка и аудит дубликатов
Чтобы понять, откуда берутся дубликаты:
-
Сравнивайте поля по порядку (сначала по одному, потом по паре и так далее).
-
Смотрите created_at, source, user_agent — возможно, дубликаты приходят из разных каналов.
-
Можно использовать DISTINCT, чтобы оценить количество уникальных комбинаций.
Специальные средства в BI-инструментах
Если используется платформа анализа данных (например, Power BI, Tableau, Superset), дубликаты можно визуально отследить через отчёты с агрегацией и фильтрацией.
Инструментальные средства
В больших системах поиск дубликатов может быть автоматизирован:
-
С помощью триггеров на INSERT/UPDATE для контроля уникальности.
-
С помощью процедур очистки, выполняемых по расписанию.
-
Через системы качества данных (Data Quality Frameworks).
SQL даёт гибкий и мощный инструментарий для поиска дубликатов, начиная от простейшего GROUP BY, заканчивая оконными функциями и CTE. Выбор конкретной техники зависит от используемой СУБД, структуры таблицы и бизнес-требований к уникальности данных.