Как найти и удалить дубликаты в SQL

Найдите дубликаты с помощью GROUP BY или оконных функций (ROW_NUMBER()), предварительно просмотрев строки, которые будут удалены. Удаляйте в транзакции, делайте резервную копию и адаптируйте запрос под диалект СУБД (Postgres, MySQL, SQL Server).

Введение

Дубликаты в таблицах появляются из-за ошибок импорта, отсутствующих ограничений UNIQUE или багов в приложениях. Их наличие увеличивает объём данных, замедляет запросы и искажает отчёты. В этом руководстве показано, как безопасно найти и удалить дубликаты в SQL, с примерами для разных СУБД и практическими советами по откату и тестированию.

Краткое определение терминов

• CTE: выражение WITH, временная именованная выборка внутри одного запроса.
• ROW_NUMBER(): оконная функция, нумерующая строки в пределах раздела.

Создание тестовой базы

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

DROP TABLE IF EXISTS Users;

CREATE TABLE Users (
    pk_id INT PRIMARY KEY,
    name VARCHAR(16),
    score INT
);

INSERT INTO Users(pk_id, name, score) VALUES
    (1, 'Jane', 20),
    (2, 'John', 13),
    (3, 'Alex', 32),
    (4, 'John', 46),
    (5, 'Jane', 20),
    (6, 'Mary', 34),
    (7, 'Jane', 20),
    (8, 'John', 13);

В колонке name есть повторы: John и Jane встречаются несколько раз, иногда с той же связкой score, иногда с разными оценками.

Найти дубликаты с GROUP BY

Если нужно обнаружить значения, которые повторяются по одному или нескольким столбцам, используйте GROUP BY + HAVING.

SELECT name, COUNT(*) AS cnt
FROM Users
GROUP BY name
HAVING COUNT(*) > 1;

Этот запрос вернёт имена, которые встречаются более одного раза. Для комбинации столбцов:

SELECT name, score, COUNT(*) AS cnt
FROM Users
GROUP BY name, score
HAVING COUNT(*) > 1;

Предварительный просмотр строк, которые будут удалены

Перед удалением полезно увидеть именно те строки, которые считаются дубликатами. Оконная функция ROW_NUMBER позволяет пронумеровать дубли в каждой группе и выбрать все, кроме первой (или другой логики выбора).

SELECT *,
       ROW_NUMBER() OVER (PARTITION BY name, score ORDER BY pk_id) AS r
FROM Users;

Чтобы увидеть строки, планируемые к удалению (r > 1):

WITH marked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name, score ORDER BY pk_id) AS r
  FROM Users
)
SELECT * FROM marked WHERE r > 1;

Всегда смотрите результат этого SELECT перед DELETE.

Удаление дубликатов: варианты по СУБД

Ниже — безопасные шаблоны для разных диалектов SQL. Перед выполнением: сделайте бэкап, выполните SELECT-просмотр и запуск в транзакции.

Postgres (рекомендуемый вариант):

WITH marked AS (
  SELECT pk_id, ROW_NUMBER() OVER (PARTITION BY name, score ORDER BY pk_id) AS r
  FROM Users
)
DELETE FROM Users u
USING marked m
WHERE u.pk_id = m.pk_id AND m.r > 1;

SQL Server (можно удалять прямо из CTE):

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name, score ORDER BY pk_id) AS r
  FROM Users
)
DELETE FROM cte WHERE r > 1;

MySQL 8+ (поддерживает CTE и оконные функции):

WITH marked AS (
  SELECT pk_id, ROW_NUMBER() OVER (PARTITION BY name, score ORDER BY pk_id) AS r
  FROM Users
)
DELETE FROM Users
WHERE pk_id IN (SELECT pk_id FROM marked WHERE r > 1);

MySQL 5.7 и ниже (без оконных функций): используйте временную таблицу или самосоединение:

-- Самосоединение: сохраняйте меньший pk_id как «оригинал»
DELETE u1 FROM Users u1
INNER JOIN Users u2
  ON u1.name = u2.name AND u1.score = u2.score
WHERE u1.pk_id > u2.pk_id;

Важно: в самосоединении логика pk_id > pk_id оставит строку с наименьшим pk_id — адаптируйте под бизнес-логику.

Безопасность, тестирование и откат

• Всегда делайте бэкап перед массовыми удалениями.
• Выполните SELECT-просмотр и подсчёт строк до и после.
• Оборачивайте удаление в транзакцию, чтобы иметь возможность отката:

BEGIN TRANSACTION;
-- DELETE / UPDATE ...
-- проверка
ROLLBACK; -- или COMMIT;

• На больших таблицах используйте пакетную обработку (batch delete) или создайте новую таблицу с уникальными строками, затем переименуйте.

Когда метод не подойдёт

• Если дубликат определяется не по очевидным полям (нужна нормализация, fuzzy matching). В таких случаях применяют алгоритмы нечеткого сравнения (Levenshtein, soundex) и ручную валидацию.
• Если требуются бизнес-правила для выбора «правильной» строки (например, самая новая запись или последняя по активности). Тогда вместо простого ROW_NUMBER выбирайте ORDER BY по нужному полю.

Альтернативные подходы

• Создать временную таблицу с SELECT DISTINCT или агрегированием, удалить старую и переименовать.
• Добавить ограничение UNIQUE после очистки, чтобы предотвратить будущие дубликаты.
• Выполнить дедупликацию в ETL-процессе при импорте данных.

Критерии приёмки

• Количество строк до и после совпадает с ожидаемым (удалено N строк).
• Тестовые отчёты и ключевые запросы возвращают корректные результаты.
• Уникальные ограничения установлены, если это требование системы.
• Проведён бэкап и есть план отката.

Чеклист по ролям

DBA

• Сделать бэкап
• Запустить выборочные проверки производительности после удаления
• Настроить уникальные индексы при необходимости

Разработчик

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

Аналитик

• Сверить отчёты до и после удаления
• Проверить ключевые KPI на предмет изменений

Дополнительные рекомендации

• Для больших таблиц рассмотрите стратегию: создать новую таблицу с агрегированными/уникальными строками, затем переключить имена таблиц — это часто быстрее и безопаснее, чем массовые DELETE.
• Документируйте причину удаления дубликатов и дату операции для аудита.

Примечания по безопасности данных

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

Краткое резюме

Удаление дубликатов в SQL — стандартная задача: сначала найдите дубликаты с GROUP BY или ROW_NUMBER, затем удаляйте безопасно (бэкап, транзакции, предварительный просмотр). Выбор конкретного запроса зависит от СУБД и бизнес-логики.

Важно: протестируйте каждый шаг на копии данных и фиксируйте изменения для отката.