Способы удаления дубликатов в 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, которые могут быть использованы в работе в зависимости от задачи и объема данных.
При больших объемах дубликатов в данных целесообразно рассмотреть возможность сохранения уникальных значений в промежуточную таблицу, очистку рабочей таблицы, и возврат оставленных уникальных записей.
Удаление повторяющихся строк в таблице запросом 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 которых больше чем выбранный во временную таблицу.
Стоит еще отметить, что описанную операцию можно выполнить при наличии в таблице ключевого поля. Если вдруг встретилась таблица без уникального идентификатора, то просто добавляем его:
Выполнив такой запрос, получим дополнительный столбец, заполненный уникальными числовыми значениями для каждой строки таблицы.
Выполняем все необходимые действия. После того, как операция по очистке таблицы от дубликатов записей выполнена, это поле можно так же удалить.
Удаление дубликатов строк SQL
AleDv: SELECT группирует все строки таблицы по полям, так что для каждой группы одинаковых строк будет выдан id только какой-то одной из этих строк.
LEFT JOIN дописывает id из выборки к строкам таблицы, помечая таким образом уникальные строки
DELETE удаляет строки с id, не попавшими в выборку, то есть все непомеченные дубликаты строк.
Пример:
Окончательно после удаления:
Могли удалиться не 3 и 5, а например 1 и 5 или 1 и 4 строки, зависит от результата выполнения GROUP BY.
Способы удаления дубликатов в 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, которые могут быть использованы в работе в зависимости от задачи и объема данных.
При больших объемах дубликатов в данных целесообразно рассмотреть возможность сохранения уникальных значений в промежуточную таблицу, очистку рабочей таблицы, и возврат оставленных уникальных записей.