Как удалить дубликаты записей?

Я должен добавить уникальное ограничение к существующей таблице. Это нормально, за исключением того, что в таблице уже есть миллионы строк, и многие из строк нарушают уникальное ограничение, которое мне нужно добавить.



каков самый быстрый подход к удалению оскорбительных строк? У меня есть SQL-оператор, который находит дубликаты и удаляет их, но для запуска требуется вечность. Есть ли другой способ решить эту проблему? Возможно, резервное копирование таблицы, а затем восстановление после ограничения добавил?

692   16  

16 ответов:

, например:

CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;
99  
2013-04-10 16:25:15

некоторые из этих подходов кажутся немного сложными, и я обычно делаю это так:

таблицы table, хотите унифицировать его (field1, field2), сохраняя строку с максимальным field3:

DELETE FROM table USING table alias 
  WHERE table.field1 = alias.field1 AND table.field2 = alias.field2 AND
    table.max_field < alias.max_field

например, у меня есть таблица, user_accounts и я хочу добавить ограничение Unique на электронную почту, но у меня есть некоторые дубликаты. Скажите также, что я хочу сохранить самый последний созданный (max id среди дубликатов).

DELETE FROM user_accounts USING user_accounts ua2
  WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;
  • Примечание - USING не стандартный SQL, это расширение PostgreSQL (но очень полезное), но в исходном вопросе конкретно упоминается PostgreSQL.
171  
2016-01-25 20:48:30

вместо создания новой таблицы, вы также можете повторно вставить уникальные строки в ту же таблицу после ее усечения. Сделай все это в одной сделке. При необходимости вы можете автоматически удалить временную таблицу в конце транзакции с помощью ON COMMIT DROP. Увидеть ниже.

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

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

SELECT pg_size_pretty(pg_relation_size('tbl'));

Set temp_buffers соответственно. Округлите щедро, потому что представление в памяти требует немного больше оперативной памяти.

SET temp_buffers = 200MB;    -- example value

BEGIN;

-- CREATE TEMPORARY TABLE t_tmp ON COMMIT DROP AS -- drop temp table at commit
CREATE TEMPORARY TABLE t_tmp AS  -- retain temp table after commit
SELECT DISTINCT * FROM tbl;  -- DISTINCT folds duplicates

TRUNCATE tbl;

INSERT INTO tbl
SELECT * FROM t_tmp;
-- ORDER BY id; -- optionally "cluster" data while being at it.

COMMIT;

этот метод может быть лучше, чем создание новой таблицы если в зависимости от объектов существует. Представления, индексы, внешние ключи или другие объекты, ссылающиеся на таблицу. TRUNCATE заставляет вас начать с чистого листа в любом случае (новый файл в фоновом режиме) и является много быстрее DELETE FROM tbl С большими столами (DELETE на самом деле может быть быстрее, с маленькими столами).

для больших таблиц, это регулярно быстрее чтобы удалить индексы и внешние ключи, заполните таблицу и воссоздайте эти объекты. Насколько ограничения fk являются конечно, вы должны быть уверены, что новые данные действительны, или вы столкнетесь с исключением при попытке создать fk.

отметим, что TRUNCATE требует более агрессивной замок, чем DELETE. Это может быть проблемой для таблиц с большой, параллельной нагрузкой.

если TRUNCATE это не вариант или вообще для малые и средние таблицы существует аналогичная техника с изменение данных CTE (Postgres 9.1+):

WITH del AS (DELETE FROM tbl RETURNING *)
INSERT INTO tbl
SELECT DISTINCT * FROM del;
-- ORDER BY id; -- optionally "cluster" data while being at it.

медленнее для больших столов, потому что TRUNCATE быстрее всего. Но может быть быстрее (и проще!) для небольших столов.

если у вас вообще нет зависимых объектов, вы можете создать новую таблицу и удалить старую, но вы вряд ли что-то выиграете от этого универсального подхода.

для очень больших таблиц, которые не вписывались бы оперативной памяти создание новая таблица будет значительно быстрее. Вам придется взвесить это против возможных проблем / накладных расходов с зависимыми объектами.

25  
2015-04-21 18:17:31

вы можете использовать oid или ctid, который обычно является" невидимыми " столбцами в таблице:

DELETE FROM table
 WHERE ctid NOT IN
  (SELECT MAX(s.ctid)
    FROM table s
    GROUP BY s.column_has_be_distinct);
20  
2011-05-12 14:05:16

функция окна PostgreSQL удобна для этой проблемы.

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

посмотреть удаление дубликатов.

19  
2016-01-25 20:42:47

обобщенный запрос на удаление дубликатов:

DELETE FROM table_name
WHERE ctid NOT IN (
  SELECT max(ctid) FROM table_name
  GROUP BY column1, [column 2, ...]
);

столбец ctid специальная столбцов для каждой таблицы, но не видно, если специально не упоминается. Элемент ctid значением столбца является уникальным для каждой строки в таблице.

8  
2016-04-12 02:01:54

С старый postgresql.org список рассылки:

create table test ( a text, b text );

уникальные значения

insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );

повторяющиеся значения

insert into test values ( 'x', 'y');
insert into test values ( 'x', 'x');
insert into test values ( 'y', 'y' );
insert into test values ( 'y', 'x' );

еще один двойной дубликат

insert into test values ( 'x', 'y');

select oid, a, b from test;

выберите повторяющиеся строки

select o.oid, o.a, o.b from test o
    where exists ( select 'x'
                   from test i
                   where     i.a = o.a
                         and i.b = o.b
                         and i.oid < o.oid
                 );

удалить дубликаты строк

Примечание: PostgreSQL dosn не поддерживает псевдонимы таблица, упомянутая в from статья об исключении.

delete from test
    where exists ( select 'x'
                   from test i
                   where     i.a = test.a
                         and i.b = test.b
                         and i.oid < test.oid
             );
7  
2016-01-25 20:40:35

Я просто использовать ответ Эрвина Брандштеттера успешно удалить дубликаты в соединительной таблице (Таблица, не имеющая собственных первичных идентификаторов), но обнаружила, что есть одна важная оговорка.

в том числе ON COMMIT DROP означает, что временная таблица будет удалена в конце транзакции. Для меня это означало, что временная таблица была больше нет в наличии к тому времени, как я пошел, чтобы вставить его!

Я только что сделал CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl; и все работало штраф.

временная таблица действительно удаляется в конце сеанса.

4  
2017-05-23 13:31:12

эта функция удаляет дубликаты без удаления индексов и делает это для любой таблицы.

использование: select remove_duplicates('mytable');

---
--- remove_duplicates(tablename) removes duplicate records from a table (convert from set to unique set)
---
CREATE OR REPLACE FUNCTION remove_duplicates(text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR ;
BEGIN
  EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT * FROM ' || tablename || ');';
  EXECUTE 'DELETE FROM ' || tablename || ';';
  EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
  EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
  RETURN;
END;
$$ LANGUAGE plpgsql;
3  
2010-01-28 01:06:17 
DELETE FROM table
  WHERE something NOT IN
    (SELECT     MAX(s.something)
      FROM      table As s
      GROUP BY  s.this_thing, s.that_thing);
3  
2012-01-11 17:54:52

Если у вас есть только один или несколько дублированных записей, и они действительно дублированный (то есть, они появляются дважды), вы можете использовать "скрытые"

3  
2016-01-25 20:44:15

во-первых, вам нужно решить, какой из ваших "дубликатов" вы будете держать. Если все столбцы равны, хорошо, вы можете удалить любой из них... Но, возможно, вы хотите сохранить только самый последний или какой-то другой критерий?

самый быстрый способ зависит от вашего ответа на вопрос выше, а также на % дубликатов на столе. Если вы выбрасываете 50% ваших строк, вам лучше делать CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ;, и если вы удалите 1% строк, использование DELETE лучше.

также для операции обслуживания, как это, как правило, хорошо установить work_mem на хороший кусок оперативной памяти: запускать объяснить, проверить N-количество видов/хэши, и набор сортировки в оперативной памяти / 2 / Н. используют много оперативной памяти, хорошая скорость. Пока у вас есть только одно одновременное подключение...

3  
2016-01-25 20:47:07

Я работаю с PostgreSQL 8.4. Когда я запустил предложенный код, я обнаружил, что это не так фактически удаление дубликатов. В выполнении некоторых тестов, я обнаружил, что добавление "DISTINCT ON (duplicate_column_name) "и" ORDER BY duplicate_column_name " сделали свое дело. Я не гуру SQL, я нашел это в PostgreSQL 8.4 SELECT...Отличный док.

CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR ;
  duplicate_column ALIAS FOR ;
BEGIN
  EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT ON (' || duplicate_column || ') * FROM ' || tablename || ' ORDER BY ' || duplicate_column || ' ASC);';
  EXECUTE 'DELETE FROM ' || tablename || ';';
  EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
  EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
  RETURN;
END;
$$ LANGUAGE plpgsql;
1  
2010-02-17 03:58:58

это работает очень хорошо и очень быстро:

CREATE INDEX otherTable_idx ON otherTable( colName );
CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable;
1  
2016-01-25 20:43:41 
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);

удалить дубликаты по столбцам и сохранить строку с самым низким идентификатором. Шаблон берется из postgres wiki

использование обобщенных табличных выражений можно добиться более читабельная версия выше через этот

WITH duplicate_ids as (
    SELECT id, rnum 
    FROM num_of_rows
    WHERE rnum > 1
),
num_of_rows as (
    SELECT id, 
        ROW_NUMBER() over (partition BY column1, 
                                        column2, 
                                        column3 ORDER BY id) AS rnum
        FROM tablename
)
DELETE FROM tablename 
WHERE id IN (SELECT id from duplicate_ids)
1  
2017-09-06 11:49:23 
CREATE TABLE test (col text);
INSERT INTO test VALUES
 ('1'),
 ('2'), ('2'),
 ('3'),
 ('4'), ('4'),
 ('5'),
 ('6'), ('6');
DELETE FROM test
 WHERE ctid in (
   SELECT t.ctid FROM (
     SELECT row_number() over (
               partition BY col
               ORDER BY col
               ) AS rnum,
            ctid FROM test
       ORDER BY col
     ) t
    WHERE t.rnum >1);
1  
2018-03-13 11:35:33

Comments

    Ничего не найдено.
Click here to cancel reply.