Удаление дубликатов в PostgreSQL: поиск по merchant_id и url
Быстрый ответ
Для удаления дубликатов в PostgreSQL используйте "Common Table Expressions" (CTE) и функцию ROW_NUMBER(). Строки с номером больше единицы необходимо убрать:
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
пропишите те значения, которые необходимы в вашем случае.
Настройка производительности для объемных таблиц
Для ситуаций с большими таблицами, где критически важна прозоидательность, можно оптимизировать удаление следующим образом:
- Создайте индексированную временную таблицу, которая позволит отследить дублирующиеся строки.
- Ссылаясь на данную таблицу при удалении, вы значительно снизите нагрузку на выполнение операции.
Обновление PostgreSQL для обеспечения совместимости
Чтобы проверить ваше решение в SQL Fiddle, обновите PostgreSQL до версии 9.3 или более поздней, которые поддерживают данный сервис.
Сохранение конкретных данных
Если во время работы с данными вам необходимо сохранить самые ранние или последние записи, используйте аргумент сортировки в функции ROW_NUMBER():
ORDER BY created_at DESC -- для сохранения последних записей
ORDER BY created_at ASC -- для сохранения первых записей
Дубликаты составных ключей
Если возникла ситуация, когда дублирование происходит по комбинации полей, таких как merchant_id
и url
, то включите их в PARTITION BY:
PARTITION BY merchant_id, url
Избегание непредвиденной потери данных
Перед применением команды DELETE сформируйте резервную копию данных. Дополнительно, можно выполнить SELECT, используя ту же логику, что позволит убедиться в правильности предполагаемых изменений.
Визуализация
Представьте таблицу данных как сад, где каждая строка – это цветок:
Сад до: [🌼, 🌼🌼, 🌼, 🌼🌼🌼, 🌼]
Наша цель – удалить все лишнее:
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
);
В итоге, мы получаем аккуратный цветник:
Сад после: [🌼, 🌼, 🌼]
Завершение: SQL помогает убрать все лишнее, оставив данные уникальными.
Отбор уникальных записей
Если вам нужно найти уникальные записи, используйте ключевое слово DISTINCT при выполнении SELECT:
SELECT DISTINCT unique_column
FROM table;
Готовность к непредвиденным ситуациям
Если что-то пойдет не так, воспользуйтесь транзакциями, чтобы безопасно отменить изменения:
BEGIN;
-- Выполнение команды DELETE
ROLLBACK; /* Если есть сомнения, лучше откатить изменения */
Использование мощи планирования запросов
Для оптимизации запросов на удаление дублирующихся строк, активируйте планировщик запросов с помощью команды EXPLAIN:
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);
Поддержание среды, свободной от дубликатов
Для предотвращения возникновения новых дубликатов применяйте ограничения и использование уникальных индексов.
Полезные материалы
- PostgreSQL: Документация: DELETE – официальные рекомендации по использованию команды DELETE.
- Как убрать дубликаты строк? – Stack Overflow – советы экспертов по удалению дублирующихся строк в SQL.
- Поиск дубликатов – PostgreSQL wiki – методы выявления дубликатов в бд PostgreSQL.
- Postgres 12 | db<>fiddle – онлайн-площадка для тестирования SQL-запросов.
- Осторожно с CTE в PostgreSQL | by Haki Benita | Medium – статья об оптимизации операций DELETE с использованием CTE.
- Ожидаем PostgreSQL 13 – Add SQL-standard WITH TIES – новые функции в PostgreSQL 13, которые помогут изменить подход к работе с дубликатами.