Как найти и удалить дубликаты в таблице?

Задача из ежедневной практики аналитика, поэтому её любят на собеседованиях. Проверяют не только синтаксис, но и то, задашь ли ты первый правильный вопрос: «а что тут вообще считать дубликатом?».

Как рассуждать

Раздели задачу на две: сначала найти, потом удалить — это разные приёмы.

Для поиска думай группами: дубликаты — это группы строк с одинаковым ключом, где строк больше одной. Значит, GROUP BY по ключу + HAVING на количество.

Для удаления нужен способ отличить «одну оставить» от «остальные снести». Внутри группы дублей строки надо пронумеровать и удалить все, кроме первой. Ровно для этого создана оконная функция ROW_NUMBER: она даёт уникальные номера даже при полном равенстве значений.

И главный вопрос до всякого кода: дубликат — это полное совпадение всех колонок или совпадение по бизнес-ключу (например, один и тот же клиент по email)? От этого зависит ключ группировки.

Эталонный ответ

Найти дубли по бизнес-ключу — GROUP BY и HAVING:

SELECT lower(email) AS email, count(*) AS cnt
FROM customer
GROUP BY lower(email)
HAVING count(*) > 1;

Условие на количество идёт в HAVING, а не в WHERE — фильтруем уже группы. Это отдельная классика: чем WHERE отличается от HAVING.

Удалить, оставив по одной записи на ключ, — пронумеровать и снести хвост:

WITH ranked AS (
  SELECT customer_id,
         row_number() OVER (
           PARTITION BY lower(email)
           ORDER BY customer_id
         ) AS rn
  FROM customer
)
DELETE FROM customer
WHERE customer_id IN (SELECT customer_id FROM ranked WHERE rn > 1);

PARTITION BY задаёт, что считать одной группой дублей, ORDER BY — какую строку оставить (здесь минимальный customer_id). Все строки с rn > 1 — лишние.

Почему именно ROW_NUMBER, а не RANK: при полном равенстве RANK выдаст всем строкам номер 1 и не даст отличить «которую оставить». Разница разобрана в ROW_NUMBER, RANK, DENSE_RANK.

Сильный ответ добавит: перед DELETE на проде сначала прогоняют SELECT тем же условием и глазами смотрят, что уйдёт; а «на будущее» защищаются UNIQUE-ограничением, чтобы дубли не появлялись вовсе.

Частые ошибки

• Считают дублем только полное совпадение всех колонок и не спрашивают про бизнес-ключ — на реальных данных дубли обычно по ключу, а не по всей строке.
• Пишут HAVING через WHERE или наоборот — не понимают порядок выполнения.
• Пытаются удалить дубли одним DELETE ... GROUP BY — так нельзя, нужен способ пометить «лишние» строки (ROW_NUMBER или self-join по id).
• Забывают нормализовать ключ (регистр, пробелы): Ivan@x.ru и ivan@x.ru — это дубли, но наивная группировка их не поймает.