Как эффективно удалить дубликаты строк, оставив только одну (самую последнюю) версию по уникальному бизнес-ключу?

Короткий ответ

Нужно пронумеровать дубликаты внутри каждой группы бизнес-ключа функцией ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY updated_at DESC) и оставить (или удалить все, кроме) строки с номером 1.

Как это работает подробнее

PARTITION BY business_key делит строки на группы дублей, ORDER BY updated_at DESC внутри каждой группы ставит самую свежую запись первой, и ROW_NUMBER() присваивает ей номер 1; все остальные строки группы получают номера 2, 3 и так далее и считаются «лишними» дублями.

Пример кода

question-45.sqlКопировать

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY business_key
           ORDER BY updated_at DESC, id DESC
         ) AS rn
  FROM raw_table
)
DELETE FROM raw_table AS r
USING ranked AS d
WHERE r.id = d.id
  AND d.rn > 1;

Что использовать на практике

• в PostgreSQL для реального удаления обычно оборачивают это в CTE и используют DELETE ... WHERE id IN (SELECT id FROM ranked WHERE rn > 1); важно сначала выполнить только SELECT, чтобы визуально проверить, какие строки будут удалены, прежде чем запускать DELETE. В ClickHouse для таблиц с движком ReplacingMergeTree похожая задача решается через FINAL или OPTIMIZE ... DEDUPLICATE, что работает принципиально иначе. Проверка: количество строк с rn = 1 должно равняться COUNT(DISTINCT business_key) в исходной таблице.

Подводные камни

• сортируют без вторичного детерминированного критерия при равенстве updated_at у нескольких строк — тогда «какая строка окажется первой» не гарантировано и может отличаться между запусками; забывают, что DELETE нужно явно ограничить тем же набором строк, что и проверочный SELECT, иначе можно случайно удалить больше данных.