Удаление дубликатов строк

Через некоторое время в большинстве приложений появляются дублированные строки, что приводит к ухудшению качества работы пользователей, повышению требований к хранению данных и снижению производительности базы данных. Процесс очистки обычно реализуется в коде приложения со сложным поведением фрагментации, поскольку данные не помещаются в память полностью. Однако один SQL-запрос может выполнить весь процесс, включая определение приоритетов строк и количества дубликатов, которые необходимо оставить.

Использование

MySQL

WITH duplicates AS (
  SELECT id, ROW_NUMBER() OVER(
    PARTITION BY firstname, lastname, email
    ORDER BY age DESC
  ) AS rownum
  FROM contacts
)
DELETE contacts
FROM contacts
JOIN duplicates USING(id)
WHERE duplicates.rownum > 1

PostgreSQL

WITH duplicates AS (
  SELECT id, ROW_NUMBER() OVER(
    PARTITION BY firstname, lastname, email
    ORDER BY age DESC
  ) AS rownum
  FROM contacts
)
DELETE FROM contacts
USING duplicates
WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

Подробное объяснение

Каким бы качественным ни было приложение, через некоторое время в нем могут появиться дубликаты строк. Поначалу они могут не представлять большой проблемы. Однако при многократном появлении дубликатов строк быстро ухудшается качество работы пользователя, а производительность базы данных снижается из-за увеличения объёма данных. Кроме того, эффективный уникальный индекс, сообщающий базе данных, что поиск можно прекратить после того, как будет найден первая строка, уже не может быть использован. Эти дублирующиеся строки должны быть удалены. Если вставить их было просто, то удалить — гораздо более сложная задача.

Стандартный подход заключается в том, чтобы GROUP BY на дублирующихся столбцах и оставить одну оставшуюся строку, используя значение MIN(id) или MAX(id). Этот простой способ удаления дублирующихся строк не работает, если необходимо соблюдать дополнительные требования:

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

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

1. Строки разбиваются на разделы по столбцам, указывающим на наличие дублирующейся строки. Для каждой комбинации указанных столбцов автоматически создаётся раздел для сбора дублирующихся строк.
2. Каждый раздел сортируется по нескольким столбцам, чтобы отметить их важность. Если, например, необходимо сохранить только пять последних записей, то строки раздела должны быть отсортированы по дате их создания в порядке убывания.
3. Отсортированным строкам внутри раздела присваивается возрастающий номер с помощью оконной функции ROW_NUMBER.
4. Любая строка может быть удалена в соответствии с желаемым количеством оставшихся строк. Если, например, необходимо сохранить только пять последних строк, то можно удалить любую строку с номером строки больше пяти.

Дополнительные ресурсы

• Документация по MySQL: Операторы DELETE для нескольких таблиц.
• Документация PostgreSQL: Операторы DELETE для нескольких таблиц.