Удаление дубликатов в PostgreSQL: поиск по merchant_id и url

Пройдите тест, узнайте какой профессии подходите

Я предпочитаю
0%
Работать самостоятельно и не зависеть от других
Работать в команде и рассчитывать на помощь коллег
Организовывать и контролировать процесс работы

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

Для удаления дубликатов в PostgreSQL используйте "Common Table Expressions" (CTE) и функцию ROW_NUMBER(). Строки с номером больше единицы необходимо убрать:

SQL
Скопировать код
WITH CTE_duplicates AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY unique_column ORDER BY id) AS row_number
  FROM table
)
DELETE FROM table
WHERE id IN (SELECT id FROM CTE_duplicates WHERE row_number > 1);

В параметрах unique_column, id и table пропишите те значения, которые необходимы в вашем случае.

Кинга Идем в IT: пошаговый план для смены профессии

Настройка производительности для объемных таблиц

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

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

Обновление PostgreSQL для обеспечения совместимости

Чтобы проверить ваше решение в SQL Fiddle, обновите PostgreSQL до версии 9.3 или более поздней, которые поддерживают данный сервис.

Сохранение конкретных данных

Если во время работы с данными вам необходимо сохранить самые ранние или последние записи, используйте аргумент сортировки в функции ROW_NUMBER():

SQL
Скопировать код
ORDER BY created_at DESC -- для сохранения последних записей
ORDER BY created_at ASC -- для сохранения первых записей

Дубликаты составных ключей

Если возникла ситуация, когда дублирование происходит по комбинации полей, таких как merchant_id и url, то включите их в PARTITION BY:

SQL
Скопировать код
PARTITION BY merchant_id, url

Избегание непредвиденной потери данных

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

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

Представьте таблицу данных как сад, где каждая строка – это цветок:

Markdown
Скопировать код
Сад до: [🌼, 🌼🌼, 🌼, 🌼🌼🌼, 🌼]

Наша цель – удалить все лишнее:

SQL
Скопировать код
DELETE FROM garden
WHERE flower_id IN (
  SELECT flower_id
  FROM (
    SELECT flower_id, 
           ROW_NUMBER() OVER (PARTITION BY flower_type ORDER BY planting_date) AS row
    FROM garden
  ) as sub_query
  WHERE sub_query.row > 1
);

В итоге, мы получаем аккуратный цветник:

Markdown
Скопировать код
Сад после: [🌼, 🌼, 🌼]

Завершение: SQL помогает убрать все лишнее, оставив данные уникальными.

Отбор уникальных записей

Если вам нужно найти уникальные записи, используйте ключевое слово DISTINCT при выполнении SELECT:

SQL
Скопировать код
SELECT DISTINCT unique_column
FROM table;

Готовность к непредвиденным ситуациям

Если что-то пойдет не так, воспользуйтесь транзакциями, чтобы безопасно отменить изменения:

SQL
Скопировать код
BEGIN;

-- Выполнение команды DELETE

ROLLBACK; /* Если есть сомнения, лучше откатить изменения */

Использование мощи планирования запросов

Для оптимизации запросов на удаление дублирующихся строк, активируйте планировщик запросов с помощью команды EXPLAIN:

SQL
Скопировать код
EXPLAIN
WITH CTE_duplicates AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY unique_column ORDER BY id) AS row_number
  FROM table
)
DELETE FROM table
WHERE id IN (SELECT id FROM CTE_duplicates WHERE row_number > 1);

Поддержание среды, свободной от дубликатов

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

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

  1. PostgreSQL: Документация: DELETE – официальные рекомендации по использованию команды DELETE.
  2. Как убрать дубликаты строк? – Stack Overflow – советы экспертов по удалению дублирующихся строк в SQL.
  3. Поиск дубликатов – PostgreSQL wiki – методы выявления дубликатов в бд PostgreSQL.
  4. Postgres 12 | db<>fiddle – онлайн-площадка для тестирования SQL-запросов.
  5. Осторожно с CTE в PostgreSQL | by Haki Benita | Medium – статья об оптимизации операций DELETE с использованием CTE.
  6. Ожидаем PostgreSQL 13 – Add SQL-standard WITH TIES – новые функции в PostgreSQL 13, которые помогут изменить подход к работе с дубликатами.
Проверь как ты усвоил материалы статьи
Пройди тест и узнай насколько ты лучше других читателей
Какой SQL-запрос следует использовать для удаления дубликатов по уникальному столбцу?
1 / 5