SQL: Удаление дубликатов без создания новой таблицы

Удаление дубликатов без создания новой таблицы

Условие задачи

Полные дубликаты (абсолютно идентичные строки) появились в таблице. Нужно удалить их без создания новой таблицы и без потери оригинальных данных.

Оптимальный подход: используем оконную функцию ROW_NUMBER()

Ключевая идея: присваиваем каждой строке номер в рамках группы дубликатов. Первой копии присваиваем 1, остальным — 2, 3, ... и удаляем все строки с номером > 1.

Решение 1: Используя CTE и ROW_NUMBER (РЕКОМЕНДУЕТСЯ)

WITH duplicates AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, ... ORDER BY rowid) AS rn
    FROM your_table
)
DELETE FROM your_table
WHERE rowid IN (
    SELECT rowid FROM duplicates WHERE rn > 1
);

Решение 2: Если есть id столбец

DELETE FROM your_table
WHERE id NOT IN (
    SELECT MIN(id)
    FROM your_table
    GROUP BY col1, col2, col3, ...
);

Этот способ оставляет первый (с минимальным id) экземпляр каждого дубликата.

Решение 3: PostgreSQL — через CTID (системный столбец)

Если таблица не имеет явного id:

DELETE FROM your_table
WHERE ctid IN (
    SELECT ctid
    FROM your_table
    WHERE ROW_NUMBER() OVER (
        PARTITION BY col1, col2, col3, ...
        ORDER BY ctid
    ) > 1
);

Ктид (ctid) — это уникальный идентификатор строки в PostgreSQL, который используется внутренне.

Решение 4: Используя NOT EXISTS

DELETE FROM your_table t1
WHERE EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t1.col1 = t2.col1
      AND t1.col2 = t2.col2
      AND t1.col3 = t2.col3
      AND t1.id > t2.id  -- удаляем более новые записи
);

Детальный анализ подходов

Подход ROW_NUMBER + CTE:

• Самый универсальный
• Работает во всех СУБД
• Четко определяет, какие строки удалять
• Легко модифицировать логику выбора (первый vs последний)

Подход с MIN(id):

• Требует наличия уникального id столбца
• Простой и быстрый
• Понятная логика

Подход с CTID (PostgreSQL):

• Специфичен для PostgreSQL
• Работает без явного id
• Наиболее эффективен для больших таблиц

Подход с NOT EXISTS:

• Хорош для выборочного удаления
• Медленнее на больших данных
• Более сложная логика

Полный пример для реальной таблицы

Предположим, таблица customers с полями: name, email, phone, city

WITH duplicates AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (
            PARTITION BY name, email, phone, city
            ORDER BY ctid
        ) AS rn
    FROM customers
)
DELETE FROM customers
WHERE ctid IN (
    SELECT ctid FROM duplicates WHERE rn > 1
);

Проверка результата перед удалением

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

SELECT 
    COUNT(*) AS total_rows,
    COUNT(DISTINCT col1, col2, col3, ...) AS unique_rows,
    COUNT(*) - COUNT(DISTINCT col1, col2, col3, ...) AS duplicates_count
FROM your_table;

Важные замечания

• Резервная копия: всегда делайте backup перед удалением
• Транзакция: оборачивайте в BEGIN/COMMIT для возможности откатки
• Блокировки: операция заблокирует таблицу, выполняйте во время низкой нагрузки
• Индексы: после удаления может потребоваться переиндексирование

Транзакция с проверкой

BEGIN;

-- Проверка
SELECT COUNT(*) FROM your_table;

-- Удаление
WITH duplicates AS (
    SELECT ctid, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, ... ORDER BY ctid) AS rn
    FROM your_table
)
DELETE FROM your_table WHERE ctid IN (SELECT ctid FROM duplicates WHERE rn > 1);

-- Проверка результата
SELECT COUNT(*) FROM your_table;

COMMIT;  -- или ROLLBACK если что-то не так