Usuwanie zduplikowanych wierszy z bazy danych sqlite

Mam ogromną tabelę - 36 milionów wierszy - w SQLite3. W tej bardzo dużej tabeli znajdują się dwie kolumny:

hash - tekst
d - prawdziwe

Niektóre wiersze są duplikatami. Oznacza to, że zarówno hashi dmają te same wartości. Jeśli dwa skróty są identyczne, to wartości d. Jednak dwa identyczne dnie oznaczają dwóch identycznych hash.

Chcę usunąć zduplikowane wiersze. Nie mam kolumny klucza podstawowego.

Jaki jest najszybszy sposób na zrobienie tego?

sql database sqlite

— Łatki
źródło

Prosimy o umieszczanie odpowiedzi w blokach odpowiedzi. Później możesz zaakceptować własną odpowiedź. Zobacz także Jak działa przyjmowanie odpowiedzi?

— jww

Odpowiedzi:

122

Potrzebujesz sposobu na rozróżnienie rzędów. Opierając się na swoim komentarzu, możesz użyć do tego specjalnej kolumny z identyfikatorem wiersza .

Aby usunąć duplikaty utrzymując najniższą rowidPER (hash,d):

delete   from YourTable
where    rowid not in
         (
         select  min(rowid)
         from    YourTable
         group by
                 hash
         ,       d
         )

— Andomar
źródło

SQLite nie pozwala na dodanie kolumny klucza podstawowego, prawda?

— Poprawki z

sqlite> alter table dist add id integer primary key autoincrement; Error: Cannot add a PRIMARY KEY column

— Poprawki z

Ciekawy! Część, której potrzebujesz, to autoincrementchociaż, czy zadziała, jeśli pominiesz primary keyczęść?

— Andomar

sqlite> alter table dist add id integer autoincrement; Error: near "autoincrement": syntax error Edycja: SQLite ma pseudokolumnę typu „rowid”, która pojawia się automatycznie, czy mogę tego użyć?

— Poprawki z

delete from dist where rowid not in (select max(rowid) from dist group by hash); Wydaje się, że załatwią sprawę! Dzięki.

— Poprawki z

Wydaje mi się, że najszybciej byłoby użyć do tego samej bazy danych: dodaj nową tabelę z tymi samymi kolumnami, ale z odpowiednimi ograniczeniami (unikalny indeks na parze hash / real?), Iteruj przez oryginalną tabelę i spróbuj wstawić rekordy do nowa tabela, ignorując błędy naruszenia ograniczeń (tj. kontynuuj iterację, gdy wyjątki są zgłaszane).

Następnie usuń starą tabelę i zmień nazwę nowej na starą.

— MaDa
źródło

Wydaje mi się, że nie jest to tak eleganckie, jak zwykła zmiana tabeli, ALE jedną naprawdę dobrą rzeczą w twoim podejściu jest to, że możesz ponownie uruchomić go tyle razy, ile chcesz, bez dotykania / niszczenia danych źródłowych, dopóki nie będziesz absolutnie zadowolony z wyników .

— Adrian K

Jeśli dodanie klucza podstawowego nie wchodzi w grę, jednym podejściem byłoby przechowywanie duplikatów DISTINCT w tabeli tymczasowej, usunięcie wszystkich zduplikowanych rekordów z istniejącej tabeli, a następnie dodanie rekordów z powrotem do oryginalnej tabeli z tabeli tymczasowej .

Na przykład (napisane dla SQL Server 2008, ale technika jest taka sama dla każdej bazy danych):

DECLARE @original AS TABLE([hash] varchar(20), [d] float)
INSERT INTO @original VALUES('A', 1)
INSERT INTO @original VALUES('A', 2)
INSERT INTO @original VALUES('A', 1)
INSERT INTO @original VALUES('B', 1)
INSERT INTO @original VALUES('C', 1)
INSERT INTO @original VALUES('C', 1)

DECLARE @temp AS TABLE([hash] varchar(20), [d] float)
INSERT INTO @temp
SELECT [hash], [d] FROM @original 
GROUP BY [hash], [d]
HAVING COUNT(*) > 1

DELETE O
FROM @original O
JOIN @temp T ON T.[hash] = O.[hash] AND T.[d] = O.[d]

INSERT INTO @original
SELECT [hash], [d] FROM @temp

SELECT * FROM @original

Nie jestem pewien, czy sqlite ma funkcję ROW_NUMBER()typu, ale jeśli tak, możesz również wypróbować niektóre z wymienionych tutaj podejść: Usuń zduplikowane rekordy z tabeli SQL bez klucza podstawowego

— rsbarro
źródło

+1, nie jestem pewien, czy sqlite obsługuje delete <alias> from <table> <alias>składnię

— Andomar