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

Практика наложения UNIQUE-ограничений — лучший способ предотвратить дубликаты. Но при работе с унаследованными или «грязными» данными иногда нужно сначала найти и удалить уже существующие дубликаты вручную.

Ниже — пример, демонстрация и рекомендации, которые помогут безопасно обнаружить и удалить повторяющиеся записи в SQL.

Создание примерной базы данных

Для демонстрации создайте таблицу Users с полями pk_id, name и score. Запросы SQL не переводятся — их нужно выполнить в вашей СУБД.

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 или в комбинации (name, score).

Поиск дубликатов с помощью GROUP BY

GROUP BY группирует строки по одним и тем же значениям. Если нужно найти повторяющиеся значения по одному столбцу (например, name), используйте:

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

Этот запрос вернёт имена, которые встречаются более одного раза (в нашем примере — John и Jane).

Если интересует дубликат по нескольким колонкам, перечислите их в GROUP BY и в SELECT.

Удаление дубликатов безопасно с помощью CTE и ROW_NUMBER

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

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY
                name, score
            ORDER BY
                name, score
        ) AS R
    FROM
        Users
)

DELETE FROM Users
WHERE pk_id IN (
    SELECT pk_id FROM cte WHERE R > 1
);

Пояснения:

• PARTITION BY разделяет набор строк на группы с одинаковыми значениями (здесь name и score).
• ROW_NUMBER() нумерует строки внутри каждой группы; строки с R > 1 считаются дубликатами.
• Удаление выполняется через ключи (pk_id), чтобы избежать несовместимостей с разными СУБД, где прямой DELETE FROM CTE может не поддерживаться.

Important: всегда делайте резервную копию или запускайте DELETE внутри транзакции и проверяйте результат через SELECT перед коммитом.

Когда такой подход не подходит

• Когда нет первичного ключа или уникального идентификатора — безопасное удаление затруднено. Тогда сначала добавьте временный уникальный идентификатор.
• Когда дубликаты частично корректны и требуется слияние (merge) данных из разных строк — удаление без агрегации приведёт к потере информации.
• На очень больших таблицах прямой DELETE может быть слишком тяжёлым для транзакции — используйте пакетное (batch) удаление.

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

• Использовать DISTINCT INTO для создания новой таблицы без дубликатов, затем переименовать таблицы.
• Использовать агрегирующие функции (MIN/MAX) для выбора «лучшей» строки в каждой группе и затем обновить/удалить остальные.
• В некоторых СУБД есть специальные операторы MERGE/UPSERT или инструменты для дедупликации (например, в PostgreSQL — DISTINCT ON).

Пошаговая методология (мини‑метод)

1. Проанализировать, какие поля определяют дубликат (один столбец или комбинация).
2. Выполнить SELECT с GROUP BY и HAVING COUNT>1, чтобы получить список групп-дубликатов.
3. Просмотреть примеры дубликатов (LIMIT/выборка) и принять решение, какую строку оставить.
4. Подготовить DELETE через ключи (идентификатор), тестировать в транзакции.
5. Выполнить удаление пакетами при больших объёмах.
6. Добавить UNIQUE-ограничение или индекс, чтобы предотвратить повторное появление.

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

DBA:

• Сделать бэкап перед изменениями.
• Оценить размер таблицы и влияние на репликацию.
• Планировать удаление в низкую нагрузку.

Разработчик:

• Написать тесты для сценариев вставки дубликатов.
• Обеспечить уникальные ограничения при необходимости.

Аналитик данных:

• Выяснить, какие поля можно считать эталонными при слиянии записей.
• Подготовить отчёт по потерянным/объединённым данным.

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

• Для каждой группы, указанной в предварительном SELECT, осталось ровно 1 строка.
• Совокупный объём данных после удаления уменьшился ожидаемо, без потери нужной информации.
• Тестовые запросы (см. раздел Тестовые случаи) проходят локально и в тестовой среде.

Тестовые случаи и контроль качества

• Запустить SELECT с GROUP BY до удаления и зафиксировать количество групп и строк.
• Выполнить DELETE в транзакции, проверить количество удалённых строк, откатнуть.
• Применить DELETE на копии таблицы и сравнить результаты с исходным набором.
• Проверить индексы и планы выполнения после удаления (влияние на производительность).

Небольшой набор команд/шпаргалка

• Найти дубликаты по одному полю: SELECT field, COUNT() FROM table GROUP BY field HAVING COUNT() > 1;
• Удалить дубликаты, оставив первую вставленную: использовать ROW_NUMBER() OVER (PARTITION BY cols ORDER BY created_at) и удалить R>1.
• Создать новую таблицу без дубликатов: CREATE TABLE new AS SELECT DISTINCT * FROM old;

1‑строчный глоссарий

• CTE — Common Table Expression, временная именованная результирующая таблица внутри запроса.
• ROW_NUMBER — оконная функция, нумерует строки внутри окна/группы.
• PARTITION BY — часть оконной функции, отделяет группы для оконных вычислений.

Конфиденциальность и безопасность

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

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

Удаление дубликатов повышает производительность и освобождает место, но требует осторожности: сначала анализируйте, тестируйте и делайте бэкапы. Используйте GROUP BY для поиска и CTE + ROW_NUMBER для безопасного удаления через ключи.

Сводка действий:

• Найдите дубликаты через GROUP BY.
• Подготовьте DELETE через идентификаторы, используя CTE + ROW_NUMBER().
• Тестируйте и защищайте операцию бэкапами и транзакциями.