Способы удаления дубликатов в SQL Server
При проектировании объектов, в частности таблиц в БД SQL Server, необходимо придерживаться определенных правил. Однако, даже если следовать данным правилам существует вероятность появления дубликатов в строках таблиц. Данная статья посвящена различным способам очистки данных от дубликатов.
43K открытий
При проектировании объектов, в частности таблиц в БД 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, которые могут быть использованы в работе в зависимости от задачи и объема данных.
При больших объемах дубликатов в данных целесообразно рассмотреть возможность сохранения уникальных значений в промежуточную таблицу, очистку рабочей таблицы, и возврат оставленных уникальных записей.
Удаление или поиск дубликатов (повторяющихся) записей в таблице
Начнем с того, что важно определить, отличаются записи хоть одним полем или нет. Оператор DELETE и вообще SQL не позволяет из двух одинаковых записей удалить только одну – либо все дубликаты, либо ни одного.
Однако, используя специфический для IB номер записи, это можно сделать. Например:
DELETE FROM XXX T1 WHERE EXISTS
(SELECT * FROM XXX T2 WHERE
(T2.column1 = T1.column1 or (T2.column1 is null and T2.column1 is null)) AND
(T2.column2 = T1.column2 or (T2.column2 is null and T2.column2 is null)) AND
(. ) AND
( T2.RDB$DB_KEY > T1.RDB$DB_KEY ))
В этом случае используется RDB$DB_KEY – физический номер записи IB. Можно оставить как запись с самым большим DB_KEY, так и с самым меньшим (> или < в последнем условии WHERE).
Для поиска имеющих какой-либо одинаковый столбец записей обычно используется запрос, похожий на следующий:
SELECT * FROM TABLE T1
WHERE (SELECT COUNT(*)
FROM TABLE T2
WHERE T1.FIELD = T2.FIELD ) > 1
Однако этот запрос не совсем эффективен. Вместо него выгоднее использовать процедуру, которая будет выполняться намного быстрее:
(Ann Harrison)
for select field
from table
group by field
having count (field) > 1
into :fld
do
begin
for select field
from table
where field = :fld
into :fld1
do
begin
suspend;
end
end
Но хранимая процедура не всегда удобна. Также можно использовать уникальный идентификатор записи RDB$DB_KEY:
(Josef Marie M. Alba)
SELECT * FROM TABLE T1
WHERE EXISTS
(SELECT FIELD FROM TABLE T2
WHERE T1.FIELD = T2.FIELD AND
T1.RDB$DB_KEY != T2.RDB$DB_KEY )
Copyright iBase.ru © 2002-2023
Запрос SQL на удаление дубликатов из таблицы по одному полю
@YuraIvanov не лучше получить сразу значение и передать на удаление? копирование будет время занимать ` SELECT name, surname FROM table group by name, surname HAVING (count(CONCAT(name, surname)) > 1); `
25 июл 2016 в 11:35
@SeniorAutomator нельзя производить удаление в таблице, одновременно выбирая из нее значения. Особенности mysql. Эту особенность можно обойти вложенным запросом (как это сделали некоторые другие отвечающие), однако это будет эквивалентно созданию временной таблицы, т.е. то же самое что и в ответе.
25 июл 2016 в 12:38
Какие-то экзотические варианты предлагаются.
Удалить из таблицы дубликаты (строки с одинаковыми значениями поля col) с меньшим id
DELETE t1 FROM t t1 LEFT JOIN t t2 ON t1.col = t2.col AND t1.id < t2.id WHERE t2.id IS NOT NULL;
через подзапрос
DELETE t FROM t LEFT JOIN (SELECT max(id) as id, col FROM t GROUP BY col) t1 USING(id) WHERE t1.id IS NULL;
Кстати, в указанной статье описан и обход ограничения подзапросов на одновременную выборку и изменение данных.
Отслеживать
ответ дан 10 апр 2015 в 12:56
599 4 4 серебряных знака 5 5 бронзовых знаков
здесь предлагают делать так:
ALTER IGNORE TABLE foobar ADD UNIQUE (name, surname) но индекс должен влезть в память.
ну и через временную таблицу естественно есть способ. а так-же через group by.
Отслеживать
ответ дан 15 фев 2013 в 18:26
18.1k 20 20 серебряных знаков 42 42 бронзовых знака
Я бы предложил пересоздать полностью таблицу, установив нужные уникальные столбцы. И отправив в ignore те данные, которые будут дублироваться. Это будет гораздо быстрее, если у вас в таблице очень много данных.
Но если вы хотите сделать это запросом, то это будет медленнее, но тоже возможно. На всякий случай, сначала проверьте, что этот запрос выводит только дублирующие строки, а потом замените SELECT * на DELETE tablename
SELECT * FROM tablename INNER JOIN (SELECT Min(id) minid, name, surname FROM tablename GROUP BY name, surname HAVING Count(1) > 1) AS duplicatesTable ON ( duplicatesTable.name = tablename.name AND duplicatesTable.surname = tablename.surname AND duplicatesTable.minid <> tablename.id ) Удаление одинаковых строк в PostgreSQL
Если получилось, что в таблице отсутствует первичный ключ (primary key), скорее всего, среди записей есть дубликаты. Речь идёт о дублирующихся строках или случаях, когда дублируется одна либо более колонок. Посмотрим на таблицу с информацией о покупателях, где вторая по счёту строка полностью задублирована:
Чтобы удалить все дубликаты, нам подойдёт следующий запрос:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customers.*);По умолчанию, уникальное для каждой записи поле ctid скрыто, однако оно существует в каждой таблице.
Тут следует заметить, что запрос довольно требователен к ресурсам, что следует учитывать при его использовании на рабочем проекте — просто будьте аккуратны.
В следующей ситуации повторяются значения полей.
Если допускается удалять дубликаты, не сохраняя все данные, используем следующий запрос:
DELETE FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);Если же информация важна, сначала давайте найдём записи с дубликатами:
SELECT * FROM customers WHERE ctid NOT IN (SELECT max(ctid) FROM customers GROUP BY customer_id);
До удаления такой записи мы можем перенести её во временную таблицу либо заменить значение customer_id на другое.
Как бы там ни было, общая форма запроса на удаление вышеописанных записей будет выглядеть так:
DELETE FROM table_name WHERE ctid NOT IN (SELECT max(ctid) FROM table_name GROUP BY column1, [column 2,] );
