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

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

Поиск и удаление неуникальных строк в таблице, SQL only

Больше всего интересует то что после GROUP BY , что дословно можно прочитать как: группировать значения по столбцу column где размер группы более 1 записи.

В итоге будут получены все неуникальные значения стоблца column.

Удаление всех неуникальных значений

Не долго думая можно попробовать удалить так:

И конечно же так не получится, потому что:

Но можно создать временную таблицу, занести в нее идентифицирующие значения (в данном случае `column`) и используя эту временную таблицу удалить данные из целевой таблицы:

Удалить дубликаты оставив оригиналы

Теперь попробуем удалить все неуникальные комбинации значений из нескольких столбцов, но оставить оригиналы (те что имеют меньший id):

Теперь в таблице останутся строки только с меньшим id . Для того чтобы оставить самые последние дубликаты, а все остальные дубликаты удалить вместо MIN(id) надо использовать MAX(id) .

Скорость исполнения

На практике была задача применить этот код к таблице в которой около 70 000 000 записей естественно был индекс на нужных столбцах), неуникальных строк было >5 000 000. Код из второй главы выполнялся

Удаление повторений в T-SQL

Потребность в устранении дубликатов из данных встречается очень часто, особенно при решении проблем с качеством данных в средах, где дублирование возникло из-за отсутствия ограничений, которые могли бы обеспечить уникальность данных. Для демонстрации подготовим с помощью следующего кода пример данных с дублирующимися заказами в таблице по имени MyOrders:

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

Результат запроса с отмеченными повторениями

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

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

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

Получить блокировку таблицы.

Выполнить инструкцию SELECT INTO.

Удалить и переименовать объекты.

Воссоздать индексы, ограничения и триггеры.

Есть еще один вариант — отфильтровать только уникальные или только неуникальные строки. Вычисляются обе функции — ROW_NUMBER и RANK — на основе упорядочения по orderid, примерно так:

Результат запроса с фильтрацией с помощью функций ROW_NUMBER и RANK

Заметьте, что в результатах только в одной строке для каждого уникального значения в orderid совпадают номер и ранг строки. К примеру, если надо удалить небольшую часть данных, можно инкапсулировать предыдущий запрос в определение CTE, а во внешнем запросе выполнить инструкцию удаления строк, у которых разные номер строки и ранг:

Приведенные решения не являются единственно возможными. В частности, есть сценарии, в которые предпочтительнее разбить крупную операцию удаления на пакеты с помощью параметра TOP. Но здесь я сосредоточился на решениях с использованием оконных функций.

Удаление или поиск дубликатов (повторяющихся) записей в таблице

Начнем с того, что важно определить, отличаются записи хоть одним полем или нет. Оператор DELETE и вообще SQL не позволяет из двух одинаковых записей удалить только одну – либо все дубликаты, либо ни одного.

Однако, используя специфический для IB номер записи, это можно сделать. Например:

В этом случае используется RDB$DB_KEY – физический номер записи IB. Можно оставить как запись с самым большим DB_KEY, так и с самым меньшим (> или < в последнем условии WHERE).

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

Ссылка на основную публикацию