Разработка
NTA

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

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

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

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

CREATE TABLE Employee ( [id] int identity(1,1), [Фамилия] nvarchar(100), [Имя] nvarchar(100), [Отчество] nvarchar(100), [Дата рождения] date, ) GO Insert into Employee ([Фамилия],[Имя],[Отчество],[Дата рождения]) values (N'Алексеев',N'Алексей',N'Алексеевич','1990-03-01'), (N'Алексеев',N'Алексей',N'Алексеевич','1990-03-01'), (N'Алексеев',N'Алексей',N'Алексеевич','1990-03-01') (N'Иванов',N'Иван',N'Иванович','1985-01-01'), (N'Иванов',N'Иван',N'Иванович','1985-01-01'), (N'Петров',N'Петр',N'Петрович','1988-02-01'),

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

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

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

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

Select [Фамилия], [Имя], [Отчество], [Дата рождения], count(*) as CNT FROM NTA.dbo.Employee GROUP BY [Фамилия], [Имя], [Отчество], [Дата рождения] having count(*) > 1

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

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

Delete FROM NTA.dbo.Employee Where id not in ( select min(id) as MinRowID FROM NTA.dbo.Employee group by [Фамилия],[Имя],[Отчество],[Дата рождения] )

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

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

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

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

WITH CTE ([Фамилия], [Имя], [Отчество], [Дата рождения], [Нумерация] ) AS (SELECT [Фамилия], [Имя], [Отчество], [Дата рождения], ROW_NUMBER () OVER (PARTITION BY [Фамилия], [Имя], [Отчество], [Дата рождения] ORDER BY id) AS [Нумерация] FROM NTA.dbo.Employee) DELETE FROM CTE WHERE [Нумерация] > 1

В данном запросе мы используем функцию 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, которые могут быть использованы в работе в зависимости от задачи и объема данных.

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

0
1 комментарий
Популярные
По порядку
Prolis Labkk

Delete FROM NTA.dbo.Employee Where id not in
- плохое начало хорошего дня.

Ответить
0
Развернуть ветку
Читать все 1 комментарий
Удобство и забота: за что пользователи любят приложение BestDoctor

Приложение BestDoctor — единое окно для получения всех услуг нашей экосистемы медицинских сервисов: записи в клинику, проведения онлайн-консультаций с врачом, страхования путешествий и многих других. 80% пользователей BestDoctor регулярно обращаются к нему и оставляют хорошие отзывы. В этой статье мы расскажем, как медицинские сервисы BestDoctor…

Как снизить стоимость за установку приложения с помощью тестирования креатива: кейс Joom и Aitarget Tech

Может ли цвет креатива или расположение цены влиять на стоимость установки приложения? Эти и другие гипотезы платформа Aitarget Tech позволяет тестировать для Joom, одного из крупнейших маркетплейсов в Европе и Азии. Делимся результатами тестирования гипотез и рассказываем о подходе, который позволяет автоматизировать процесс.

«Альфа-Банк» выдает кредиты онлайн-мошенникам без должной идентификации клиентов и отказывается аннулировать договор
Концепт: как выглядели бы логотипы известных компаний в стиле Средневековья Статьи редакции

Burger King, Audi, Twitter, Starbucks и другие.

Эффект Кинопоиска. Какие исследования обязательно нужно сделать перед редизайном

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

Путин поддержал идеи Минфина о регулировании криптовалют и разрешении на майнинг в отдельных регионах — Bloomberg Статьи редакции

Майнинг могут разрешить в Иркутске, Карелии и Красноярске, сообщили источники издания.

«Эталон» требует доплаты в 300 тысяч после оформления ДДУ и выдачи ипотеки из-за ошибки в их калькуляторе

Добрый день!

Возникла неприятная ситуация с застройщиком «Эталон». В ноябре 2021 года было принято решение приобрести квартиру в жилом комплекте от застройщика «Эталон», обратилась напрямую в отдел продаж, без агентов и риэлторов. Процесс выбора несколько затянулся т.к. предложенные изначально варианты не устраивали по планировке или цене, к концу…
Я запарился перебирать онлайн-кинотеатры и создал агрегатор «Кино.Вино»

Есть такая проблема в век официальных/модных/современных (нужное подчеркнуть) онлайн-кинотеатров — их стало много, а выбрать где смотреть, да ещё и по более выгодной цене когда контент платный, занятие утомляющее.

Кейс: запустить шрифт для дислексиков и увеличить количество скачиваний приложения на 70%

Зачем — рассказывают дизайнер, логопед и агентство.

Почему отключение SWIFT не разрушит российскую банковскую систему, и чего действительно стоит бояться

С 2014 года мы слышим о возможном отключении России от системы SWIFT. В последние недели эти разговоры все чаще звучат в медиа и серьезно влияют на настроения в банковской среде. CBDO Банка 131 Анна Кузьмина рассказала, почему блокировка SWIFT в России не смертельный сценарий, и какие санкции против банков на самом деле вызывают беспокойство.

Инвестиции в IPO: научно доказанный способ зарабатывать 18% в день, или как акулы Уолл-стрит наживаются на Цукербергах

Профильные Телеграм-каналы и брокеры уверяют, что на вложениях в IPO легко получать по 100% годовых, и даже больше. В этой статье я разбираю, почему ученые-финансисты в чем-то склонны с ними согласиться — и кто на самом деле зарабатывает огромные деньги на IPO (спойлер: скорее всего, это будете не вы).

null