Как исключить дублирование в sql

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

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

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders; GO SELECT * INTO Sales.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;

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

SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

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

WITH C AS ( SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders ) DELETE FROM C WHERE n > 1;

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

WITH C AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders ) SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename 'Sales.OrdersTmp', 'MyOrders'; -- воссоздание индексов, ограничений и триггеров

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

1. Открыть транзакцию.
2. Получить блокировку таблицы.
3. Выполнить инструкцию SELECT INTO.
4. Удалить и переименовать объекты.
5. Воссоздать индексы, ограничения и триггеры.
6. Зафиксировать транзакцию.

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

SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;

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

WITH C AS ( SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders ) DELETE FROM C WHERE rownum <> rnk;

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

По завершении надо выполнить следующий код очистки:

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;

Исключение дубликатов

Иногда в таблицах встречаются дубликаты, как например имена сотрудников. Для исключения повторяющихся строк, нужно после ключевого слова SELECT написать DISTINCT .

Чтобы получить список уникальных имен сотрудников, необходимо выполнить запрос:

SELECT DISTINCT first_name FROM employee 

first_name
Анна
Лука
Ольга
.

Удаление повторяющихся строк из SQL Server с помощью сценария

В этой статье приведен сценарий, который можно использовать для удаления повторяющихся строк из таблицы в Microsoft SQL Server.

Оригинальная версия продукта: SQL Server
Оригинальный номер базы знаний: 70956

Сводка

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

create table original_table (key_value int ) insert into original_table values (1) insert into original_table values (1) insert into original_table values (1) insert into original_table values (2) insert into original_table values (2) insert into original_table values (2) insert into original_table values (2) 

Затем попробуйте следующие методы, чтобы удалить повторяющиеся строки из таблицы.

Способ 1

Запустите следующий сценарий:

SELECT DISTINCT * INTO duplicate_table FROM original_table GROUP BY key_value HAVING COUNT(key_value) > 1 DELETE original_table WHERE key_value IN (SELECT key_value FROM duplicate_table) INSERT original_table SELECT * FROM duplicate_table DROP TABLE duplicate_table 

Этот сценарий выполняет следующие действия в указанном порядке:

• Перемещает один экземпляр любой повторяющейся строки в исходной таблице в дублирующую таблицу.
• Удаляет все строки из исходной таблицы, которые также находятся в дублирующей таблице.
• Перемещает строки в дублирующей таблице обратно в исходную таблицу.
• Удаляет дублирующую таблицу.

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

Кроме того, если таблица содержит столбец IDENTITY, при восстановлении данных в исходной таблице необходимо использовать SET IDENTITY_INSERT ON.

Способ 2

Функция ROW_NUMBER, добавленная в Microsoft SQL Server 2005, значительно упрощает эту операцию:

DELETE T FROM ( SELECT * , DupRank = ROW_NUMBER() OVER ( PARTITION BY key_value ORDER BY (SELECT NULL) ) FROM original_table ) AS T WHERE DupRank > 1 

Этот сценарий выполняет следующие действия в указанном порядке:

• Использует ROW_NUMBER функцию для разделения данных на основе key_value , в роли которого может выступать один или несколько столбцов, разделенных запятыми.
• Удаляет все записи, которые получили значение DupRank , превышающее 1. Это указывает на то, что записи являются дубликатами.

Из-за выражения (SELECT NULL) сценарий не сортирует разделенные данные на основе каких-либо условий. Если ваша логика удаления дубликатов требует выбора того, какие записи удалить, а какие оставить, основываясь на порядке сортировки других столбцов, можно использовать для этого выражение ORDER BY.

Дополнительная информация

Метод 2 является простым и эффективным по следующим причинам:

• Для этого не требуется временно копировать повторяющиеся записи в другую таблицу.
• При этом не требуется объединения исходной таблицы с самой собой (например, с помощью подзапроса, который возвращает все повторяющиеся записи с помощью комбинации GROUP BY и HAVING).
• Для достижения оптимальной производительности в таблице должен быть соответствующий индекс, который использует key_value в качестве ключа индекса и содержит любые столбцы сортировки, которые могли использоваться в выражении ORDER BY.

Однако этот метод не работает в устаревших версиях SQL Server, которые не поддерживают функцию ROW_NUMBER. В этом случае следует использовать метод 1 или аналогичный метод.

Обратная связь

Были ли сведения на этой странице полезными?

Удаление повторяющихся строк в таблице запросом SQL

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

В этой статье рассмотрим задачу удаления дублирующих строк в таблице БД. Сразу же отмечу, что речь идет о необходимости удалить именно повторяющиеся строки. Например, записи в таблице заказов с полями «код заказа», «код товара», «код покупателя», «дата заказа» могут различаться только кодом заказа, так как все же один покупатель в один день может заказать один и тот же товар несколько раз. А главный показатель здесь, что все правильно – наличие ключевого поля.

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

Пример явно избыточной таблицы:

id (код записи)	country_id (код страны)	city_name (код города)
1	1	Москва
2	1	Хабаровск
3	1	Самара
4	1	Кисловодск
5	1	Хабаровск
6	1	Самара
7	1	Кисловодск
8	1	Кисловодск

Теперь рассмотрим, как можно решить эту проблему. Здесь можно применить несколько методов.

1. Можно написать функцию для сравнения и перебора всех данных. Это долго, да и писать код для одноразового использования не всегда хочется.

2. Другое решение – создать запрос на выборку с группировкой данных, так чтобы получить только уникальные строки:

SELECT country_id, city_name FROM mytable GROUP BY country_id, city_name

Получаем следующую выборку:

country_id	city_name
1	Кисловодск
1	Москва
1	Самара
1	Хабаровск

Затем, полученный набор данных записываем в другую таблицу.

3. В указанных решениях применяется дополнительный программный код или дополнительные таблицы. Однако, было бы удобней сделать все, используя только запросы SQL без дополнительных таблиц. И вот пример такого решения:

DELETE a.* FROM mytable a, (SELECT b.country_id, b.city_name, MIN(b.id) mid FROM mytable b GROUP BY b.country_id, b.city_name ) c WHERE a.country_id = c.country_id AND a.city_name = c.city_name AND a.id > c.mid

После выполнения такого запроса в таблице останутся только уникальные записи:

id	country_id	city_name
1	1	Москва
2	1	Хабаровск
3	1	Самара
4	1	Кисловодск

Теперь разберемся подробнее, как все это работает. При запросе на удаление, необходимо задать условие, которое укажет какие данные нужно удалить, а какие оставить. Нам необходимо удалить все не уникальные записи. Т.е. если существует несколько одинаковых записей (одинаковые они, если у них равны значения country_id и city_name), то нужно взять одну из строк, запомнить ее код и удалить все записи с такими же значениями country_id и city_name, но другим кодом (id).

Строка SQL запроса:

DELETE a.* FROM mytable a,

указывает, что удаление будет производиться из таблицы mytable.

Затем запрос на выборку формирует вспомогательную таблицу, где мы группируем записи так, чтобы все записи были уникальными:

 (SELECT b.country_id, b.city_name, MIN(b.id) mid FROM mytable b GROUP BY b.country_id, b.city_name ) c

MIN(b.id) mid – формирует столбец mid (сокращение min id), в который вносятся минимальное значение id, в каждой подгруппе.

В результате получается таблица, содержащая уникальные записи и id первой строки для каждой группы дублирующих записей.

country_id	city_name	mid
1	Кисловодск	4
2	Москва	1
3	Самара	3
4	Хабаровск	2

Теперь мы имеем две таблицы. Одну общую, содержащую все записи. Из нее будут удаляться лишние строки. Вторая содержит информацию о строках, которые нужно сохранить.

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

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

ALTER TABLE ` mytable` ADD `id` INT( 11 ) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY ( `id` )

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

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