Как удалить дубликаты в SQL

Три основных подхода

1. DELETE с подзапросом — прямое удаление
2. CREATE TABLE AS SELECT (CTAS) — создать новую без дублей
3. DISTINCT — если можно просто скопировать уникальные

Рекомендация: всегда делайте backup / транзакцию перед DELETE.

1. DELETE через ROW_NUMBER (Postgres)

Оставить только первую запись среди дубликатов:

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

ctid — внутренний системный идентификатор строки в Postgres.

2. DELETE через подзапрос (универсально)

DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

Оставляет запись с минимальным id, остальные удаляет.

Внимание: для больших таблиц может быть очень медленно (NOT IN на миллионах записей).

3. DELETE через SELF JOIN (MySQL)

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

Удаляет все, у которых есть «более ранняя» копия.

4. CTAS — создать новую таблицу без дубликатов

Безопаснее, чем DELETE:

CREATE TABLE users_clean AS
SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
) t
WHERE rn = 1;

Потом:

ALTER TABLE users RENAME TO users_old;
ALTER TABLE users_clean RENAME TO users;
-- убедиться, всё ок
DROP TABLE users_old;

5. Через DISTINCT (если все колонки дублируются)

Если дубликаты — полные (все колонки одинаковые):

CREATE TABLE users_clean AS
SELECT DISTINCT * FROM users;

Простое, но не сохраняет индексы и не контролирует, какую именно копию оставить.

6. UPDATE + DELETE (для «частичных» дубликатов)

Если у дубликатов разные значения в каких-то полях, можно сначала объединить:

-- Пример: объединить данные перед удалением
WITH duplicates AS (
    SELECT email,
        MAX(last_login) AS last_login,
        MAX(updated_at) AS updated_at,
        MIN(id) AS keep_id
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
UPDATE users u
SET last_login = d.last_login,
    updated_at = d.updated_at
FROM duplicates d
WHERE u.id = d.keep_id;

-- Теперь удалить остальных
DELETE FROM users
WHERE id IN (
    SELECT id FROM users u
    JOIN duplicates d ON d.email = u.email
    WHERE u.id != d.keep_id
);

7. В ClickHouse (тут свои правила)

ClickHouse — column-store, обычного UPDATE/DELETE нет. Используют:

-- MergeTree + ReplacingMergeTree
CREATE TABLE users_clean ENGINE = ReplacingMergeTree ORDER BY email
AS SELECT * FROM users;

-- Или OPTIMIZE FINAL для дедупликации
OPTIMIZE TABLE users FINAL DEDUPLICATE;

8. В BigQuery

BigQuery не поддерживает DELETE напрямую в определённых случаях. Use CTAS:

CREATE OR REPLACE TABLE dataset.users AS
SELECT * EXCEPT(rn)
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM dataset.users
)
WHERE rn = 1;

9. Предохраняемся от ошибок

Шаг 1. Убедиться, сколько удалим

-- сначала SELECT, чтобы оценить
SELECT COUNT(*)
FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
) t
WHERE rn > 1;

Шаг 2. Транзакция с откатом

BEGIN;

DELETE FROM users
WHERE id IN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
        FROM users
    ) t
    WHERE rn > 1
);

-- проверяем
SELECT COUNT(*) FROM users;

-- если всё ок
COMMIT;
-- если нет
-- ROLLBACK;

Шаг 3. Backup

CREATE TABLE users_backup AS SELECT * FROM users;

10. Добавить UNIQUE constraint после очистки

Чтобы дубликаты не появлялись снова:

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Теперь INSERT дубля будет падать с ошибкой.

Как решить, какую копию оставить

По времени создания

ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at ASC) AS rn
-- rn = 1 — самая старая (первая зарегистрированная)

По свежести

ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) AS rn
-- rn = 1 — самая свежая запись

По приоритету статуса

ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY
        CASE status WHEN 'verified' THEN 1 WHEN 'pending' THEN 2 ELSE 3 END,
        created_at
) AS rn

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

Ошибка 1. DELETE без WHERE

Удалит всю таблицу. Всегда проверяйте запрос в SELECT-форме сначала.

Ошибка 2. Не учитывать foreign keys

Если в orders есть user_id, а вы удаляете users — FK ломаются. Работайте каскадно или сначала обработайте дочерние таблицы.

Ошибка 3. Не делать backup

Даже для аналитических таблиц. DROP TABLE IF EXISTS users_backup; CREATE TABLE users_backup AS SELECT * FROM users;.

Ошибка 4. DELETE без индекса

На миллионах записей может занять часы. Если нужно быстро — CTAS + RENAME.

Ошибка 5. Забыть про ETL

Если данные приходят из pipeline — дубли будут появляться снова. Нужно чинить источник, а не только чистить.

Связанные темы

• Как найти дубликаты в SQL
• INSERT / UPDATE / DELETE — шпаргалка
• DELETE vs TRUNCATE
• ROW_NUMBER vs RANK vs DENSE_RANK

FAQ

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

Сначала SELECT, чтобы увидеть. Потом в транзакции с возможностью отката. Потом COMMIT.

DELETE медленно — что делать?

CTAS (создать новую таблицу) обычно быстрее для больших объёмов.

Как предотвратить повторное появление?

UNIQUE constraint на БД. Идемпотентный ETL (ON CONFLICT DO NOTHING).

Что делать если таблица на 100М записей?

CTAS + RENAME. DELETE будет слишком долгим. В ClickHouse — ReplacingMergeTree.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.