Избавиться от дубликатов sql

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

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

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

Сводка

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

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

Способ 1

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

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

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

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

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

Способ 2

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

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

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

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

Дополнительные сведения

Метод 2 прост и эффективен по этим причинам:

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

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

Источник

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

Аннотация

Таблицы Microsoft SQL Server никогда не должны содержать повторяющихся строк и неуникальных первичных ключей. Для краткости в этой статье мы будем иногда называть первичные ключи просто ключами, но подразумеваться всегда будут именно первичные ключи. Повторяющиеся ключи нарушают целостность сущностей, поэтому должны быть запрещены в реляционной системе. SQL Server содержит ряд механизмов, обеспечивающих целостность сущностей, включая индексы, ограничения UNIQUE, ограничения PRIMARY KEY и триггеры.

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

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

Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.

Если используется SQL Server 2000 или SQL Server 2005, может быть выведено следующее сообщение об ошибке.

Сообщение 1505, уровень 16, состояние 1 Операция CREATE UNIQUE INDEX прервана, так как обнаружен повторяющийся ключ для имени объекта «%1!s!» и имени индекса «%2!s!». Повторяющееся значение ключа: %ls.

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

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

Проблема дублирования ключей будет рассмотрена на примере следующей таблицы с повторяющимся первичным ключом. В данной таблице первичным ключом являются два столбца (col1, col2). Создать уникальный индекс или ограничение PRIMARY KEY в данном случае невозможно, поскольку две строки имеют повторяющиеся первичные ключи. Можно воспользоваться процедурой, описанной ниже, чтобы обнаружить и удалить дубликаты.

Первым делом следует определить, какие строки имеют повторяющиеся значения первичного ключа.

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

Если повторяющихся значений первичного ключа мало, лучше всего удалить их по отдельности вручную. например:

Значение rowcount должно быть на единицу меньше, чем число дубликатов определенного значения ключа. В данном примере дубликатов два, поэтому переменной rowcount присваивается значение 1. Значения col1 и col2 берутся из результата выполнения запроса GROUP BY, приведенного выше. Если запрос GROUP BY возвращает несколько строк, запрос «set rowcount» нужно будет выполнить по одному разу для каждой из этих строк. Переменной rowcount при каждом запуске нужно должно присваиваться значение на единицу меньше числа дубликатов определенного значения первичного ключа.

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

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

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

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

Выделите повторяющиеся строки в отдельную таблицу, удаляя при этом дубликаты, например:

Теперь таблица holddups должна содержать уникальные первичные ключи, однако это условие не будет выполнено, если таблица t1 включает повторяющиеся первичные ключи и уникальные строки (как в приведенном выше примере с номерами социального обеспечения). Убедитесь в том, что каждый ключ в таблице holddups уникален и что у вас нет повторяющихся ключей при наличии уникальных строк. Если это так, подумайте, какие из строк нужно сохранить для определенного повторяющегося значения ключа. Например, запрос

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

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

Поместите уникальные строки обратно в исходную таблицу, например:

Источник

Способы удаления дубликатов в SQL Server

Способы удаления дубликатов в SQL Server

При проектировании объектов, в частности таблиц в БД SQL Server необходимо придерживаться определенных правил: рекомендуется использовать правила нормализации БД; таблица должна иметь первичные ключи, кластерные и некластерные индексы; ограничения для обеспечения целостности данных и производительности. Но даже если следовать этим правилам, мы можем столкнуться с проблемой появления дубликатов в строках таблицы. Кроме этого, возможна ситуация получения дубликатов при импорте данных, когда мы загружаем данные as is в промежуточные таблицы, и далее требуется удалить дублирующие записи перед загрузкой в промышленные таблицы.

Рассмотрим различные способы для очистки данных от дублей. Создадим простую таблицу сотрудников и наполним её несколькими записями.

Как мы видим, в таблице присутствуют дублирующие строки, которые необходимо удалить.

• Удаление дубликатов с использованием агрегатных функций

C помощью условия GROUP BY мы группируем данные по определенным столбцам и используем функцию COUNT для подсчета вхождений строк в таблицу.

Например, с помощью следующего запроса, определим записи, которые присутствуют в таблице более 1 раза.

Т.е. сотрудники Алексеев А.А. и Иванов И.И. присутствуют в таблице 3 и 2 раза соответственно.

Удалим дублирующие записи, оставив только строки с MIN id сотрудника.

Выведем оставшиеся записи таблицы, и убедимся, что дубликаты отсутствуют.

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

• Удаление дубликатов используя обобщенные табличные выражения (CTE)

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

В данном запросе мы используем функцию ROW_NUMBER() с конструкцией PARTITION BY в предложении OVER для нумерации записей, и удаляем записи с пронумерованными значениями > 1, соответствующие дубликатам.

• Удаление дубликатов с использованием инструментария SSIS пакетов.

Создадим в SQL Server Data Tools новый пакет integration Services.

Добавим в пакет элемент «OLE DB Source», откроем редактор OLE DB Source, в графе Connection Manager укажем реквизиты экземпляра СУБД и БД, и наименование исходной таблицы с данными, содержащей дубликаты.

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

Добавим оператор «Sort», и выделим поля, в которых присутствуют дублирующие данные.

Установим галку «Remove rows with duplicate sort values» для удаления дубликатов.

Добавим элемент «OLE DB Destination», в котором укажем целевую таблицу для записей результата очистки данных.

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

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

В данной статье мы рассмотрели различные способы удаления дубликатов записей в таблицах БД SQL Server, которые могут быть использованы в работе в зависимости от задачи и объема данных.

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 которых больше чем выбранный во временную таблицу.

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

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

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

Источник