Как исключить дубли в sql
Способы удаления дубликатов в SQL Server
Способы удаления дубликатов в SQL Server
При проектировании объектов, в частности таблиц в БД SQL Server необходимо придерживаться определенных правил: рекомендуется использовать правила нормализации БД; таблица должна иметь первичные ключи, кластерные и некластерные индексы; ограничения для обеспечения целостности данных и производительности. Но даже если следовать этим правилам, мы можем столкнуться с проблемой появления дубликатов в строках таблицы. Кроме этого, возможна ситуация получения дубликатов при импорте данных, когда мы загружаем данные as is в промежуточные таблицы, и далее требуется удалить дублирующие записи перед загрузкой в промышленные таблицы.
Рассмотрим различные способы для очистки данных от дублей. Создадим простую таблицу сотрудников и наполним её несколькими записями.
Как мы видим, в таблице присутствуют дублирующие строки, которые необходимо удалить.
C помощью условия GROUP BY мы группируем данные по определенным столбцам и используем функцию COUNT для подсчета вхождений строк в таблицу.
Например, с помощью следующего запроса, определим записи, которые присутствуют в таблице более 1 раза.
Т.е. сотрудники Алексеев А.А. и Иванов И.И. присутствуют в таблице 3 и 2 раза соответственно.
Удалим дублирующие записи, оставив только строки с MIN id сотрудника.
Выведем оставшиеся записи таблицы, и убедимся, что дубликаты отсутствуют.
Отметим, что данный способ удаления дубликатов возможен в случае таблиц, для которых определен первичный ключ.
Мы можем использовать связку обобщенных табличных выражений и функции ROW_NUMBER() для удаления дубликатов, например следующим образом:
В данном запросе мы используем функцию ROW_NUMBER() с конструкцией PARTITION BY в предложении OVER для нумерации записей, и удаляем записи с пронумерованными значениями > 1, соответствующие дубликатам.
Создадим в 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 Server с помощью скрипта
В этой статье содержится скрипт, который можно использовать для удаления дублирующихся строк из таблицы в Microsoft SQL Server.
Оригинальная версия продукта: SQL Server
Исходный номер КБ: 70956
Сводка
Существует два распространенных метода, которые можно использовать для удаления дублирующихся записей из SQL Server таблицы. Для демонстрации сначала создайте пример таблицы и данных:
Затем попробуйте следующие методы, чтобы удалить дублирующиеся строки из таблицы.
Способ 1
Запустите следующий сценарий:
Этот скрипт принимает следующие действия в данном порядке:
Этот метод прост. Однако для создания дублирующей таблицы в базе данных необходимо иметь достаточно места. Этот метод также накладные расходы, так как данные перемещаются.
Кроме того, если в вашей таблице есть столбец IDENTITY, при восстановлении данных в исходной таблице необходимо использовать set IDENTITY_INSERT ON.
Способ 2
Функция ROW_NUMBER, которая была представлена в Microsoft SQL Server 2005 г., значительно упрощает эту операцию:
Этот скрипт принимает следующие действия в данном порядке:
Из-за выражения скрипт не сортировать разделимые (SELECT NULL) данные на основе каких-либо условий. Если в логике удаления дубликатов необходимо выбрать, какие записи удалять, а какие хранить в соответствии с порядком сортировки других столбцов, для этого можно использовать выражение ORDER BY.
Дополнительные сведения
Метод 2 прост и эффективен по этим причинам:
Однако этот метод не работает в устаревших версиях SQL Server, которые не поддерживают функцию ROW_NUMBER. В этой ситуации следует использовать метод 1 или аналогичный метод.
Способы удаления дубликатов в SQL Server
При проектировании объектов, в частности таблиц в БД SQL Server, необходимо придерживаться определенных правил. Однако, даже если следовать данным правилам существует вероятность появления дубликатов в строках таблиц. Данная статья посвящена различным способам очистки данных от дубликатов.
При проектировании объектов, в частности таблиц в БД SQL Server необходимо придерживаться определенных правил: рекомендуется использовать правила нормализации БД; таблица должна иметь первичные ключи, кластерные и некластерные индексы; ограничения для обеспечения целостности данных и производительности. Но даже если следовать этим правилам, мы можем столкнуться с проблемой появления дубликатов в строках таблицы. Кроме этого, возможна ситуация получения дубликатов при импорте данных, когда мы загружаем данные as is в промежуточные таблицы, и далее требуется удалить дублирующие записи перед загрузкой в промышленные таблицы.
Рассмотрим различные способы для очистки данных от дублей. Создадим простую таблицу сотрудников и наполним её несколькими записями.
Как мы видим, в таблице присутствуют дублирующие строки, которые необходимо удалить.
C помощью условия GROUP BY мы группируем данные по определенным столбцам и используем функцию COUNT для подсчета вхождений строк в таблицу.
Например, с помощью следующего запроса, определим записи, которые присутствуют в таблице более 1 раза.
Т.е. сотрудники Алексеев А.А. и Иванов И.И. присутствуют в таблице 3 и 2 раза соответственно.
Удалим дублирующие записи, оставив только строки с MIN id сотрудника.
Выведем оставшиеся записи таблицы, и убедимся, что дубликаты отсутствуют.
Отметим, что данный способ удаления дубликатов возможен в случае таблиц, для которых определен первичный ключ.
Мы можем использовать связку обобщенных табличных выражений и функции ROW_number() для удаления дубликатов, например следующим образом:
В данном запросе мы используем функцию ROW_number() с конструкцией partition BY в предложении OVER для нумерации записей, и удаляем записи с пронумерованными значениями > 1, соответствующие дубликатам.
Создадим в 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, которые могут быть использованы в работе в зависимости от задачи и объема данных.
При больших объемах дубликатов в данных целесообразно рассмотреть возможность сохранения уникальных значений в промежуточную таблицу, очистку рабочей таблицы, и возврат оставленных уникальных записей.
Как избавиться от дублей в базе данных (на примере MS SQL)
Всем привет! Меня зовут Евгений, я занимаюсь разработкой и проектированием в Ozon. Больше всего работаю с MS SQL и C#, но попадаются и другие СУБД и языки программирования.
Заказы пользователей обрабатываются разными системами, которые взаимодействуют между собой. Это порождает необходимость учитывать многочисленные интеграции и приводит к проблеме дублирования данных.
Взаимодействие информационных систем
Я расскажу об одном таком случае, когда наша команда потратила много времени и сил, но всё-таки нашла оптимальный способ решения проблемы дублирования данных.
Но сначала позвольте погрузить вас немного в предметную область — объясню, на примере чего будет демонстрироваться проблема дублирования данных, и освещу некоторые методы её решения.
расскажу немного о специфике предметной области;
рассмотрим популярные варианты борьбы с дублированием данных;
опишу, в чём заключается наш способ;
приведу пример, как это всё работает.
Примеры будут приведены для MS SQL Server. Однако аналогичное решение можно реализовать на любой другой СУБД с учётом её особенностей.
Предметная область: логистика заказов
У нас в Ozon все заказы делятся по отправлениям:
Состав заказа
В отправлении может быть один или несколько типов товаров.
В базе данных существуют отдельные таблицы для заказов и для отправлений.
Заказы бывают двух типов:
Одноместный заказ, состоящий из одного отправления (Упаковка 1 — одно отправление):
Одноместный заказ из одного отправления (упаковки)
Многоместный заказ, состоящий из двух и более отправлений (Упаковка 1 — одно отправление, Упаковка 2 — второе отправление):
Многоместный заказ из нескольких отправлений (упаковок)
При совершении над отправлением определённых действий происходит тарификация — расчёт суммы за предоставленные услуги. Эти действия записываются в отдельную таблицу операций, а сумма попадает в соответствующее поле этой таблицы.
В контексте статьи нас интересуют два основных действия над отправлениями с точки зрения тарификации: выдача и возврат. Они бывают полными и частичными. Полные — это когда полностью выдали или полностью вернули заказ, а частичные — когда заказ частично выдан или частично возвращён после полной выдачи.
Все отправления записываются в систему LogOzon, в которой заказы существуют только на уровне базы данных. Таким образом, LogOzon имеет дело только с отправлениями и ничего не знает про заказы, с которыми работают другие системы Ozon. Получается, что изменения на уровне всего заказа нельзя сделать в LogOzon; для этого нужно внести корректировки в составные части заказа, а именно в отправления. Так сложилось исторически при создании и масштабировании новых сервисов.
Асинхронная тарификация отправлений
Проблема дублирования данных возникает в отношении многоместных заказов.
Сумма тарификации по бизнес-требованиям должна проставляться целиком только на одном отправлении одного типа тарификации в рамках заказа (выдача или возврат).
И тут мы подходим к сути проблемы. Как заставить асинхронные процессы из других систем Ozon обновлять сумму только в рамках одного типа тарификации, то есть на одном отправлении? Если не делать синхронизацию изменений, то можно получить дублирование сумм для разных отправлений заказа по одному и тому же типу тарификации, то есть когда на одном или нескольких отправлениях в рамках одного заказа будут проставлены одни и те же суммы.
Дублирование тарификаций отправлений
Выбираем метод борьбы с дублированием данных
На практике я встречал два основных способа не допустить дублирования данных:
Навесить ограничение на уникальность для нужной комбинации полей в таблице и обрабатывать исключения при нарушении этого ограничения (хорошо работает при вставках и обновлениях данных).
Поместить участок кода, где может возникнуть дублирование данных, в транзакцию с уровнем изоляции «сериализуемая» (SERIALIZABLE) (хорошо работает при добавлениях и обновлениях в монопольном режиме).
Второй способ дорогостоящий, а первый не всегда возможно применить (например, при сложной логике определения уникальности записи в таблице), и он требует обработки исключений, которые возникнут в случае появления дублирующей строки для ограничения уникальности.
А что, если сделать гибридный вариант?
И тут мы подходим непосредственно к алгоритму борьбы с дублированием данных в нашей задаче.
На самом деле можно делать пересчёт и постфактум (то есть жить с дублирующими суммами), но в нашем случае это плохое решение, так как разные сервисы будут считывать невалидные данные до момента пересчёта, что приведёт к некорректным счетам;
мы используем уровень изоляции транзакций снимками (SNAPSHOT).
Алгоритм предотвращения дублирования данных
Верхнеуровнево алгоритм для многоместных заказов выглядит следующим образом:
Создаём таблицу, в которой будет содержаться вся необходимая информация для предотвращения дублирования сумм при обновлениях.
Делаем хранимую процедуру, которая будет в монопольном режиме добавлять в новую таблицу запись с проверкой и возвращать статус, отражающий, удалось ли добавить запись или нет (если нет, значит, запись уже была добавлена).
Создаём хранимую процедуру для записи суммы тарифа. При каждом проставлении суммы сравниваем её с суммой из новой таблицы и сначала обновляем сумму в новой таблице, если там она была 0:
в случае успешного обновления обновляем сумму в операции;
если в новой таблице уже указана сумма, отличная от 0, и отправление в обработке отличается от отправления, на котором проставлена сумма (то есть кто-то уже обновил сумму и нам не нужны дубли), обнуляем её;
в противном случае ничего не делаем.
Схема алгоритма
Примечание: если нужно изменить уже проставленную сумму, необходимо это сделать на протарифицированном отправлении, информация о котором находится в новой таблице.
Создаём таблицу с информацией для предотвращения дублирования сумм при обновлениях
Создадим таблицу dbo.LogisticOrderMultiPostingPrincipalTariff следующим образом:
Определение таблицы dbo.LogisticOrderMultiPostingPrincipalTariff
Таблица dbo.LogisticOrderMultiPostingPrincipalTariff содержит следующие поля:
LogisticOrderID — идентификатор заказа;
TariffTypeID — идентификатор типа тарификации;
ArticleID — идентификатор отправления;
OperationID — идентификатор операции;
Amount — сумма тарификации;
InsertUTCDate — дата и время создания записи в UTC (служебное поле).
Создаём хранимую процедуру для монопольного добавления записи в таблицу
Создадим хранимую процедуру dbo.AddLogisticOrderMultiPostingPrincipalTariff, которая будет в монопольном режиме добавлять в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff запись с проверкой на существование записи с заданной парой (заказ, тип тарификации) и возвращать статус, показывающий, удалось добавить запись или нет (если нет, то запись уже была добавлена):
Определение хранимой процедуры dbo.AddLogisticOrderMultiPostingPrincipalTariff
Хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:
LogisticOrderID — идентификатор заказа;
TariffTypeID — идентификатор типа тарификации;
ArticleID — идентификатор отправления;
OperationID — идентификатор операции;
IsResult — выходной параметр, возвращающий значение 1 (успех вставки) или 0 (неуспех — когда уже есть запись с таким же заказом и типом тарификации).
Обратите внимание, что при добавлении записи в таблицу dbo.LogisticOrderMultiPostingPrincipalTariff в хранимой процедуре dbo.AddLogisticOrderMultiPostingPrincipalTariff сумма тарификации Amount принимает значение 0, так как проставление этой суммы произойдёт позже через обновление.
Создаём хранимую процедуру для записи суммы тарифа
Создадим ещё одну хранимую процедуру. При каждой записи суммы тарифа сравниваем её с суммой из новой таблицы dbo.LogisticOrderMultiPostingPrincipalTariff и сначала обновляем в ней сумму Amount, если там она 0. В случае успешного обновления меняем сумму в операции. В противном случае (если в операции стоит иная сумма, то есть кто-то её уже обновил) — обнуляем её.
Создадим эту хранимую процедуру dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff:
Определение хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff
Хранимая процедура dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff принимает следующие параметры:
LogisticOrderID — идентификатор заказа;
TariffTypeID — идентификатор типа тарификации;
ArticleID — идентификатор отправления;
OperationID — идентификатор операции;
Amount — сумма тарификации;
IsResult — выходной параметр, возвращающий значение:
1 — успех обновления;
0 — неуспех, т е когда уже есть запись с нужной суммой
NULL — когда не нужно ничего менять, например при попытке записать сумму, которая уже указана, в то же отправление, на котором она указана.
Эта хранимая процедура позволяет изменить сумму тарификации только для того отправления, у которого ранее была проставлена ненулевая сумма.
Пример использования созданных хранимых процедур
Давайте посмотрим, как пользоваться хранимыми процедурами, которые мы создали.
У нас все отправления заказа создаются и переходят в статус «Сформировано» до того, как хотя бы одно из них куда-либо поедет. Напомним, что тарификация происходит при выдаче или возврате товара, когда сформированы все отправления заказа.
При тарификации у нас на входе есть следующие параметры:
LogisticOrderID — идентификатор заказа;
TariffTypeID — идентификатор типа тарификации;
ArticleID — идентификатор отправления;
OperationID — идентификатор операции.
На старте тарификации мы определяем, является ли заказ многоместным, то есть состоит ли он более чем из одного отправления. Если да, то вызывается хранимая процедура dbo.AddLogisticOrderMultiPostingPrincipalTariff для монопольного добавления записи о том, что мы стали тарифицировать заказ:
По значению переменной @IsResult мы узнаём, получилось ли добавить запись. В нашем случае это нужно просто для информации и не используется для какой-либо логики в коде.
Далее мы вычисляем сумму тарифа по определённому алгоритму и кладём её в переменную @Amount.
На следующем этапе мы записываем ненулевую сумму тарификации @Amount в новую таблицу dbo.LogisticOrderMultiPostingPrincipalTariff через вызов хранимой процедуры dbo.UpdAmountLogisticOrderMultiPostingPrincipalTariff:
Теперь смотрим на вернувшееся значение в переменной @IsResult:
если @IsResult = 1, то сумму тарифа @Amount не меняем;
если @IsResult = 0, то обнуляем сумму тарифа @Amount и после этого записываем получившуюся сумму тарификации @Amount в нужную операцию отправления заказа;
если @IsResult IS NULL, то ничего не делаем.
Синхронизация проставления суммы тарификации в отправлении в рамках заказа
В случае же с одноместным заказом мы просто записываем сумму тарификации @Amount в соответствующую операцию отправления заказа.
Ключ к уменьшению количества дублей — уровень изоляции транзакций
Смотрите, что у нас получилось. Раньше стоимость доставки и возврата рассчитывалась по составу операций в рамках одного отправления. Теперь мы научились анализировать все отправления заказа для определения конечной операции с ним. Благодаря этому стало возможным автоматически рассчитывать корректные суммы для многоместных заказов.
К этому решению мы пришли не сразу. Изначально мы искали способ, как избежать появления дублей в строках. А нужно было правильно применить уровень изоляции транзакций — так, чтобы блокировка на вставку и обновление данных была минимальной, то есть с выделением нового объекта для блокировки и синхронизации, которым стала новая таблица, описанная выше.
Этот способ — гибридный: мы используем ограничение уникальности по набору (заказ, тип тарификации) и монопольный блок на вставку и обновление на вставку и обновление данных.
Результат правильной тарификации
Приведённое решение можно использовать на разных СУБД в задачах, где происходят асинхронные вставки и обновления данных разными процессами и из-за этого возможно дублирование данных.
Такие задачи распространены во многих сферах:
Во всех этих областях тоже есть сущности, которые делятся на составные части, и при интеграции их в разные системы тоже бывает необходима синхронизация асинхронных процессов, будь то запись суммы или любого другого значения либо признака.
Но есть у данного подхода и недостаток: возникает очередь ожидания доступа к таблице, пусть и краткосрочная. С другой стороны, если есть возможность, то можно позволить создавать дубли данных при последующей автоматизации разрешения конфликта дублей или же обрабатывать исключение попытки вставки дублирующих записей на стороне сервиса.
Ссылки по теме
Транзакции MS SQL Server: как вызывать транзакции, откатывать и фиксировать их, какие существуют уровни изоляции транзакций и различия между ними.
Предложение OUTPUT: как получить то, что вставили, обновили (заодно смотрим, что было до обновления) или удалили при различных командах модификации данных.
MS SQL Server: общая документация по СУБД, которую я выбрал для реализации описанного метода предотвращения дублирования данных.
Удаление повторений в T-SQL
Потребность в устранении дубликатов из данных встречается очень часто, особенно при решении проблем с качеством данных в средах, где дублирование возникло из-за отсутствия ограничений, которые могли бы обеспечить уникальность данных. Для демонстрации подготовим с помощью следующего кода пример данных с дублирующимися заказами в таблице по имени MyOrders:
Представьте, что вам нужно устранить дублирование данных, оставив только по одному экземпляру с уникальным значением orderid. Дублируюшиеся номера отмечаются с помощью функции ROW_NUMBER с секционированием по предположительно уникальному значению (в нашем случае orderid) и с использованием произвольного упорядочения, если вам неважно, какую строку оставить, а какую удалить. Вот код, в котором функция ROW_NUMBER отмечает дубликаты:
Затем нужно рассмотреть разные варианты в зависимости от количества строк, которые нужно удалить, процента размерности таблицы, какое это количество составляет, активности производственной среды и других обстоятельств. При небольшом числе удаляемых строк обычно достаточно использовать операцию удаления с полным протоколированием, в которой удаляются все экземпляры, у которых номер строки больше единицы:
Но если число удаляемых строк большое — особенно когда оно составляет большую долю строк таблицы, удаление с полной записью операции в журнале будет слишком медленным. В этом случае стоит подумать об использовании операции неполного протоколирования, такой как SELECT INTO, для копирования уникальных строк (с номером 1) в другую таблицу. После этого оригинальная таблица удаляется, затем новой таблице присваивается имя удаленной таблицы, воссоздаются ограничения индексы и триггеры. Вот код законченного решения:
Для простоты я не добавил сюда никакого контроля транзакций, но нужно всегда помнить, что с данными могут одновременно работать несколько пользователей. При реализации этого метода в производственной среде нужно соблюдать следующую последовательность:
Получить блокировку таблицы.
Выполнить инструкцию SELECT INTO.
Удалить и переименовать объекты.
Воссоздать индексы, ограничения и триггеры.
Заметьте, что в результатах только в одной строке для каждого уникального значения в orderid совпадают номер и ранг строки. К примеру, если надо удалить небольшую часть данных, можно инкапсулировать предыдущий запрос в определение CTE, а во внешнем запросе выполнить инструкцию удаления строк, у которых разные номер строки и ранг:
Приведенные решения не являются единственно возможными. В частности, есть сценарии, в которые предпочтительнее разбить крупную операцию удаления на пакеты с помощью параметра TOP. Но здесь я сосредоточился на решениях с использованием оконных функций.
По завершении надо выполнить следующий код очистки: