Spring АйО

🗑 Удаление дублирующихся строк



Со временем в любом приложении могут появиться дублирующиеся записи. Иногда их удаление реализуют на уровне приложения, хотя это можно сделать с помощью одного SQL-запроса.



Удалить дубликаты вручную не так просто, как может показаться. Обычно для этого используется команда GROUP BY с функциями MIN(id) или MAX(id), чтобы оставить одну строку.



⚠️ Будьте крайне аккуратны при выполнении DML-запросов. Во избежании потери данных рекоммендуем сделать backup таблицы, которую планируете изменить.

DELETE FROM contacts

WHERE id NOT IN (

   SELECT MIN(id)

   FROM contacts

   GROUP BY firstname, lastname, email

);

Однако такой метод не всегда подходит, если есть дополнительные требования:

* Нужно сохранить не все дубликаты, а лишь часть, например, последние пять.

* Иногда важно не просто удалить первые или последние строки — дополнительные поля могут задавать приоритет, например, мы не хотим удалять пользователя, подтвердившего email, ради того, который этого не сделал.



Чтобы учесть все требования, можно воспользоваться оконными функциями:

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

2. Каждая группа сортируется по важным полям, например, по дате создания.

3. Для каждой строки в группе с помощью функции ROW_NUMBER назначается порядковый номер.

4. Можно удалить любые строки с номером, превышающим необходимый, например, если нужно оставить только пять последних записей.



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

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 > 5;

А запрос, для удаления дубликатов с учетом факта подтверждения email следующим образом:

WITH duplicates AS (

   SELECT

       id,

       ROW_NUMBER() OVER(

           PARTITION BY email

           ORDER BY email_confirmed DESC

       ) AS rownum

   FROM contacts

)

DELETE FROM contacts

USING duplicates

WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

#DatabaseTip #SQL