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;

Удаление повторяющихся строк из 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 или аналогичный метод.

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

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

Как удалить дубликаты строк из таблицы?

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

Пусть имеется следующая таблица T:

name John Smith John Smith Smith Tom

Для простоты я не включаю сюда другие столбцы, предполагая, что данные в них однозначно определяются значением в столбце name. Требуется сделать столбец name уникальным (скажем, первичным ключом), предварительно удалив дубликаты.

Распространенным решением данной проблемы является создание вспомогательной таблицы требуемой структуры, в которую копируются уникальные строки из таблицы T с последующим удалением таблицы T и переименованием вспомогательной таблицы. Ниже приводится код на языке T-SQL, реализующий данный алгоритм.

 CREATE TABLE Ttemp(name VARCHAR(50) NOT NULL PRIMARY KEY); GO INSERT INTO Ttemp SELECT DISTINCT * FROM T; GO DROP TABLE T; GO EXEC sp_rename 'Ttemp', 'T'; GO SELECT * FROM T; 

В результате получим то, что и требовалось:

name John Smith Tom

При этом ограничение первичного ключа будет препятствовать появлению дубликатов впоследствии.

А можно ли обойтись без создания новой таблицы? Можно. Например, с помощью такого алгоритма:
— добавить новый столбец типа счетчик (IDENTITY), который перенумерует все имеющиеся строки в таблице;
— из каждой группы строк с одинаковым значением в столбце name удалить все строки за исключением строки с максимальным номером (или минимальным — это все равно, т.к. мы имеем дело с дубликатами);
— удалить вспомогательный столбец;
— наложить ограничение.

Вот пример реализации такого подхода:

 ALTER TABLE T ADD id INT IDENTITY(1,1); GO DELETE FROM T WHERE id < (SELECT MAX(id) FROM T AS T1 WHERE T.name = T1.name ); GO ALTER TABLE T DROP COLUMN id; GO ALTER TABLE T ALTER COLUMN name VARCHAR(50) NOT NULL; GO ALTER TABLE T ADD CONSTRAINT T_PK PRIMARY KEY(name); GO 

А если без создания дополнительного столбца? Опять ответ утвердительный, но тут нам потребуются новые возможности языка, специфицированные в стандарте ANSI SQL-99. Идея состоит в том, чтобы создавать не постоянный столбец в таблице, который потом потребуется удалять, а виртуальный (вычисляемый). Этот столбец мы создадим с помощью оконных функций, присвоив ранг каждой строке внутри окна, определяемого равенством значений в столбце name. Наконец, мы удалим все строки с рангом выше 1.

Давайте подробно рассмотрим построение запроса на удаление дубликатов этим методом.

1. Нумерация строк

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

 SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T 

В результате получим

name num John 1 John 2 Smith 3 Smith 4 Smith 5 Tom 6

2. Ранжирование строк внутри групп дубликатов

К сожалению, запрещено (MS SQL Server) использовать оконные функции внутри оконных функций. Т.е. мы не можем написать так:

 SELECT name, RANK() OVER (PARTITION BY name ORDER BY ROW_NUMBER() OVER(ORDER BY name)) rnk FROM T; 

а потому используем подзапрос:

 SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X; 

Ниже представлен результат этого запроса.

name rnk John 1 John 2 Smith 1 Smith 2 Smith 3 Tom 1

3. Удаление дубликатов из виртуальной таблицы

Недопустимо удалять записи из запроса, т.е. мы не можем воспользоваться таким вариантом:

 DELETE FROM (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X WHERE rnk > 1; 

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

 CREATE VIEW Tview AS SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X GO DELETE FROM Tview WHERE rnk > 1; 

"Опять что-то создавать", - скажете вы. Не обязательно, и, чтобы доказать это, нам помогут общие табличные выражения (CTE), которые можно назвать виртуальными представлениями. CTE, хотя и не являются сохраняемыми в базе данных объектами, могут использоваться с операторами обновления. В результате все сводится к одному запросу:

 WITH CTE AS (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num FROM T ) X ) DELETE FROM CTE WHERE rnk > 1; GO 

Не забудьте только создать первичный ключ. 🙂

Попал по внешней ссылке на эту статью и решил себе возразить. 🙂

Вот эта фраза: "Мы не можем сразу ранжировать строки просто потому, что их не по чем ранжировать."

Разумеется, это правильно, но мы можем отказаться от ранжирования (в ущерб обучению :-)), выполнив "псевдоранжирование". Дело в том, что есть возможность выполнить независимую нумерацию для каждой группы, если в предложении OVER для функции ROW_NUMBER использовать конструкцию PARTITION BY. Итак, можно вообще обойтись без функции RANK, если выполнить разбиение по name

 SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) FROM T; 

Это упростит все последующие запросы, в частности, последнее решение задачи удаления дубликатов можно переписать в виде:

 WITH CTE AS ( SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk FROM T ) DELETE FROM CTE WHERE rnk > 1; 

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

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

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

DELETE FROM XXX T1 WHERE EXISTS
(SELECT * FROM XXX T2 WHERE
(T2.column1 = T1.column1 or (T2.column1 is null and T2.column1 is null)) AND
(T2.column2 = T1.column2 or (T2.column2 is null and T2.column2 is null)) AND
(. ) AND
( T2.RDB$DB_KEY > T1.RDB$DB_KEY ))

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

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

SELECT * FROM TABLE T1
WHERE (SELECT COUNT(*)
FROM TABLE T2
WHERE T1.FIELD = T2.FIELD ) > 1

Однако этот запрос не совсем эффективен. Вместо него выгоднее использовать процедуру, которая будет выполняться намного быстрее:
(Ann Harrison)

for select field
from table
group by field
having count (field) > 1
into :fld
do
begin
for select field
from table
where field = :fld
into :fld1
do
begin
suspend;
end
end

Но хранимая процедура не всегда удобна. Также можно использовать уникальный идентификатор записи RDB$DB_KEY:
(Josef Marie M. Alba)

SELECT * FROM TABLE T1
WHERE EXISTS
(SELECT FIELD FROM TABLE T2
WHERE T1.FIELD = T2.FIELD AND
T1.RDB$DB_KEY != T2.RDB$DB_KEY )

Copyright iBase.ru © 2002-2023