Управление дубликатами в SQL: подсчёт, пометка и удаление

Дубликаты в таблицах искажают аналитические выводы. В статье показаны практические способы: подсчёт с GROUP BY, пометка окнами row_number(), удаление через DISTINCT и CTE. Приведены рекомендации по производительности, чек‑листы ролей и готовые SQL‑фрагменты для разных сценариев.

Данные бывают разными, но дубликаты встречаются всегда. Независимо от источника — веб, CRM или экспорт из ERP — наличие повторяющихся строк исказит метрики: сумму продаж, средние значения, количество уникальных клиентов и т. д.

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

Основные понятия в одну строку

• Дубликат: строка или сочетание столбцов, которое повторяется в таблице.
• Агрегация: подсчёт, суммирование, усреднение по группам.
• Оконные функции: вычисления по «окну» строк, которые не сводят мотив информацию в одну строку.

Содержание

• Подходы к обнаружению дубликатов
• Подсчёт через GROUP BY
• Пометка с row_number()
• Удаление: DISTINCT и CTE
• Когда методы не подходят и альтернативы
• Рекомендации по производительности и безопасности
• Чек‑листы и тесты приёмки
• Краткая методология удаления дубликатов
• Глоссарий и контрольный список ролей

1. Подходы к обнаружению дубликатов

Выбор подхода зависит от цели:

• «Найти, сколько дубликатов» — GROUP BY с HAVING.
• «Пометить все повторяющиеся строки для последующей обработки» — оконные функции (row_number, rank, dense_rank).
• «Получить уникальные строки» — DISTINCT или CTE с row_number + фильтр.
• «Удалить повторяющиеся строки на постоянной основе» — DELETE с использованием окон или временной таблицы.

Ниже — примеры и пояснения.

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

SQL позволяет агрегировать данные и отобрать те значения, что встречаются несколько раз.

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

Предположим таблица с колонками ProductID и Orders. Чтобы посчитать, какие ProductID повторяются больше одного раза, используем GROUP BY и HAVING:

select productid, count(productid) as Total_count

from sahil.product_dups

group by productid

having count(productid) > 1

order by productid;

Разбор по шагам:

• SELECT productid, count(productid) — отображаем значение и число его появлений.
• FROM — источник данных.
• GROUP BY productid — сгруппировать строки по самому значению ProductID.
• HAVING count(productid) > 1 — показать только те группы, где размер больше 1.
• ORDER BY для удобочитаемости.

Результат очевиден: список ProductID с числом повторов.

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

Если «дубликат» определяется сочетанием колонок (например, ProductID + Orders), сгруппируйте по этим колонкам:

select productid, orders, count(*) as Total_count

from sahil.product_dups

group by productid, orders

having count(productid) > 1

order by productid;

Особенности:

• COUNT(*) подсчитывает повторяющиеся строки целиком (включая NULL в других столбцах), тогда как COUNT(column) считает только ненулевые значения указанной колонки.
• В примере будут показаны только те пары (productid, orders), которые встречаются более одного раза.

3. Пометка дубликатов с row_number()

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

select productid, orders,

row_number() over (partition by productid order by productid) as sno

from sahil.product_dups;

Пояснение:

• ROW_NUMBER() OVER (PARTITION BY productid ORDER BY productid) присваивает каждой строке в группе порядковый номер.
• PARTITION BY разделяет набор строк на группы по productid.
• ORDER BY внутри окна задаёт критерий, по которому определяется «первая» строка в группе. Это важно: если хотите сохранить строку с наибольшей датой или наибольшей суммой — используйте соответствующий ORDER BY.

Пример вывода показывает групповые номера — легко отфильтровать sno > 1 для выбора повторов.

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

Удаление требует осторожности: перед изменениями всегда делайте бэкап или работайте в транзакции (и проверяйте платформенные ограничения).

Использование DISTINCT для получения уникальных строк

SELECT DISTINCT полезен, когда нужно получить набор уникальных значений, но он не изменяет исходную таблицу сам по себе.

select distinct productid from sahil.product_dups;

Вернёт список уникальных ProductID.

Чтобы получить уникальные строки (по всем колонкам):

select distinct * from sahil.product_dups;

Это удобно для быстрых отчётов или вставки в новую таблицу с уникальными строками.

Удаление дубликатов через CTE и row_number

Common Table Expression (CTE) позволяет виртуально пронумеровать строки и затем удалить лишние, сохранив только одну копию.

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;

Этот пример возвращает только «первую» строку из каждой группы. Чтобы физически удалить повторяющиеся строки, используйте DELETE на базе CTE (синтаксис зависит от СУБД):

Пример для SQL Server / PostgreSQL:

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

Или альтернативно (в PostgreSQL):

with cte as (
  select ctid, row_number() over (partition by productid order by productid) as rn
  from sahil.product_dups
)
delete from sahil.product_dups
using cte
where sahil.product_dups.ctid = cte.ctid and cte.rn > 1;

Внимание: точный синтаксис удаления зависит от СУБД. В MySQL удаление по CTE доступно в новых версиях, а в старых можно создать временную таблицу.

5. Когда методы не подходят — примеры и альтернативы

• Когда «дубликат» определяется бизнес‑логикой (например, похожие имена, разные регистры, опечатки) — простые GROUP BY и DISTINCT не помогут. Нужны fuzzy matching и алгоритмы сравнения (Levenshtein, trigram, soundex).
• Если надо объединять и агрегировать данные по приоритету (например, оставить запись с самой свежей датой) — используйте оконную функцию с ORDER BY по дате.
• Для одновременного удаления и сохранения связей (FK) — сначала создайте новую таблицу с уникальными строками, обновите внешние ключи, затем удалите старую таблицу.

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

• ETL‑процессы: очистка до загрузки (pre‑ingest dedup) предотвращает накопление мусора.
• Специализированные инструменты для MDM (master data management) при работе с критичными справочниками.

6. Производительность и лучшие практики

• Индексы: группировка и фильтрация по индексированным колонкам выполняется быстрее. Однако индексирование большого количества колонок увеличит нагрузку на запись.
• Ограничивайте количество колонок в GROUP BY — чем меньше данных нужно агрегировать, тем быстрее.
• Для больших таблиц (> сотен тысяч — миллионы строк) предпочитайте подход «создать новую таблицу с уникальными строками» вместо массового DELETE, чтобы избежать долгих блокировок:
  1. CREATE TABLE new AS SELECT DISTINCT …;
  2. Проверить данные;
  3. Переименовать таблицы.
• Всегда выполняйте операции в транзакции или работайте на копии данных, если возможны ошибки.
• Логи и аудит: сохраняйте информацию о том, какие строки были удалены/объединены и почему.

Важно: для OLTP систем массовые DELETE могут приводить к проблемам с производительностью и росту файлов журналов транзакций. Планируйте окна обслуживания.

7. Мини‑методология удаления дубликатов (шаги)

1. Анализ: определите, что считать дубликатом (один столбец, набор столбцов, бизнес‑правила).
2. Извлечение: выберите примеры подозрительных групп (LIMIT 1000) и вручную проверьте.
3. Пометка: используйте ROW_NUMBER() и сохраните результаты в staging‑таблице.
4. Правила: решите, какие строки сохранять (по дате, по заполненности полей, по приоритету источника).
5. Удаление/слияние: выполните операцию на копии или в транзакции.
6. Валидация: запустите тесты целостности и сверку агрегатов.
7. Мониторинг: добавьте ежедневную проверку на новые дубликаты.

8. Чек‑листы (роль‑ориентированные)

Чек‑лист для аналитика данных:

• Я знаю, какие поля определяют уникальность.
• Проверил частоту повторов (GROUP BY + HAVING).
• Пометил повторы и просмотрел выборку вручную.

Чек‑лист для инженера данных:

• Предусмотрел бэкап перед массовыми операциями.
• Планировал операции в окне обслуживания.
• Обновил индексы по итоговой таблице.

Чек‑лист для администратора БД:

• Оценил влияние на транзакционные логи и место на диске.
• Настроил блокировки/параметры выполнения.
• Проверил зависимости FK и триггеры.

9. Тесты приёмки (acceptance) и примеры сценариев

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

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

Примеры тесткейсов:

• TC1: Удаление дубликатов среди 1000 строк — скорость выполнения менее X (опционально измеряется).
• TC2: Проверка, что для каждой группы productid остаётся ровно одна строка после удаления.
• TC3: Проверка, что строки с максимальной датой в группе сохраняются при приоритезации по дате.

10. Риски и смягчающие меры

Риски:

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

Меры:

• Всегда работать на копии или в транзакции.
• Логировать изменения и сохранять архив удалённых строк.
• Тестировать на выборках перед запуском в продакшене.

11. Примеры расширенных сценариев и шаблоны запросов

1. Сохранить строку с наибольшей суммой в повторяющейся группе:

with ranked as (
  select *, row_number() over (partition by productid order by total_amount desc) as rn
  from sahil.product_dups
)
select * from ranked where rn = 1;

2. Слияние похожих значений (фаззи) — псевдо‑подход:

• Используйте техники нормализации текста (trim, lower, replace), затем trigram/levenshtein для поиска вероятных совпадений.
• Результаты ручной проверки перед массовым объединением.

3. Мягкое удаление (soft delete) для критичных таблиц:

• Добавьте колонку is_deleted boolean, помечайте дубликаты как удалённые, не удаляйте физически.

12. Модель принятия решений (flowchart)

flowchart TD
  A[Начало: обнаружены возможные дубликаты] --> B{Определён ли критерий уникальности?}
  B -- Нет --> C[Определить бизнес‑правила]
  C --> D
  B -- Да --> D[Выбрать метод]
  D --> E{Нужно ли сохранить одну строку из группы?}
  E -- Да --> F[Использовать ROW_NUMBER и фильтр rn=1]
  E -- Нет --> G[Использовать DISTINCT или GROUP BY]
  F --> H{Таблица большая?}
  G --> H
  H -- Да --> I[Создать новую таблицу с уникальными строками]
  H -- Нет --> J[Использовать DELETE с CTE]
  I --> K[Проверить, переименовать, удалить старую]
  J --> K
  K --> L[Валидация и мониторинг]
  L --> M[Конец]

13. Частые ошибки и когда методы терпят неудачу

• Ожидание, что DISTINCT исправит «похожесть» данных (например, «Иванов» vs «иванов»); DISTINCT чувствителен к регистру и пробелам.
• Удаление без учёта зависимостей FK — приводит к нарушенной целостности данных.
• Использование ORDER BY productid в окне без явного правила приоритета — может удалить нужную строку.

14. Краткий справочник (1‑строчная глоссарий)

• DISTINCT — возвращает уникальные строки.
• GROUP BY — объединяет строки по значению столбца(ов) для агрегации.
• HAVING — фильтрует сгруппированные результаты.
• ROW_NUMBER() — нумерует строки в пределах окна.
• CTE — временная виртуальная таблица для упрощения сложных запросов.

15. Краткие рекомендации по внедрению в рабочий процесс

• Автоматизируйте проверки на дубликаты в ETL: ежедневные джобы, которые логируют новые совпадения.
• Документируйте правила уникальности в Data Dictionary.
• При вводе данных — валидация на уровне приложения и базы данных (unique constraints) там, где это возможно.

Итог

Дубликаты — обычная, но решаемая проблема. Для простых случаев GROUP BY/HAVING и DISTINCT достаточно. Когда нужна гибкость — используйте оконные функции и CTE. Для больших объёмов данных планируйте операции аккуратно: используйте копии, транзакции, индексы и тесты. Внедрите автоматическую проверку и документируйте правила уникальности, чтобы дубликаты не возвращались снова.

Важно

• Всегда сохраняйте резервную копию перед массовыми удалениями.
• Для чувствительных данных рассмотрите мягкое удаление (is_deleted) и аудит.

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

• Определите правило уникальности.
• Пометьте повторы с ROW_NUMBER()
• Удаляйте аккуратно (CTE, новая таблица).
• Тестируйте и мониторьте.