SQL запрос на поиск дубликатов по нескольким полям

Быстрый ответ

Чтобы обнаружить дубликаты, достаточно применить GROUP BY к нужным полям и HAVING, чтобы отфильтровать группы с количеством записей больше одной.

SELECT field1, field2, COUNT(*)
FROM table
GROUP BY field1, field2
HAVING COUNT(*) > 1;

В этом запросе мы группируем записи при помощи полей field1 и field2, выбираем те группы, в которых количество записей превышает одну — тем самым определяем дубликаты.

Продвинутое решение проблемы дубликатов

Борьба с упрямыми дубликатами

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

SELECT *
FROM table t1
WHERE EXISTS (
    SELECT 1 
    FROM table t2 
    WHERE t1.field1 = t2.field1 AND 
          t1.field2 = t2.field2 AND 
          t1.id > t2.id
);

Подробнее об этом расскажет наш спикер на видео

Управление дубликатами при помощи оконных функций

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

SELECT field1, field2, field3,
       RANK() OVER(PARTITION BY field1, field2, field3 ORDER BY id) AS rank
FROM table;

Обратите внимание, что null-значения могут искажать результаты проверки на дубликаты, поэтому с ними следует обращаться с особым вниманием.

Визуализация

Можно представить дубликаты в базе данных как излишние бутылки рома в погребе пирата:

🍾🍾🍷🍾🍸🍾🍷🍾🍸...

Каждая бутылка — это строка данных. Ищем мы дубликаты в группировке бутылок (строк) по определенным характеристикам.

SELECT field1, field2, COUNT(*)
FROM your_table
GROUP BY field1, field2
HAVING COUNT(*) > 1;

Таким запросом мы группируем бутылки рома по сортам и считаем их количество.

Работа со специфическими сценариями дублирования

Сохранение одного из дубликатов

Периодически нужно оставить один экземпляр дубликата, например, для анализа или ведения истории изменений.

SELECT MIN(id) as UniqueID, field1, field2
FROM table
GROUP BY field1, field2

Применение Общих Табличных Выражений (CTE)

CTE помогает структурировать запросы и придает им понятность, подобно тому как карта ведет к сокровищам.

WITH DuplicateRecords AS (
    SELECT field1, field2, field3, 
           ROW_NUMBER() OVER (PARTITION BY field1, field2, field3 ORDER BY id) AS row_num
    FROM table
)
SELECT * FROM DuplicateRecords
WHERE row_num > 1;

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

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

Сортировка

Использование ORDER BY необходимо для поддержания уникальности результатов.

Проверка уникальных составных ключей

Это аналогично распознаванию пирата по полному имени, а не по прозвищу.

Полезные материалы

1. SQL GROUP BY Statement — руководство по использованию GROUP BY и HAVING.
2. SQL SELECT DISTINCT Statement — описание ключевого слова DISTINCT.
3. SQL Server Common Table Expression (CTE) Basics — руководство по использованию CTE.
4. SQL Window Functions | Advanced SQL — использование оконных функций в SQL.
5. Just a moment... — обсуждение различных стратегий поиска дубликатов на Stack Overflow.