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

    Моисеенко С.И. (13-06-2009)

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

Пусть имеется следующая таблица 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

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

    06-10-2009

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

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

Разумеется, это правильно, но мы можем отказаться от ранжирования (в ущерб обучению :-)), выполнив "псевдоранжирование". Дело в том, что есть возможность выполнить независимую нумерацию для каждой группы, если в предложении 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;

Dzone.com