Вопрос:
У меня есть повторяющиеся строки в моей таблице, и я хочу удалить дубликаты наиболее эффективным способом, так как таблица большая. После некоторых исследований у меня появился этот запрос:
WITH TempEmp AS ( SELECT name, ROW_NUMBER() OVER(PARTITION by name, address, zipcode ORDER BY name) AS duplicateRecCount FROM mytable ) — Now Delete Duplicate Records DELETE FROM TempEmp WHERE duplicateRecCount > 1;
Но он работает только в SQL, а не в Netezza. Казалось бы, это не нравится DELETE после предложения WITH?
Лучший ответ:
Мне нравится решение @erwin-brandstetter, но я хотел показать решение с ключевым словом USING:
DELETE FROM table_with_dups T1 USING table_with_dups T2 WHERE T1.ctid < T2.ctid — delete the «older» ones AND T1.name = T2.name — list columns that define duplicates AND T1.address = T2.address AND T1.zipcode = T2.zipcode;
Если вы хотите просмотреть записи перед их удалением, просто замените DELETE на SELECT * и USING запятой ,, т.е.
SELECT * FROM table_with_dups T1 , table_with_dups T2 WHERE T1.ctid < T2.ctid — select the «older» ones AND T1.name = T2.name — list columns that define duplicates AND T1.address = T2.address AND T1.zipcode = T2.zipcode;
Обновление: здесь я проверил несколько различных решений на скорость. Если вы не ожидаете много дубликатов, то это решение работает намного лучше, чем те, у которых есть предложение NOT IN (…), поскольку они генерируют много строк в подзапросе.
Если переписать запрос для использования IN (…), он будет работать аналогично решению, представленному здесь, но код SQL станет гораздо менее лаконичным.
Обновление 2: если у вас есть значения NULL в одном из ключевых столбцов (что вам действительно не следует использовать в IMO), тогда вы можете использовать COALESCE() в условии для этого столбца, например
AND COALESCE(T1.col_with_nulls, ‘[NULL]’) = COALESCE(T2.col_with_nulls, ‘[NULL]’) Ответ №1
Если у вас нет другого уникального идентификатора, вы можете использовать ctid:
delete from mytable where exists (select 1 from mytable t2 where t2.name = mytable.name and t2.address = mytable.address and t2.zip = mytable.zip and t2.ctid > mytable.ctid );
Это хорошая идея иметь уникальный, автоматически увеличивающий id в каждой таблице. Выполнение delete, как это, является одной из важных причин.
Ответ №2
В идеальном мире каждая таблица имеет уникальный идентификатор.
При отсутствии какого-либо уникального столбца (или их комбинации) используйте столбец ctid:
DELETE FROM tbl WHERE ctid NOT IN ( SELECT min(ctid) — ctid is NOT NULL by definition FROM tbl GROUP BY name, address, zipcode); — list columns defining duplicates
Вышеприведенный запрос является коротким, удобно перечисляя имена столбцов только один раз. NOT IN (SELECT …) – сложный стиль запроса, когда могут использоваться значения NULL, но системный столбец ctid никогда не имеет значения NULL. См:
- Найти записи, в которых не существует объединения
Использование EXISTS как продемонстрировано @Gordon обычно быстрее. Так же как и само присоединение к предложению USING , как @isapir, добавленный позже. Оба должны привести к одному и тому же плану запросов.
Но обратите внимание на важное отличие: эти другие запросы обрабатывают значения NULL как , не равные, тогда как GROUP BY (или DISTINCT или DISTINCT ON ()) обрабатывает значения NULL как равные. Не имеет значения, определены ли ключевые столбцы NOT NULL. Иначе, в зависимости от вашего определения “дубликат”, вам понадобится тот или иной подход. Или используйте IS NOT DISTINCT FROM для сравнения значений (которые могут быть не в состоянии использовать некоторые индексы).
Отказ от ответственности:
ctid является внутренней деталью реализации Postgres, она не входит в стандарт SQL и может быть изменена между основными версиями без предупреждения (даже если это очень маловероятно). Его значения могут меняться между командами из-за фоновых процессов или одновременных операций записи (но не внутри одной команды).
Связанный:
-
Как мне (или я могу) ВЫБРАТЬ ОТЛИЧИЕ на нескольких столбцах?
-
Как использовать физическое расположение строк (ROWID) в инструкции DELETE
Помимо:
Целью оператора DELETE не может быть CTE, только базовая таблица. Это побочный эффект от SQL Server – как и весь ваш подход.
Ответ №3
Вот что я придумал, используя group by
DELETE FROM mytable WHERE id NOT in ( SELECT MIN(id) FROM mytable GROUP BY name, address, zipcode )
Он удаляет дубликаты, сохраняя самую старую запись с дубликатами.
Ответ №4
Мы можем использовать оконную функцию для очень эффективного удаления повторяющихся строк:
DELETE FROM tab WHERE id IN (SELECT id FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), id FROM tab) x WHERE x.row_number > 1);
Некоторая оптимизированная версия PostgreSQL (с ctid):
DELETE FROM tab WHERE ctid = ANY(ARRAY(SELECT ctid FROM (SELECT row_number() OVER (PARTITION BY column_with_duplicate_values), ctid FROM tab) x WHERE x.row_number > 1)); Ответ №5
Действительный синтаксис указан в http://www.postgresql.org/docs/current/static/sql-delete.html
Я бы изменил вашу таблицу, чтобы добавить уникальный автоматически увеличивающийся идентификатор первичного ключа, чтобы вы могли выполнить запрос, подобный следующему, который будет сохранять первый из каждого набора дубликатов (то есть тот, который имеет самый низкий идентификатор). Обратите внимание, что добавление ключа в Postgres немного сложнее, чем в некоторых других БД.
DELETE FROM mytable d USING ( SELECT min(id), name, address, zip FROM mytable GROUP BY name, address, zip HAVING COUNT() > 1 ) AS k WHERE d.id <> k.id AND d.name=k.name AND d.address=k.address AND d.zip=k.zip; Ответ №6
Если вы хотите сохранить одну строку из повторяющихся строк в таблице.
create table some_name_for_new_table as (select * from (select *,row_number() over (partition by pk_id) row_n from your_table_name_where_duplicates_are_present) a where row_n = 1);
Это создаст таблицу, которую вы можете скопировать.
Перед копированием таблицы удалите столбец “row_n”
Ответ №7
Из документации удалить повторяющиеся строки
Частым вопросом в IRC является удаление строк, которые дублируются по набору столбцов, сохраняя только один с самым низким идентификатором.
Этот запрос делает это для всех строк tablename, имеющих одинаковые столбцы1, column2 и column3.
DELETE FROM tablename WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum FROM tablename) t WHERE t.rnum > 1);
Иногда вместо поля идентификатора используется поле метки времени.
Ответ №8
Если вам нужен уникальный идентификатор для каждой строки, вы можете просто добавить один (последовательный или направляющий) и обработать его как суррогатный ключ.
CREATE TABLE thenames ( name text not null , address text not null , zipcode text not null ); INSERT INTO thenames(name,address,zipcode) VALUES (‘James’, ‘main street’, ‘123’ ) ,(‘James’, ‘main street’, ‘123’ ) ,(‘James’, ‘void street’, ‘456’) ,(‘Alice’, ‘union square’ , ‘123’) ; SELECT*FROM thenames; — add a surrogate key ALTER TABLE thenames ADD COLUMN seq serial NOT NULL PRIMARY KEY ; SELECT*FROM thenames; DELETE FROM thenames del WHERE EXISTS( SELECT*FROM thenames x WHERE x.name=del.name AND x.address=del.address AND x.zipcode=del.zipcode AND x.seq < del.seq ); — add the unique constrain,so that new dupplicates cannot be created in the future ALTER TABLE thenames ADD UNIQUE (name,address,zipcode) ; SELECT*FROM thenames;
