Как удалить повторяющиеся строки без уникального идентификатора

У меня есть повторяющиеся строки в моей таблице, и я хочу удалить дубликаты наиболее эффективным способом, так как таблица большая. После некоторых исследований у меня появился этот запрос:

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?

Ответ 1

Мне нравится решение @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]')

Ответ 2

Если у вас нет другого уникального идентификатора, вы можете использовать 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, как это, является одной из важных причин.

Ответ 3

В идеальном мире каждая таблица имеет уникальный идентификатор.
При отсутствии какого-либо уникального столбца (или их комбинации) используйте столбец 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 и может быть изменена между основными версиями без предупреждения (даже если это очень маловероятно). Его значения могут меняться между командами из-за фоновых процессов или одновременных операций записи (но не внутри одной команды).

Связанный:

Помимо:

Целью оператора DELETE не может быть CTE, только базовая таблица. Это побочный эффект от SQL Server - как и весь ваш подход.

Ответ 4

Вот что я придумал, используя group by

DELETE FROM mytable
WHERE id NOT in (
  SELECT MIN(id) 
  FROM mytable
  GROUP BY name, address, zipcode
)

Он удаляет дубликаты, сохраняя самую старую запись с дубликатами.

Ответ 5

Мы можем использовать оконную функцию для очень эффективного удаления повторяющихся строк:

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));

Ответ 6

Действительный синтаксис указан в 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;

Ответ 7

Если вы хотите сохранить одну строку из повторяющихся строк в таблице.

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"

Ответ 8

Из документации удалить повторяющиеся строки

Частым вопросом в 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);

Иногда вместо поля идентификатора используется поле метки времени.

Ответ 9

Если вам нужен уникальный идентификатор для каждой строки, вы можете просто добавить один (последовательный или направляющий) и обработать его как суррогатный ключ.

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;