Как находить и удалять дубликаты в SQL

Введение

Данные бывают разных форм, но дубликаты присутствуют почти в любом наборе данных. Независимо от того, работаете ли вы с веб-логами или большим массивом продаж, дублированные записи искажут агрегаты и отчёты. Этот материал подробно объясняет, как находить, помечать и удалять дубликаты в SQL, с практическими примерами и рекомендациями по производительности и проверкам.

Основные категории задач

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

Короткое определение: дубликат — это запись, у которой совпадают значения в наборе столбцов, определённых как «ключ дубликата».

1. Подсчёт дубликатов через GROUP BY

SQL предоставляет мощные агрегирующие функции. Для обнаружения повторяющихся значений в одном или нескольких столбцах удобно использовать GROUP BY вместе с HAVING.

Подсчёт дубликатов в одном столбце

Предположим, есть таблица с колонками ProductID и Orders.

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

select productid, count(productid) as Total_count
from sahil.product_dups
group by productid
having count(productid) > 1
order by productid;

Пояснение шагов:

• select выбирает столбец и агрегатную метрику count.
• from указывает источник данных.
• group by группирует по ProductID, чтобы агрегат считался на группу.
• having фильтрует группы, оставляя только те, у которых count > 1.
• order by упорядочивает результат.

Примечание. GROUP BY даёт итог по группе — полезно, когда важен именно подсчёт уникальных значений.

Подсчёт дубликатов в нескольких столбцах

Если дубликатом считается сочетание нескольких столбцов (например, ProductID+Orders), добавьте их в GROUP BY:

select productid, orders, count(*) as Total_count
from sahil.product_dups
group by productid, orders
having count(productid) > 1
order by productid;

Здесь count(*) считает количество строк в группе. Этот запрос вернёт группы строк, где совпадают одновременно значения productid и orders.

Когда использовать GROUP BY

• Нужно быстро получить распределение частот по ключу.
• Нужен отчёт по числу дубликатов.

Ограничения GROUP BY

• Не даёт индивидуальных идентификаторов для строк (нельзя однозначно выбрать «первую» из дубликатов без дополнительной логики).
• При больших текстовых полях или многих столбцах агрегирование может быть медленным.

2. Пометка дубликатов через ROW_NUMBER()

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

select productid, orders,
row_number() over (partition by productid order by productid) as sno
from sahil.product_dups;

Принцип работы:

• partition by productid разделяет строки на группы по ProductID.
• row_number() присваивает каждой строке в группе порядковый номер: 1, 2, 3…
• Можно фильтровать по sno = 1, чтобы оставить только первую строку в каждой группе.

Преимущества оконного подхода

• Позволяет явно выбрать «какую» из дублирующихся строк сохранять (например, по дате или по приоритету).
• Подходит для непосредственного удаления с использованием CTE.

Когда partition не нужен

• Если вы хотите просто пронумеровать все строки без группировки, то partition опускается — но тогда row_number не помогает для дубликатов.

3. Удаление дубликатов из таблицы

Удалять дубликаты нужно аккуратно: сохранить корректные строки, минимизировать блокировки и иметь возможность отката.

a. DISTINCT для вывода уникальных значений

select distinct удобен для быстрых выборок уникальных значений без изменения таблицы.

select distinct productid from sahil.product_dups;

Для выдачи уникальных строк:

select distinct * from sahil.product_dups;

Этот подход удобен для проверки и превью, но не изменяет исходную таблицу.

Ограничения DISTINCT

• DISTINCT сравнивает все колонки в выборке; если таблица большая и содержит BLOB/TEXT поля, операция может быть тяжёлой.
• Не даёт гибкости по выбору «какой» из дубликатов оставить.

b. Удаление дубликатов через CTE и ROW_NUMBER()

CTE (Common Table Expression) удобно комбинируется с оконными функциями для безопасного удаления, оставляя одну строку на группу.

with cteproducts as
(select productid, orders,
row_number() over (partition by productid order by productid) as sno
from sahil.product_dups)
select * from cteproducts
where sno = 1;

Чтобы удалить дубликаты в самой таблице, в СУБД, поддерживающей CTE в DML, можно сделать так:

with cte as (
  select *, row_number() over (partition by productid order by created_at) as rn
  from sahil.product_dups
)
delete from sahil.product_dups
where (productid, orders) in (
  select productid, orders from cte where rn > 1
);

Или в системах, где DELETE поддерживает ссылку на CTE:

with cte as (
  select rowid as rid, row_number() over (partition by productid order by created_at desc) as rn
  from sahil.product_dups
)
delete from sahil.product_dups
where rowid in (select rid from cte where rn > 1);

Важные рекомендации при удалении

• Всегда выполняйте бэкап или копию таблицы перед крупной операцией удаления.
• Выполняйте операцию в транзакции, если СУБД это поддерживает, чтобы можно было откатить изменения.
• Тестируйте запросы SELECT, которые возвращают то, что вы собираетесь удалить, прежде чем запускать DELETE.

4. Альтернативные способы удаления и их применение

• Создать новую таблицу с уникальными строками и затем атомарно заменить старую таблицу (swap). Подходит для очень больших таблиц, когда массовый DELETE долго держит блокировки.

create table product_dups_new as
select distinct * from sahil.product_dups;
-- проверить -> переименовать таблицы в атомарной операции

• Добавить уникальный индекс или ограничение UNIQUE на набор столбцов после очистки, чтобы предотвратить повторное появление дубликатов.
• Использовать агрегатную логику (например, GROUP BY + aggregate) для консолидирования значений вместо удаления.

5. Практические советы и уязвимости

• NULL-значения. В разных СУБД NULL считается равным NULL при сравнении в контексте UNIQUE-ограничений по-разному. Явно обрабатывайте NULL (например, COALESCE).
• Коллация и регистрозависимость. Строки ‘abc’ и ‘ABC’ могут считаться разными. Приводите к одному регистру при сравнении: LOWER(column).
• Пробелы и невидимые символы. Используйте TRIM() и нормализацию.
• Порядок выбора при удалении. Определите критерий «какую» строку оставить: по created_at, по updated_at, по флагу is_primary и т. п.
• Индексы. При больших таблицах наличие подходящих индексов ускоряет поиск дубликатов, но массовые deletes могут приводить к фрагментации индексов.
• Блокировки. Массовые операции удаления могут вызвать долгие блокировки. Разбивайте удаление на чанки:

-- псевдокод для удаления порциями
delete from sahil.product_dups
where id in (
  select id from sahil.product_dups
  where <условие дубликата>
  limit 10000
);

6. Когда подходы не работают

Counterexamples:

• DISTINCT не подойдёт, если вам нужно сохранить конкретную версию строки (например, самую свежую по времени).
• ROW_NUMBER() без надёжного ORDER BY не детерминирует, какую именно строку сохранить.
• Создание новой таблицы не всегда возможно при нагрузке на диск или ограничениях по правам.

7. Пошаговая методика проверки и очистки (минимальная методология)

1. Определите набор столбцов, которые определяют «дубликат» (ключ дубликата).
2. Выполните SELECT с GROUP BY или оконной функцией, чтобы оценить масштаб дубликатов.
3. Решите стратегию: пометка (флаг), удаление или консолидация.
4. Тестируйте запросы на непроизводственной копии данных.
5. Создайте бэкап или снимок таблицы.
6. Выполните удаление/консолидацию в транзакции (или по чанкам).
7. Добавьте ограничения/индексы, чтобы предотвратить регресс.
8. Мониторьте данные и прогоняйте проверки на регулярной основе.

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

Data Engineer

• Определил ключ дубликата.
• Выполнил нагрузочное тестирование запросов.
• Создал бэкап и план отката.
• Минимизировал время блокировок (удалял пакетами).
• Добавил уникальные индексы/ограничения после очистки.

Аналитик

• Подтвердил критерии того, какую строку считать «правильной».
• Протестировал агрегаты до и после очистки.
• Проверил результаты выборок на предмет потери данных.

DBA

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

9. Кейсы тестирования и критерии приёмки

Тестовые сценарии:

• Для заданной таблицы с искусственными дубликатами запрос на подсчёт должен вернуть N групп с count > 1.
• После удаления по алгоритму CTE каждая комбинация ключевых столбцов должна быть уникальной.
• Откат транзакции должен вернуть таблицу в исходное состояние при симуляции ошибки.

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

• Количество строк уменьшилось ожидаемо и агрегаты (SUM/AVG) скорректировались согласно ожиданиям.
• Запросы на уникальность (SELECT productid, count() FROM table GROUP BY productid HAVING count() > 1) возвращают пустой набор.
• Лог операций содержит информацию о выполненных действиях и подтверждение успешной валидации.

10. Шпаргалка / Cheat sheet

• Найти дубликаты по колонке:

  select key, count(*) as c from table group by key having count(*) > 1;

• Пометить дубликаты и оставить одну строку:

  with cte as (
  select *, row_number() over (partition by key order by created_at desc) rn
  from table
  )
  select * from cte where rn = 1;

• Удалить дубликаты по row_number:

  with cte as (
  select id, row_number() over (partition by key order by created_at desc) rn
  from table
  )
  delete from table where id in (select id from cte where rn > 1);

• Быстрая консервация через новую таблицу:

  create table table_clean as select distinct * from table;
  -- проверить, затем swap/rename

11. Дерево принятия решения

graph TD
  A[Есть дубликаты?] -->|Нет| B[Остановиться]
  A -->|Да| C[Нужна ли конкретная строка?]
  C -->|Да| D[Использовать ROW_NUMBER + ORDER BY]
  C -->|Нет| E[Использовать DISTINCT или GROUP BY]
  D --> F{Таблица большая?}
  E --> F
  F -->|Да| G[Создать новую таблицу -> swap]
  F -->|Нет| H[Удалить/Обновить в месте]

12. Дополнительные рекомендации по производительности

• Проводите операции удаления в ночное время или в окне низкой нагрузки.
• Разбейте удаление на партии (batch delete) для снижения времени удержания блокировок.
• После массовых DELETE выполните VACUUM/OPTIMIZE/TABLE ANALYZE в зависимости от СУБД.
• Рассмотрите использование партиционирования таблицы, чтобы локализовать поиск дубликатов.

13. Частые ошибки и как их избежать

• Удаление без бэкапа. Всегда делайте копию или снимок перед критичными операциями.
• Полагание на неопределённый ORDER BY в оконных функциях. Всегда указывайте однозначный критерий порядка.
• Ожидание одинакового поведения разных СУБД. Тестируйте в целевой среде.

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

• Используйте GROUP BY для быстрого подсчёта повторов по ключу.
• ROW_NUMBER() + CTE даёт гибкость при выборе, какую строку сохранить.
• DISTINCT полезен для превью, но не для выбора «правильной» из дубликатов.
• Для больших таблиц предпочтительнее создать новую таблицу с уникальными записями и затем заменить старую.
• Всегда делайте бэкап, тестируйте запросы и добавляйте уникальные ограничения для предотвращения регрессий.

Важно: адаптируйте стратегию под объём данных, требования по доступности и особенности конкретной СУБД.

Итоги:

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