Вопрос с собеседования · Аналитика данных

Как удалить дубликаты и оставить последнюю версию записи?

JuniorSQL для аналитикаSQL
Ещё спрашивают так:
  • Как найти дубликаты в таблице?
Коротко

Найти: GROUP BY ключ HAVING COUNT(*) > 1. Оставить последнюю версию: пронумеровать строки внутри ключа ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY updated_at DESC) и взять rn = 1 — в выборке или в DELETE с этим условием.

  • Главный вопрос — что считать дубликатом: полное совпадение или один бизнес-ключ
  • Порядок «какую версию оставить» нужно определить явно, с тай-брейкером
  • В хранилищах с событиями дубли — норма (повторная доставка): дедуплицируют по id события

А ещё В ClickHouse для этого есть ReplacingMergeTree и argMax

Подробно

Откуда дубли: повторная доставка событий (at-least-once), повторный импорт файла, отсутствие уникального ограничения, разный регистр или пробелы в ключе (Ivan@Mail.ru и ivan@mail.ru).

Порядок действий: уточнить ключ уникальности и правило выбора версии → посмотреть масштаб (сколько групп с дублями) → проверить нормализацию ключа (lower, trim) → удалить или исключить в витрине → добавить ограничение UNIQUE или дедупликацию на загрузке, чтобы дубли не вернулись.

Аналитика по событиям: события дедуплицируют по event_id, а не по содержимому — два одинаковых клика подряд могут быть настоящими.

Пример

-- найти
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- оставить последнюю версию в запросе
SELECT * FROM (
  SELECT u.*, ROW_NUMBER() OVER (PARTITION BY email
                                 ORDER BY updated_at DESC, id DESC) AS rn
  FROM users u
) t WHERE rn = 1;

-- удалить лишние (PostgreSQL)
DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC, id DESC) rn
    FROM users) t
  WHERE rn > 1);

Что хотят услышать

  • Поиск через GROUP BY HAVING.
  • Выбор версии через ROW_NUMBER.
  • Определение ключа и предотвращение повторения.

Проверьте себя

Один верный вариант. Подумайте, потом откройте ответ.

Показать варианты и ответ
  • ✗GROUP BY ключ и MAX по каждой остальной колонке отдельно
  • ✗SELECT DISTINCT * всегда оставляет последнюю версию записи
  • ✗Порядок выбора версии определять не нужно
  • ✓ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY updated_at DESC), оставить rn = 1

Связанные вопросы

Партнёр платформы

Разбор собеседования с sotto

После собеседования sotto разбирает разговор: оценка, каждый вопрос — хорошо, частично или слабо, чего не хватило в ответе и над чем поработать к следующему разу. Первые 30 минут бесплатно.

Разбор собеседования