0Pricing
SQL Interview Prep · Урок

Безопасное удаление дубликатов строк

Удаляйте полные и почти полные дубликаты, сохраняя одну эталонную запись.

«Безопасное удаление дубликатов строк» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Проблема дедупликации

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

Мы рассмотрим обнаружение дубликатов, выбор копии для сохранения, дедупликацию в SELECT и физическое удаление дубликатов из таблицы.

Сначала определите дубликат

Первый вопрос, который следует задать интервьюеру: «Что делает две строки дубликатами?» Возможны такие варианты:

  • Полные дубликаты: все столбцы идентичны.
  • Дубликаты по ключу: совпадает один и тот же бизнес-ключ (например, один и тот же email), но остальные столбцы могут различаться.

Для каждого случая применяется свой подход. Никогда не делайте предположений: уточнение определения дубликата — самый важный шаг, и интервьюеры ожидают, что Вы зададите этот вопрос.

Обнаружение дубликатов

Чтобы найти дублирующиеся ключи, сгруппируйте строки по столбцам, определяющим дубликат, и оставьте группы с количеством больше одного. Так Вы узнаете, какие ключи затронуты и сколько копий существует, прежде чем что-либо изменять.

Сначала выполнить запрос для обнаружения — полезная практика, о которой стоит сказать вслух: перед удалением Вы проверяете масштаб проблемы.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Полные дубликаты: DISTINCT

Если дубликаты действительно идентичны по всем столбцам, представление без дубликатов, доступное только для чтения, создаётся так же просто, как SELECT DISTINCT *. UNION (без ALL) также удаляет дублирующиеся строки.

Но DISTINCT подходит только тогда, когда нужно избавиться от дубликатов целых строк и не требуется выбирать, какую копию сохранить. Для дубликатов по ключу, где столбцы различаются, необходимо ранжирование.

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

Дубликаты по ключу: ROW_NUMBER

Если строки имеют общий ключ, но различаются в других столбцах, разделите их по ключу и пронумеруйте каждую копию. rn = 1 обозначает строку, которую следует сохранить, а rn > 1 — лишние строки для удаления.

Конструкция ORDER BY внутри окна определяет, какая копия станет эталонной. Выбирайте порядок осознанно: например, можно оставить строку, обновлённую последней.

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

Выбор эталонной копии

Поместите нумерацию в CTE и оставьте только rn = 1. Это вернёт по одной строке на каждый ключ — именно ту строку, которую Ваш ORDER BY поставил на первое место.

Такая форма SELECT не изменяет данные: она отлично подходит для создания чистого представления или передачи данных через INSERT ... SELECT в целевую таблицу без дубликатов, не затрагивая исходную таблицу.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

Важность выбора порядка

Конструкция ORDER BY внутри раздела — это бизнес-решение, а не формальность:

  • ORDER BY updated_at DESC сохраняет самую свежую запись.
  • ORDER BY created_at ASC сохраняет исходную запись.
  • ORDER BY id ASC сохраняет наименьший суррогатный ключ — это полезный стабильный произвольный выбор.

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

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

Физическое удаление дубликатов

Чтобы действительно удалить дубликаты из таблицы, найдите лишние строки (rn > 1) и удалите их. В PostgreSQL и сервере SQL можно удалять строки с помощью CTE; в MySQL обычно применяют самосоединение или подзапрос.

Всегда сначала выполняйте соответствующий SELECT, чтобы предварительно просмотреть, какие именно строки исчезнут. Удаление вслепую — причина, по которой кандидаты не справляются с этим вопросом.

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

Шаблон удаления с самосоединением

Классический переносимый подход сохраняет строку с наименьшим id для каждого дублирующегося ключа, а остальные удаляет с помощью самосоединения. Для него не нужны оконные функции, что важно при работе со старыми СУБД.

Условие соединения сопоставляет каждую строку с другой строкой, у которой тот же ключ, но меньшее значение id; любая строка, для которой существует такая копия с меньшим id, является дубликатом и подлежит удалению.

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

Проверка безопасности

Перед удалением защитите себя:

  • Оберните удаление в транзакцию, чтобы выполнить ROLLBACK, если количество строк окажется неверным.
  • Сначала выполните SELECT COUNT(*) для строк, предназначенных к удалению, и проверьте результат на здравый смысл.
  • Рассмотрите возможность создать резервную таблицу: CREATE TABLE users_bak AS SELECT * FROM users.
  • Убедитесь, что столбцы в PARTITION BY действительно определяют дубликат, иначе Вы можете удалить разные записи.
BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

Почти дубликаты и нормализация

Иногда строки не полностью совпадают, но логически являются одинаковыми: 'Ann@X.com' и 'ann@x.com' или строки с пробелами в конце. Разделяйте строки по нормализованному выражению, а не по исходному столбцу.

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

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

Быстрая проверка

Выберите безопасный способ дедупликации.

Итоги: безопасная дедупликация

Выполняйте дедупликацию последовательно:

  • Сначала определите, что считать дубликатом, затем обнаружьте его с помощью GROUP BY / HAVING COUNT(*) > 1.
  • Полные дубликаты → DISTINCT. Дубликаты по ключу → ROW_NUMBER с разделением по ключу; сохраните rn = 1.
  • Оконный ORDER BY выбирает эталонную копию; добавьте уникальный дополнительный критерий.
  • Удаляйте строки с rn > 1 внутри транзакции после предварительной проверки количества.
  • Нормализуйте ключи, чтобы находить почти дубликаты.

Часто задаваемые вопросы

Урок «Безопасное удаление дубликатов строк» бесплатный?

Да — полный текст урока «Безопасное удаление дубликатов строк» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Безопасное удаление дубликатов строк»?

Удаляйте полные и почти полные дубликаты, сохраняя одну эталонную запись. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Безопасное удаление дубликатов строк»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Первые N строк в каждой группе с ROW_NUMBER
  2. Обработка совпадений среди первых N строк
  3. Безопасное удаление дубликатов строк
  4. Сохранение последней строки для каждого ключа
← Назад к SQL Interview Prep