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

Работа с базами данных в .NET Framework --- Оконные функции 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;