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

Способы удаления дубликатов в 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 Запрос — Удаление дублей — только одну (последнюю) запись из нескольких

Запрос «Удаление_Дублей» сложен и работает относительно медленно.
На 10 000 записей может работать минуту или две.

Вот вариант на VBA который в разы шустрее:

Private Sub RemoveDataDuplicates() ' Удаление дублей (только одну запись из нескольких дублирующих) '---------------------------------------------------------------- Dim s$, l& On Error GoTo RemoveDataDuplicates_Err 'Зачистка временной Таблицы (от старых ID подлежащих удалению) CurrentDb.Execute "DELETE FROM tmpRecordCodes" ' или . ' Код создания временной таблицы "tmpДубли" 'Заполняем временную таблицу: DoCmd.Hourglass True 'Показать часики s = "INSERT INTO tmpRecordCodes (Max_ID) SELECT Max(Код) AS Max_ID " & _ "FROM [Дубли] GROUP BY [Номер] HAVING Count([Номер])>1" CurrentDb.Execute s DoCmd.Hourglass False 'Вернуть нормальный курсор l = DCount("*", "tmpRecordCodes") If l = 0 Then MsgBox "Данных подлежащих удалению не обнаружено!", vbExclamation, "Нет данных!" GoTo RemoveDataDuplicates_End End If '---------------------------------------------------------------- 'Запрос поддтверждения удаления записи при ответе НЕТ - остановка If MsgBox("Действительно удалить:" & vbCrLf & Format(l, "#,###,##0") & _ " последних дубликатов записей из таблицы [Дубли] . ", vbYesNo + vbExclamation + vbDefaultButton1, _ "Удаление данных") = vbNo Then GoTo RemoveDataDuplicates_End 'Exit Sub DoCmd.Hourglass True 'Показать часики s = "DELETE [Дубли].* FROM [Дубли] INNER JOIN tmpRecordCodes ON [Дубли].Код = tmpRecordCodes.Max_ID" CurrentDb.Execute s DoCmd.Hourglass False 'Вернуть нормальный курсор MsgBox "Готово!", vbInformation, "OK!" RemoveDataDuplicates_End: On Error Resume Next DoCmd.Hourglass False 'Вернуть нормальный курсор 'CurrentDb.TableDefs.Delete "tmpRecordCodes" Err.Clear Exit Sub RemoveDataDuplicates_Err: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub RemoveDataDuplicates.", _ vbCritical, "Произошла ошибка!" Err.Clear Resume RemoveDataDuplicates_End End Sub

MSA-2007 ( 44 kB) Пример

Как удалить дубликаты и просуммировать ячейки?

Как видно, четыре поля (date_id, store_id, good_id и order) имеют одинаковое значение. Хотелось бы обновить таблицу, при этом просуммировав поле quantity у строк, которые имеют одинаковое значение по 4 вышеуказанным полям, т.е. получить что-то такое:

+----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+ | id | date_id | year | week | store_id | store_id_new | good_id | promo | quantity | order | cost | +----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+ | 1 | 2015-12-07 00:00:00.000 | NULL | NULL | 5 | NULL | 26 | 0 | 3 | 764536 | NULL | +----+-------------------------+------+------+----------+--------------+---------+-------+----------+--------+------+

• Вопрос задан более трёх лет назад
• 224 просмотра

Комментировать
Решения вопроса 0
Ответы на вопрос 3

Так вроде же
select id , date_id, year , week , store_id , store_id_new , good_id , promo , SUM(quantity) , order , cost
from table

group by
id , date_id, year , week , store_id , store_id_new , good_id , promo , order , cost

То есть, в поле GROUP BY добавляем все поля, для которых не нужны арифметические операции. В данном случае увидел только quantity

Ответ написан более трёх лет назад

Константин Цветков @tsklab Куратор тега SQL Server
id разные, суммирования не будет.
Готовые решения — не подаю, но.

Если это действительно надо, то проще всего в рамках транзакции вставить выборку во временную таблицу, удалить «дубликаты» из основной и вставить в основную результат агрегации из временной. Тут правда возникнет вопрос id (надеюсь он и есть pk ?)

Ответ написан более трёх лет назад
Никита Рубинковский @avatar22007 Автор вопроса
d-stream Да, и есть pk. Ваш способ был бы оптимальным, не могли бы пример привести?
Никита Рубинковский @avatar22007 Автор вопроса
Данные вставляю в показанную таблицу из другой (исходной) insert-select

Никита Рубинковский, если записей не очень много, то можно даже так:

begin transaction declare @temp_table table (. ) insert into @tem_table select * from table truncate table insert into table select … from @temp_table group by … commit transaction

естественно таковое имеет смысл если источник дублей устранен и нет необходимости это делать регулярно

И да, кстати, веселая жизнь будет гарантирована, если есть референсы на id (особенно не явные foreign key, а типа «просто ссылаемся на id» )

А что-то мне подсказывает, что где-то в поступлениях из разных партий фигурируют отсылки на id отпусков из этой таблицы

Никита Рубинковский @avatar22007 Автор вопроса
d-stream записей порядка 60 млн
Никита Рубинковский, стоп-стоп!
если речь о

Данные вставляю в показанную таблицу из другой (исходной) insert-select

а если таблица-получатель на момент вставки пустая — то вообще идеально:

примитивно агрегируем то что надо + добавляем row_number() из оконных — то есть получаем нечто типа

SELECT OVER(PARTITION BY recovery_model_desc ORDER BY date_id ASC) date_id, year_week=min(year_week), … quantity=sum(quantity) FROM src_table group by date_id

и собственно этот результат и инсертим в таблицу-получатель, имея гарантию уникального id и сгруппированные по date_id записи с суммированным количеством

з.ы. 80 миллионов — может оказаться веселым делом и возможно придется сделать табличку-фильтр с id исходных записей и в цикле:
— загнать туда порцию (top xx)
— insert select с inner join на фильтр
— delete из исходной с inner join фильтра
— вернуться к началу

Общие табличные выражения (CTE) стр. 3

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

Поступим следующим образом:

1. Удалим все строки из базовой таблицы, возвращая их в табличное выражение (первое CTE).
2. Используя результат 1 шага, формируем уникальные строки, которые должны остаться в таблице (второе CTE).
3. Вставляем строки, полученные на шаге 2 в базовую таблицу.

Воспользуемся таблицей из цитируемого примера, чтобы написать запрос: