Выбор дублей в MySQL: сравнение нескольких столбцов с JOIN

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

Для выявления дублированных записей по столбцам col1, col2 применяют оператор GROUP BY к этим столбцам и выбирают записи с помощью условия HAVING COUNT(*) > 1. Так, можно отобразить записи, повторяющиеся более одного раза:

SELECT col1, col2, COUNT(*) as duplicates
FROM my_table
GROUP BY col1, col2
HAVING duplicates > 1;

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

Аккуратная работа с NULL значениями

NULL значения требуют специального учета. В SQL-запросах критически важно корректно обрабатывать NULL, чтобы избежать искажения результатов сравнения. В рамках операции GROUP BY NULL значения в столбцах рассматриваются, как отдельные частные случаи. Чтобы сгруппировать NULL значения как идентичные, используйте оператор NULL-safe сравнения <=> совместно с функцией COALESCE:

SELECT col1, col2, COUNT(*)
FROM my_table
GROUP BY COALESCE(col1, 'constant'), COALESCE(col2, 'constant')
HAVING COUNT(*) > 1;

Функция COALESCE заменяет NULL на указанную константу, что облегчает сравнение с NULL значениями.

Как обойти дубликаты: извлечение всех повторяющихся записей

Чтобы получить все дублирующиеся записи, сделайте самосоединение основной таблицы с подзапросом, полученным из GROUP BY:

SELECT a.*
FROM my_table a
JOIN (
    SELECT col1, col2
    FROM my_table
    GROUP BY col1, col2
    HAVING COUNT(*) > 1
) b ON a.col1 = b.col1 AND a.col2 = b.col2;

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

Оптимизация производительности: ускорение запросов

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

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

Как две одинаковые фотографии в фотоальбоме:

| Фото   | Дата     | Место    | 
| ------ | -------- | -------- | 
| 📷     | 01/01/23 | 🏞️       | 
| 📷     | 01/01/23 | 🏞️       |  <- Дубликат. Я думал, что потерял ключи, а они оказались в кармане 🤦
| 📷     | 02/01/23 | 🌆       |

Дубликаты — это идентичные "сцены" (SQL-записи), "снятые" в один и тот же день (по столбцам).

Продвинутые приемы: решение исключительных ситуаций

Отличие сходных: разные лиц дубликатов

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

SELECT col1, ROUND(col2, 1) as rounded_col2, COUNT(*)
FROM my_table
GROUP BY col1, ROUND(col2, 1)
HAVING COUNT(*) > 1;

Сложные дубликаты: цельная deduction

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

SELECT CONCAT(col1, '-', col2) as complex_criteria, COUNT(*)
FROM my_table
GROUP BY complex_criteria
HAVING COUNT(*) > 1;

Что делать с дубликатами: следующие шаги

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

SELECT a.*, 
       CASE 
           WHEN ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) = 1 
           THEN 'original' 
           ELSE 'duplicate' 
       END as dupe_status
FROM my_table a;

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

1. SQL GROUP BY – GeeksforGeeks — практическое руководство по освоению GROUP BY в SQL.
2. Инструкция по SQL GROUP BY – W3Schools — начальная подготовка специалиста SQL по группировке строк.
3. SQL GROUP BY | Продвинутый SQL – Mode — глубокое погружение в тему GROUP BY для пользователей среднего уровня.
4. SQL: JOINS – Tech on the Net — первичное знакомство с SQL JOINS, включая самосоединения для нахождения дубликатов.
5. Моделирование баз данных в Vertabelo – Поиск дубликатов в SQL — разнообразные подходы к поиску дублирующихся данных в SQL-таблицах.
6. Технические доклады – Percona — вебинары по оптимизации запросов MySQL для улучшения производительности и ускорения работы.