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

Question

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

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 статья?

Answer 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]')

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

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

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

Связанные с:

• Как мне (или я могу) ВЫБРАТЬ DISTINCT по нескольким столбцам?

• Как использовать физическое расположение строк (ROWID) в операторе DELETE

В сторону:

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

Answer 4

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

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

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

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

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

Answer 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

Answer 8

Чтобы удалить дубликаты (оставить только одну запись) из «вкладки» таблицы, где данные выглядят следующим образом:

Вы можете сделать это:

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

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

Answer 10

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

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

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

Answer 11

Я дам простое решение.1-я копия одной строки (Чтобы скопировать эту строку, нажмите на сгенерированный sql столбец). Теперь удалите все дублирующиеся строки. Теперь откройте параметр редактирования 200-строк SQL-сервера, а затем вставьте строку, скопированную ранее.

Answer 12

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

Вы можете использовать этот запрос ниже:

Удалить из таблицы1 t1, где t1.rowid> (выберите min (t2.rowid) из таблицы1 t2, где t1.column = t2. Column)