Как удалить повторяющиеся строки без уникального идентификатора
У меня есть дубликаты строк в моей таблице, и я хочу удалить дубликаты наиболее эффективным способом, так как таблица большая. После некоторого исследования я пришел с этим запросом:
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
статья?
12 ответов
Мне нравится решение @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]')
Если у вас нет другого уникального идентификатора, вы можете использовать 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
);
Хорошей идеей будет иметь уникальный автоматически увеличивающийся идентификатор в каждой таблице. Делать delete
как это одна из важных причин, почему.
В идеальном мире каждая таблица имеет уникальный идентификатор.
При отсутствии какого-либо уникального столбца (или их комбинации) используйте 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 и может быть изменена между основными версиями без предупреждения (даже если это очень маловероятно). Его значения могут меняться между командами из-за фоновых процессов или одновременных операций записи (но не внутри одной и той же команды).
Связанные с:
Как мне (или я могу) ВЫБРАТЬ DISTINCT по нескольким столбцам?
Как использовать физическое расположение строк (ROWID) в операторе DELETE
В сторону:
Цель DELETE
оператор не может быть CTE, только базовая таблица. Это побочный эффект от SQL Server - как и весь ваш подход.
Вот то, что я придумал, используя group by
DELETE FROM mytable
WHERE id NOT in (
SELECT MIN(id)
FROM mytable
GROUP BY name, address, zipcode
)
Он удаляет дубликаты, сохраняя самую старую запись с дубликатами.
Мы можем использовать оконную функцию для очень эффективного удаления дублирующихся строк:
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));
Действительный синтаксис указан по адресу 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;
Если вы хотите сохранить одну строку из повторяющихся строк в таблице.
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
Чтобы удалить дубликаты (оставить только одну запись) из «вкладки» таблицы, где данные выглядят следующим образом:
Вы можете сделать это:
DELETE FROM tab WHERE ctid IN
(SELECT ctid FROM
(SELECT ctid, fk_id_1, fk_id_2, row_number() OVER (PARTITION BY fk_id_1, fk_id_2 ORDER BY fk_id_1) AS rnum FROM tab) t
WHERE t.rnum > 1);
Где ctid — это физическое расположение строки в ее таблице (следовательно, идентификатор строки), а row_number — оконная функция, которая присваивает последовательное целое число каждой строке в результирующем наборе.
PARTITION группирует набор результатов, и последовательное целое число перезапускается для каждой группы.
Если вам нужен уникальный идентификатор для каждой строки, вы можете просто добавить его (серийный номер или идентификатор) и рассматривать его как суррогатный ключ.
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;
Из документации удалите дублирующиеся строки
В IRC часто задают вопрос, как удалять строки, которые являются дубликатами, по набору столбцов, сохраняя только одну строку с самым низким идентификатором. Этот запрос делает это для всех строк таблицы, имеющих одинаковые column1, 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);
Иногда поле метки времени используется вместо поля идентификатора.
Я дам простое решение.1-я копия одной строки (Чтобы скопировать эту строку, нажмите на сгенерированный sql столбец). Теперь удалите все дублирующиеся строки. Теперь откройте параметр редактирования 200-строк SQL-сервера, а затем вставьте строку, скопированную ранее.
Для небольших таблиц мы можем использовать псевдостолбец rowid для удаления повторяющихся строк.
Вы можете использовать этот запрос ниже:
Удалить из таблицы1 t1, где t1.rowid> (выберите min (t2.rowid) из таблицы1 t2, где t1.column = t2. Column)