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

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

Удалить дубликаты можно с помощью DISTINCT . Например у нас есть такая выборка:

SELECT first_name FROM users; first_name ------------ Sean Sean Roman Maxwell Russell Mia Mia 

SELECT DISTINCT first_name FROM users; first_name ------------ Sean Roman Maxwell Russell Mia 

Как видите дубликаты были удалены.

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

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

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

SELECT DISTINCT first_name FROM employee 

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

Удаление дубликатов строк

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

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

Использование

MySQL

WITH duplicates AS ( 
SELECT id, ROW_NUMBER() OVER( 
PARTITION BY firstname, lastname, email 
ORDER BY age DESC 
) AS rownum 
FROM contacts 
) 
DELETE contacts 
FROM contacts 
JOIN duplicates USING(id) 
WHERE duplicates.rownum > 1

PostgreSQL

WITH duplicates AS ( 
SELECT id, ROW_NUMBER() OVER( 
PARTITION BY firstname, lastname, email 
ORDER BY age DESC 
) AS rownum 
FROM contacts 
) 
DELETE FROM contacts 
USING duplicates 
WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

Подробное объяснение

Каким бы качественным ни было приложение, через некоторое время в нем могут появиться дубликаты строк. Поначалу они могут не представлять большой проблемы. Однако при многократном появлении дубликатов строк быстро ухудшается качество работы пользователя, а производительность базы данных снижается из-за увеличения объёма данных. Кроме того, эффективный уникальный индекс, сообщающий базе данных, что поиск можно прекратить после того, как будет найден первая строка, уже не может быть использован. Эти дублирующиеся строки должны быть удалены. Если вставить их было просто, то удалить — гораздо более сложная задача.

Стандартный подход заключается в том, чтобы GROUP BY на дублирующихся столбцах и оставить одну оставшуюся строку, используя значение MIN(id) или MAX(id) . Этот простой способ удаления дублирующихся строк не работает, если необходимо соблюдать дополнительные требования:

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

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

1. Строки разбиваются на разделы по столбцам, указывающим на наличие дублирующейся строки. Для каждой комбинации указанных столбцов автоматически создаётся раздел для сбора дублирующихся строк.
2. Каждый раздел сортируется по нескольким столбцам, чтобы отметить их важность. Если, например, необходимо сохранить только пять последних записей, то строки раздела должны быть отсортированы по дате их создания в порядке убывания.
3. Отсортированным строкам внутри раздела присваивается возрастающий номер с помощью оконной функции ROW_NUMBER .
4. Любая строка может быть удалена в соответствии с желаемым количеством оставшихся строк. Если, например, необходимо сохранить только пять последних строк, то можно удалить любую строку с номером строки больше пяти.

Дополнительные ресурсы

• Документация по MySQL: Операторы DELETE для нескольких таблиц.
• Документация PostgreSQL: Операторы DELETE для нескольких таблиц.