Jak usunąć zduplikowane wpisy?


92

Muszę dodać unikalne ograniczenie do istniejącej tabeli. Jest to w porządku, z wyjątkiem tego, że tabela ma już miliony wierszy, a wiele z nich narusza unikalne ograniczenie, które muszę dodać.

Jaka jest najszybsza metoda usuwania nieprawidłowych wierszy? Mam instrukcję SQL, która znajduje duplikaty i usuwa je, ale jej uruchomienie trwa wieczność. Czy jest inny sposób rozwiązania tego problemu? Może utworzyć kopię zapasową tabeli, a następnie przywrócić ją po dodaniu ograniczenia?

Odpowiedzi:


101

Na przykład możesz:

CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

2
Czy możesz wyróżnić grupę kolumn. Może „SELECT DISTINCT (ta, tb, tc), * FROM t”?
gjrwebber


36
łatwiej wpisać: CREATE TABLE tmp AS SELECT ...;. Wtedy nie musisz nawet zastanawiać się, jaki jest układ tmp. :)
Randal Schwartz

9
Ta odpowiedź w rzeczywistości nie jest zbyt dobra z kilku powodów. @Randal o nazwie jeden. W większości przypadków, zwłaszcza jeśli masz zależne obiekty, takie jak indeksy, ograniczenia, widoki itp., Lepszym podejściem jest użycie rzeczywistej TABELI TYMCZASOWEJ , TRUNCATE oryginał i ponowne wstawienie danych.
Erwin Brandstetter

7
Masz rację co do indeksów. Upuszczanie i odtwarzanie jest znacznie szybsze. Ale inne zależne obiekty mogą zepsuć lub całkowicie uniemożliwić upuszczenie tabeli - o czym OP dowiedziałby się po wykonaniu kopii - to tyle, jeśli chodzi o „najszybsze podejście”. Mimo to masz rację, jeśli chodzi o głosowanie przeciw. Jest to nieuzasadnione, ponieważ nie jest to zła odpowiedź. Po prostu nie jest tak dobrze. Mogłeś dodać kilka wskazówek na temat indeksów lub zależnych obiektów lub odsyłacz do instrukcji, tak jak to zrobiłeś w komentarzu lub jakimkolwiek innym wyjaśnieniu. Chyba byłem sfrustrowany tym, jak ludzie głosują. Usunięto głos przeciw.
Erwin Brandstetter

173

Niektóre z tych podejść wydają się nieco skomplikowane i generalnie robię to jako:

Podana tabela table, chcesz ją unikatową na (field1, field2) zachowując wiersz z max field3:

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

Na przykład mam tabelę user_accountsi chcę dodać unikalne ograniczenie dotyczące poczty elektronicznej, ale mam kilka duplikatów. Powiedz również, że chcę zachować ostatnio utworzony (maksymalny identyfikator wśród duplikatów).

DELETE FROM user_accounts USING user_accounts ua2
  WHERE user_accounts.email = ua2.email AND user_account.id < ua2.id;
  • Uwaga - USINGnie jest to standardowy SQL, jest to rozszerzenie PostgreSQL (ale bardzo przydatne), ale w oryginalnym pytaniu jest mowa o PostgreSQL.

4
To drugie podejście jest bardzo szybkie na postgresach! Dzięki.
Eric Bowman - abstracto -

5
@Tim, czy możesz lepiej wyjaśnić, co robi USINGw postgresql?
Fopa Léon Constantin

3
To zdecydowanie najlepsza odpowiedź. Nawet jeśli w tabeli nie masz kolumny szeregowej, której można by użyć do porównania identyfikatora, warto tymczasowo dodać ją, aby skorzystać z tego prostego podejścia.
Shane

2
Właśnie sprawdziłem. Odpowiedź brzmi: tak, będzie. Użycie funkcji less-than (<) pozostawia tylko maksymalny identyfikator, podczas gdy większe-niż (>) pozostawia tylko minimalny identyfikator, usuwając resztę.
André C. Andersen

1
@Shane można użyć: WHERE table1.ctid<table2.ctid- nie ma potrzeby dodawania kolumny szeregowej
alexkovelsky

25

Zamiast tworzyć nową tabelę, możesz również ponownie wstawić unikalne wiersze do tej samej tabeli po jej obcięciu. Zrób to wszystko w jednej transakcji . Opcjonalnie możesz automatycznie usunąć tabelę tymczasową na końcu transakcji za pomocą ON COMMIT DROP. Zobacz poniżej.

To podejście jest przydatne tylko wtedy, gdy istnieje wiele wierszy do usunięcia z całej tabeli. W przypadku kilku duplikatów użyj zwykłego DELETE.

Wspomniałeś o milionach wierszy. Aby wykonać operację FAST chcesz przeznaczyć wystarczających buforów tymczasowych dla sesji. To ustawienie należy zmienić przed użyciem jakiegokolwiek bufora tymczasowego w bieżącej sesji. Sprawdź rozmiar swojego stołu:

SELECT pg_size_pretty(pg_relation_size('tbl'));

Ustaw temp_buffersodpowiednio. Zaokrąglij hojnie, ponieważ reprezentacja w pamięci wymaga nieco więcej pamięci RAM.

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;

Ta metoda może być lepsza niż tworzenie nowej tabeli, jeśli istnieją zależne obiekty. Widoki, indeksy, klucze obce lub inne obiekty odwołujące się do tabeli. TRUNCATEsprawia, że ​​i tak zaczynasz z czystym kontem (nowy plik w tle) i jest znacznie szybszy niż DELETE FROM tblprzy dużych stołach (w DELETErzeczywistości może być szybszy przy małych stołach).

W przypadku dużych tabel regularnie szybciej usuwa się indeksy i klucze obce, uzupełnia tabelę i ponownie tworzy te obiekty. Jeśli chodzi o ograniczenia fk, musisz oczywiście mieć pewność, że nowe dane są prawidłowe, w przeciwnym razie napotkasz wyjątek podczas próby utworzenia fk.

Należy pamiętać, że TRUNCATEwymaga bardziej agresywnego blokowania niż DELETE. Może to być problem w przypadku tabel z dużym, równoczesnym obciążeniem.

Jeśli TRUNCATEnie jest to opcja lub ogólnie w przypadku małych i średnich tabel, istnieje podobna technika z modyfikacją danych 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.

Wolniej przy dużych stołach, bo TRUNCATEtam jest szybciej. Ale może być szybszy (i prostszy!) Dla małych stołów.

Jeśli nie masz żadnych obiektów zależnych, możesz utworzyć nową tabelę i usunąć starą, ale prawie nic nie zyskujesz dzięki temu uniwersalnemu podejściu.

W przypadku bardzo dużych tabel, które nie mieszczą się w dostępnej pamięci RAM , tworzenie nowej tabeli będzie znacznie szybsze. Będziesz musiał rozważyć to z możliwymi problemami / kosztami związanymi z zależnymi obiektami.


2
Ja też zastosowałem to podejście. Jednak może to być sprawa osobista, ale moja tabela tymczasowa została usunięta i niedostępna po obcięciu ... Uważaj, aby wykonać te kroki, jeśli tabela tymczasowa została pomyślnie utworzona i jest dostępna.
xlash

@xlash: Możesz sprawdzić istnienie, aby się upewnić, i albo użyć innej nazwy dla tabeli tymczasowej, albo ponownie użyć istniejącej. Dodałem trochę do mojej odpowiedzi.
Erwin Brandstetter

OSTRZEŻENIE: Uważaj, +1 do @xlash - muszę ponownie zaimportować moje dane, ponieważ później tabela tymczasowa nie istniała TRUNCATE. Jak powiedział Erwin, przed skróceniem tabeli upewnij się, że istnieje. Zobacz odpowiedź @ codebykat
Jordan Arseno

1
@JordanArseno: Przerzuciłem się na wersję bez ON COMMIT DROP, aby osoby, którym brakuje fragmentu, w którym napisałem „w jednej transakcji”, nie tracą danych. I dodałem BEGIN / COMMIT, aby wyjaśnić „jedną transakcję”.
Erwin Brandstetter

1
rozwiązanie z USING zajęło ponad 3 godziny na stole z 14 milionami rekordów. To rozwiązanie z temp_buffers zajęło 13 minut. Dzięki.
castt

20

Możesz użyć oid lub ctid, które zwykle są „niewidocznymi” kolumnami w tabeli:

DELETE FROM table
 WHERE ctid NOT IN
  (SELECT MAX(s.ctid)
    FROM table s
    GROUP BY s.column_has_be_distinct);

4
Aby usunąć na miejscu , NOT EXISTSpowinno być znacznie szybsze : DELETE FROM tbl t WHERE EXISTS (SELECT 1 FROM tbl t1 WHERE t1.dist_col = t.dist_col AND t1.ctid > t.ctid)- lub użyj dowolnej innej kolumny lub zestawu kolumn do sortowania, aby wybrać ocalałego.
Erwin Brandstetter

@ErwinBrandstetter, czy zapytanie, które podajesz, powinno być używane NOT EXISTS?
John

1
@John: To musi być EXISTStutaj. Przeczytaj to w ten sposób: „Usuń wszystkie wiersze, w których istnieje inny wiersz o tej samej wartości w, dist_colale większym ctid”. Jedynym ocalałym z każdej grupy oszustów będzie ten z największym ctid.
Erwin Brandstetter

Najłatwiejsze rozwiązanie, jeśli masz tylko kilka zduplikowanych wierszy. Może być używany z, LIMITjeśli znasz liczbę duplikatów.
Skippy le Grand Gourou

19

W przypadku tego problemu przydatna jest funkcja okna 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);

Zobacz Usuwanie duplikatów .


Używając „ctid” zamiast „id”, działa to w przypadku w pełni zduplikowanych wierszy.
bradw2k

Świetne rozwiązanie. Musiałem to zrobić dla tabeli z miliardem rekordów. Dodałem WHERE do wewnętrznego SELECT, aby zrobić to w kawałkach.
Jan

8

Uogólnione zapytanie do usuwania duplikatów:

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

Kolumna ctidjest specjalną kolumną dostępną dla każdej tabeli, ale niewidoczną, o ile nie zaznaczono inaczej. Wartość ctidkolumny jest uważana za unikalną dla każdego wiersza w tabeli. Zobacz kolumny systemowe PostgreSQL, aby dowiedzieć się więcej ctid.


1
jedyna uniwersalna odpowiedź! Działa bez JOIN samodzielnego / kartezjańskiego. Warto jednak dodać, że konieczne jest poprawne określenie GROUP BYklauzuli - powinny to być „kryteria unikalności”, które są obecnie naruszane lub jeśli chcesz, aby klucz wykrywał duplikaty. Jeśli podano źle, nie będzie działać poprawnie
msciwoj

7

Ze starej listy mailingowej postgresql.org :

create table test ( a text, b text );

Unikalne wartości

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

Zduplikowane wartości

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

Jeszcze jeden podwójny duplikat

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

select oid, a, b from test;

Wybierz zduplikowane wiersze

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

Usuń zduplikowane wiersze

Uwaga: PostgreSQL nie obsługuje aliasów w tabeli wymienionej w fromklauzuli usuwania.

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

Twoje wyjaśnienie jest bardzo sprytne, ale brakuje ci jednego punktu, w tabeli tworzenia określ oid, a następnie
uzyskaj

@Kalanidhi Dziękuję za uwagi dotyczące poprawy odpowiedzi, wezmę pod uwagę ten punkt.
Bhavik Ambani

To naprawdę pochodzi z postgresql.org/message-id/…
Martin F

Możesz użyć kolumny systemowej „ctid”, jeśli „oid” zwraca błąd.
sul4bh

4

Właśnie użyłem odpowiedzi Erwina Brandstettera z powodzeniem, aby usunąć duplikaty w tabeli łączenia (tabela bez własnych podstawowych identyfikatorów), ale stwierdziłem, że jest jedno ważne zastrzeżenie.

W tym ON COMMIT DROPoznacza, że ​​tymczasowa tabela zostanie usunięta po zakończeniu transakcji. Dla mnie oznaczało to, że tymczasowy stół nie był już dostępny , zanim poszedłem go wstawić!

Właśnie zrobiłem CREATE TEMPORARY TABLE t_tmp AS SELECT DISTINCT * FROM tbl;i wszystko działało dobrze.

Tabela tymczasowa zostaje usunięta pod koniec sesji.


3

Ta funkcja usuwa duplikaty bez usuwania indeksów i robi to w dowolnej tabeli.

Stosowanie: select remove_duplicates('mytable');

---
--- remove_duplicates (tablename) usuwa zduplikowane rekordy z tabeli (konwertuje ze zbioru na unikalny zestaw)
---
UTWÓRZ LUB ZAMIEŃ FUNKCJĘ remove_duplicates (tekst) RETURNS void AS $$
OGŁOSIĆ
  tablename ALIAS FOR 1 $;
ZACZYNAĆ
  WYKONAJ „UTWÓRZ TYMCZASOWĄ TABELĘ _DISTINCT_” || nazwa tabeli || 'AS (SELECT DISTINCT * FROM' || nazwa tabeli || ');';
  WYKONAJ „USUŃ Z” || nazwa tabeli || ';';
  WYKONAJ „WSTAW DO” || nazwa tabeli || '(SELECT * FROM _DISTINCT_' || nazwa tabeli || ');';
  WYKONAJ „DROP TABLE _DISTINCT_” || nazwa tabeli || ';';
  POWRÓT;
KONIEC;
$$ JĘZYK plpgsql;

3
DELETE FROM table
  WHERE something NOT IN
    (SELECT     MAX(s.something)
      FROM      table As s
      GROUP BY  s.this_thing, s.that_thing);

To jest to, co obecnie robię, ale to trwa bardzo długo.
gjrwebber

1
Czy to się nie powiedzie, jeśli wiele wierszy w tabeli ma taką samą wartość w kolumnie coś?
shreedhar

3

Jeśli masz tylko jeden lub kilka zduplikowanych wpisów i rzeczywiście są one zduplikowane (to znaczy pojawiają się dwukrotnie), możesz użyć ctidkolumny „ukryte” , jak zaproponowano powyżej, razem z LIMIT:

DELETE FROM mytable WHERE ctid=(SELECT ctid FROM mytable WHERE […] LIMIT 1);

Spowoduje to usunięcie tylko pierwszego z wybranych wierszy.


Wiem, że nie rozwiązuje to problemu OP, który ma wiele zduplikowanych w milionach wierszy, ale i tak może być pomocny.
Skippy le Grand Gourou

Musiałoby to zostać uruchomione raz dla każdego zduplikowanego wiersza. Odpowiedź shekwi wystarczy uruchomić tylko raz.
bradw2k

3

Najpierw musisz zdecydować, które ze swoich „duplikatów” zatrzymasz. Jeśli wszystkie kolumny są równe, OK, możesz usunąć dowolną z nich ... Ale może chcesz zachować tylko najnowsze lub inne kryterium?

Najszybszy sposób zależy od twojej odpowiedzi na powyższe pytanie, a także od% duplikatów na stole. Jeśli wyrzucisz 50% wierszy, lepiej to zrobisz CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ;, a jeśli usuniesz 1% wierszy, użycie DELETE jest lepsze.

Również w przypadku takich czynności konserwacyjnych, ogólnie dobrze jest ustawić work_memdobrą część pamięci RAM: uruchom EXPLAIN, sprawdź liczbę N rodzajów / skrótów i ustaw work_mem na pamięć RAM / 2 / N. Użyj dużej ilości pamięci RAM; to dobre dla szybkości. O ile masz tylko jedno równoczesne połączenie ...


1

Pracuję z PostgreSQL 8.4. Kiedy uruchomiłem proponowany kod, stwierdziłem, że w rzeczywistości nie usuwa on duplikatów. Podczas wykonywania niektórych testów stwierdziłem, że dodanie „DISTINCT ON (duplicate_column_name)” i „ORDER BY duplicate_column_name” załatwiło sprawę. Nie jestem guru SQL, znalazłem to w dokumencie PostgreSQL 8.4 SELECT ... DISTINCT.

CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR $1;
  duplicate_column ALIAS FOR $2;
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

Działa to bardzo ładnie i jest bardzo szybkie:

CREATE INDEX otherTable_idx ON otherTable( colName );
CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable;

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

Usuń duplikaty według kolumn i zachowaj wiersz o najniższym identyfikatorze. Wzorzec jest pobierany z wiki postgres

Używając CTE, możesz dzięki temu uzyskać bardziej czytelną wersję powyższego

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

Przetestowałem to i zadziałało; Sformatowałem go pod kątem czytelności. Wygląda na dość wyrafinowane, ale przydałoby się pewne wyjaśnienie. Jak można zmienić ten przykład na własny przypadek użycia?
Tobias
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.