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

Решение

Анализ задачи

Дубликаты в таблице: Для каждого client_id есть несколько записей с разными updated_at. Нужно оставить только самую свежую запись для каждого клиента.

В примере:

• client_id=100: 3 записи (id=1,2,4) → оставить id=4 (самая свежая)
• client_id=200: 1 запись (id=3) → оставить как есть

Решение 1: SELECT последних записей (просмотр)

SELECT DISTINCT ON (client_id)
    id,
    client_id,
    balance,
    updated_at
FROM ClientBalance
ORDER BY client_id, updated_at DESC;

Только для PostgreSQL. Объяснение:

• DISTINCT ON (client_id) — оставляет одну запись на client_id
• ORDER BY client_id, updated_at DESC — первая запись в группе (самая свежая)

Результат:

id | client_id | balance | updated_at
---|-----------|---------|---------------------
4  | 100       | 6000.00 | 2024-01-17 12:00:00
3  | 200       | 3000.00 | 2024-01-15 09:00:00

Решение 2: С использованием ROW_NUMBER (универсальное)

SELECT
    id,
    client_id,
    balance,
    updated_at
FROM (
    SELECT
        id,
        client_id,
        balance,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
) ranked
WHERE rn = 1
ORDER BY client_id;

Как работает:

1. ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC)

   • Нумерует записи в каждой группе client_id
   • Сортирует по убыванию updated_at
   • Первая строка (rn=1) — самая свежая

2. WHERE rn = 1 — берём только первую

Визуально:

client_id | updated_at | id | rn
----------|------------|----|----|---
100       | 2024-01-17 | 4  | 1  ✓ ← берём
100       | 2024-01-16 | 2  | 2
100       | 2024-01-15 | 1  | 3
200       | 2024-01-15 | 3  | 1  ✓ ← берём

Решение 3: С использованием RANK (если важен порядок привязки)

SELECT
    id,
    client_id,
    balance,
    updated_at
FROM (
    SELECT
        id,
        client_id,
        balance,
        updated_at,
        RANK() OVER (PARTITION BY client_id ORDER BY updated_at DESC, id DESC) AS rnk
    FROM ClientBalance
) ranked
WHERE rnk = 1
ORDER BY client_id;

Отличие от ROW_NUMBER:

• RANK() — если несколько записей с одинаковым updated_at, они получат одинаковый ранг
• ROW_NUMBER() — всегда разные номера

Здесь добавлен id DESC в ORDER BY для детерминистичности (при одинаковом времени берём запись с большим id).

Решение 4: DELETE дубликатов (основное)

DELETE FROM ClientBalance
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
        FROM ClientBalance
    ) ranked
    WHERE rn = 1
);

Что происходит:

1. Внутренний подзапрос находит id последних записей
2. DELETE удаляет всё остальное

Результат:

Таблица до:              Таблица после:
id | client_id           id | client_id
1  | 100  ✗ удалена     4  | 100
2  | 100  ✗ удалена     3  | 200
3  | 200  ✓ осталась
4  | 100  ✓ осталась

Решение 5: DELETE с использованием CTE (более читаемо)

WITH latest_records AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
),
records_to_delete AS (
    SELECT id
    FROM latest_records
    WHERE rn > 1  -- Всё кроме первой записи
)
DELETE FROM ClientBalance
WHERE id IN (SELECT id FROM records_to_delete);

Преимущества:

• Очень читаемо
• Легко проверить, что будет удалено: SELECT * FROM records_to_delete;
• Атомарно

Решение 6: MERGE операция (для атомарности в SQL Server)

-- SQL Server
MERGE INTO ClientBalance AS target
USING (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
) AS source
ON target.id = source.id
WHEN MATCHED AND source.rn > 1
THEN DELETE;

Решение 7: Создание новой таблицы без дубликатов (для очень больших таблиц)

-- Шаг 1: Создать новую таблицу с уникальными записями
CREATE TABLE ClientBalance_new AS
SELECT
    id,
    client_id,
    balance,
    updated_at
FROM (
    SELECT
        id,
        client_id,
        balance,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
) ranked
WHERE rn = 1;

-- Шаг 2: Переименовать таблицы
ALTER TABLE ClientBalance RENAME TO ClientBalance_old;
ALTER TABLE ClientBalance_new RENAME TO ClientBalance;

-- Шаг 3: Удалить старую таблицу (если всё ок)
DROP TABLE ClientBalance_old;

Преимущества:

• На очень больших таблицах работает быстрее (DELETE работает медленно)
• Можно откатиться если что-то пошло не так (ClientBalance_old всё ещё есть)

Решение 8: Безопасное удаление с проверкой (рекомендуется)

-- Шаг 1: Проверить, что будет удалено
WITH latest_records AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
),
records_to_delete AS (
    SELECT COUNT(*) as delete_count
    FROM latest_records
    WHERE rn > 1
)
SELECT * FROM records_to_delete;

-- Шаг 2: Если всё ок, то удаляем
BEGIN TRANSACTION;

WITH latest_records AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
)
DELETE FROM ClientBalance
WHERE id IN (
    SELECT id FROM latest_records WHERE rn > 1
);

COMMIT;

Решение 9: Для PostgreSQL (самое элегантное)

DELETE FROM ClientBalance
WHERE (client_id, updated_at, id) NOT IN (
    SELECT client_id, updated_at, id
    FROM ClientBalance
    DISTINCT ON (client_id)
    ORDER BY client_id, updated_at DESC
);

Это одна операция, очень быстро и безопасно.

Обеспечение атомарности

Транзакция гарантирует либо полное выполнение, либо отката:

BEGIN TRANSACTION;

-- Все операции здесь выполняются атомарно
WITH to_delete AS (
    SELECT id
    FROM ClientBalance cb1
    WHERE id NOT IN (
        SELECT id
        FROM ClientBalance cb2
        WHERE cb1.client_id = cb2.client_id
        ORDER BY updated_at DESC
        LIMIT 1
    )
)
DELETE FROM ClientBalance WHERE id IN (SELECT id FROM to_delete);

IF @@ROWCOUNT > 0
BEGIN
    COMMIT;  -- Успех
END
ELSE
BEGIN
    ROLLBACK;  -- Откат если что-то не так
END

Уровни изоляции для надёжности:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;
-- операции
COMMIT;

Оптимизирующие индексы

-- 1. Индекс для быстрого поиска по client_id
CREATE INDEX idx_client_balance_client_id 
    ON ClientBalance(client_id);

-- 2. Индекс для ORDER BY updated_at
CREATE INDEX idx_client_balance_updated_at 
    ON ClientBalance(updated_at DESC);

-- 3. Составной индекс (лучший для нашего запроса)
CREATE INDEX idx_client_balance_composite 
    ON ClientBalance(client_id, updated_at DESC, id);

-- 4. Индекс на первичный ключ (автоматический)
-- CREATE UNIQUE INDEX idx_client_balance_id ON ClientBalance(id);

Почему составной индекс помогает:

• PostgreSQL может использовать его для PARTITION BY (client_id)
• И для ORDER BY (updated_at DESC)
• Минимум обращений к диску

План выполнения для проверки использования индекса

EXPLAIN ANALYZE
SELECT
    id,
    client_id,
    balance,
    updated_at
FROM (
    SELECT
        id,
        client_id,
        balance,
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC) AS rn
    FROM ClientBalance
) ranked
WHERE rn = 1;

Ищем: Index Scan (быстро) вместо Seq Scan (медленно)

Рекомендуемое решение для production

-- PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

WITH latest_records AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC, id DESC) AS rn
    FROM ClientBalance
)
DELETE FROM ClientBalance
WHERE id NOT IN (SELECT id FROM latest_records WHERE rn = 1);

COMMIT;

-- MySQL / SQL Server
BEGIN TRANSACTION;

WITH latest_records AS (
    SELECT
        id,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY updated_at DESC, id DESC) AS rn
    FROM ClientBalance
)
DELETE FROM ClientBalance
WHERE id IN (SELECT id FROM latest_records WHERE rn > 1);

COMMIT;

Итоговый чеклист

• ROW_NUMBER() работает везде, DISTINCT ON только PostgreSQL
• Всегда используй TRANSACTION для atomicity
• Проверь что будет удалено перед DELETE
• Составной индекс (client_id, updated_at DESC) критичен
• На очень больших таблицах лучше CREATE + RENAME + DROP
• Добавь id DESC в ORDER BY для детерминистичности
• SERIALIZABLE изоляция для максимальной безопасности
• Хранить резервные копии перед массовым DELETE