Оптимальное удаление строк SQL, за исключением первых n

Быстрый ответ

Перейдём сразу к существу: оставим ТОП-'n' записей и удалим все остальные с применением CTE и оператора DELETE в SQL Server:

WITH CTE AS (
  SELECT TOP (n) [PrimaryKey] FROM [YourTable] ORDER BY [Criteria] DESC
)
DELETE FROM [YourTable]
WHERE [PrimaryKey] NOT IN (SELECT [PrimaryKey] FROM CTE);

Замените n на нужное количество строк, которые вы хотите сохранить, [YourTable] – на имя вашей таблицы, [Criteria] – на критерий сортировки, а [PrimaryKey] – на идентификатор строки.

Пошаговое руководство и альтернативные методы

Анализ производительности и поиск альтернатив

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

CREATE TABLE #TempTable (PrimaryKey DataType);
INSERT INTO #TempTable
SELECT TOP (n) [PrimaryKey] FROM [YourTable] ORDER BY [Criteria] DESC;

DELETE FROM [YourTable]
WHERE [PrimaryKey] NOT IN (SELECT [PrimaryKey] FROM #TempTable);

DROP TABLE #TempTable;

Не забудьте указать тип DataType в соответствии с типом данных вашего первичного ключа.

Подробнее об этом расскажет наш спикер на видео

Сохранение целостности данных с помощью уникальных идентификаторов

Для гарантированного сохранения именно тех записей, которые нам нужны, стоит использовать уникальные идентификаторы, основываясь на строгих критериях сортировки:

DELETE t1 FROM [YourTable] t1
LEFT OUTER JOIN (
    SELECT UNIQUE_IDENTIFIER FROM (
        SELECT UNIQUE_IDENTIFIER, ROW_NUMBER() OVER (ORDER BY [Criteria] DESC) AS rn
        FROM [YourTable]
    ) t
    WHERE rn <= n
) t2 ON t1.UNIQUE_IDENTIFIER = t2.UNIQUE_IDENTIFIER
WHERE t2.UNIQUE_IDENTIFIER IS NULL;

Оптимизация частых удалений

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

Различия в синтаксисе и методах для разных баз данных

Например, в PostgreSQL вместо TOP следует использовать LIMIT:

DELETE FROM [YourTable]
WHERE [PrimaryKey] NOT IN (
    SELECT [PrimaryKey] FROM [YourTable] ORDER BY [Criteria] DESC LIMIT n
);

Не забудьте заменить [YourTable], [PrimaryKey], и [Criteria] на актуальные данные.

Визуализация

Представим, что вы владелец выставки шаров с эмодзи:

До: 🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈🎈

Ваша задача – оставить лишь пять шедевров:

DELETE FROM EmojiBalloonDisplay
WHERE BalloonID NOT IN (
    SELECT TOP 5 BalloonID FROM EmojiBalloonDisplay ORDER BY DesignQuality DESC
);

Результат: лучшие из лучших

После: 🎈🥇, 🎈🥈, 🎈🥉, 🎈🎖️, 🎈🎖️

Остаётся пятерка великолепных дизайнов!

Экстремальные сценарии, потенциальные проблемы и SQL-хитрости

Правильное использование ORDER BY

Убедитесь, что сортировка по ORDER BY происходит по уникальным значениям, чтобы избежать ошибок при выборе топ-'n' записей.

Избегайте производительных ловушек

Не рекомендуется выполнять SELECT TOP n прямо в DELETE, чтобы не тормозить выполнение запроса.

Всегда проводите тестирование перед выполнением

Перед тем как удалить записи, проверьте, какие именно будут удалены, с помощью тестового запроса SELECT.

Резервное копирование – ваша безопасность

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

Полезные материалы

Для дополнительного изучения предлагаем следующие ресурсы:

1. SQL query: Delete all records from the table except latest N? – Stack Overflow — обсуждение сохранения последних 'N' строк в SQL.
2. Unique foreign key constraint between three tables – Database Administrators Stack Exchange — про уникальные ключевые связи в базах данных.
3. SQL Window Functions | Advanced SQL – Mode — освещение оконных функций SQL.