Spis treści:

Kategoria:SQL Server


Usuwanie duplikatów z tabeli bez klucza - SQL Server

Wielokrotnie spotkałem się z takimi tabelami, które mają zwielokrotnione wpisy. Zdarza się, że jest to zjawisko planowane i pożądane. Co jednak wtedy, gdy nie chcemy dopuścić do takiego stanu? Najłatwiej założyć klucz unikalny.

Łatwo powiedzieć, trudniej zrobić. Nie da się założyć klucza, jeżeli wcześniej nie usuniemy duplikatów. Przedstawię klika technik, które mogą zostać wykorzystane. Są to zarówno rozwiązania proste, najczęściej najwolniejsze, mniej lub bardziej skomplikowane, ale sprawniejsze, niektóre wymagające większego nakładu i wymagające pewnych założeń. Przejdźmy jednak do rzeczy.

Przykład testowy

Tabela testowa będzie prosta. Pokazane rozwiązania dają się jednak rozszerzyć tak, aby działały z bardziej skomplikowanymi tabelami. Poniżej skrypt tworzący nasz poligon doświadczalny:

CREATE TABLE Duplicates
(
ID int,
Nazwa varchar(10)
)

INSERT INTO Duplicates
VALUES(1,'A'),(2,'B'),(2,'B'),
(3,'C'),(3,'C'),(3,'C'),(4,'D'),(1,'E')

Prosta tabela z dwoma kolumnami i z powtarzającymi się wartościami. Dwa razy wystąpuje para 2-B, natomiast para 3-C trzy razy. To tych wartości będziemy się pozbywać. Przyjrzyjmy się jeszcze zawartości tabeli:

IDNazwa
1A
2B
2B
3C
3C
3C
4D
1E

Tabela tymczasowa i dwukrotne przekopiowanie danych

Pierwsze rozwiązanie jest wyjątkowo proste. Na początku tworzymy tymczasową tabelę z takim samym schematem i kopiujemy do niej dane z tabeli źródłowej korzystając przy okazji z instrukcji DISTINCT. Drugi etap to wyczyszczenie tabeli z duplikatami, a trzeci - skopiowanie danych z powrotem do oryginalnej tabeli. Na końcu wystarczy posprzątać po sobie i usunąć tabelę tymczasową. Pełny skrypt zaprezentowany jest poniżej:

--Tabela tymczasowa i uzupełnienie danych od początku
SELECT DISTINCT * INTO #temp FROM Duplicates
TRUNCATE TABLE Duplicates
INSERT INTO Duplicates SELECTFROM #temp
DROP TABLE #temp

Rozwiązanie proste, ale mało wydajne. Przypuśćmy, że duplikaty znajdują się w tabeli z milionem rekordów, a duplikatów jest kilka lub kilkadziesiąt. Co się dzieje? Następuje dwukrotne kopiowanie miliona rekordów (za drugim razem prawie milion). Na szczęście nie jest to jedyne rozwiązanie.

Zmiana nazwy tabeli i kopiowanie DISTINCT

Da się uniknąć jednego kopiowania. Jak? Tytuł wyjaśnił już chyba wszystko. Tym razem procedura składa się z trzech kroków. Pierwszy - zmiana nazwy tabeli źródłowej. Drugi - skopiowanie do tabeli docelowej, która - tak jest - przyjmie nazwę dawnej tabeli źródłowej. Trzeci etap to sprzątanie. Skrypt pokazany jest na poniższym listingu:

--Zmiana nazwy zamiast tworzenia nowej tabeli
--Zamiast dwóch operacji kopiowania wykonujemy jedną
EXEC sp_rename 'Duplicates''Duplicates_Temp';
SELECT DISTINCT * INTO Duplicates FROM Duplicates_Temp
DROP TABLE Duplicates_Temp

W dalszym ciągu występuje jednak problem kopiowania całej zawartości i konieczności sprzątania całego bałaganu. Czy da się to zrobić inaczej?

Adres rekordu i jego unikatowe położenie

Wiemy, bo takie było początkowe założenie, że rekordy nie mają unikatowego klucza. Jak więc wskazać w sposób jednoznaczny, że chcemy usunąć pierwszą parę 2-B nie ruszając drugiej lub drugą nie ruszając pierwszej? Tradycyjne wskazanie nie nadaje się do tego bo 2-B to 2-B. Tak tego nie rozróżnimy. Trzeba jednak wiedzieć, że każdy z tych rekordów gdzieś na dysku leży, w jakimś pliku, w którejś komórce pamięci masowej. Informacje te można wydobyć korzystając z ukrytej kolumny %%physloc%%. Teraz pozostaje nam tylko zadecydować, który spośród identycznych rekordów zostawić. Ja w zaprezentowanym dalej skrypcie zostawiam ten o najniższym adresie. W rozwiązaniu skorzystałem z instrukcji GROUP BY oraz funkcji agregującej MIN. Cały skrypt zamieściłem na poniższym listingu:

--Wykorzystanie Row Identifier (RID)
--Każdy rekord ma swój unikatowy adres
DELETE D
FROM Duplicates D
LEFT JOIN
(
  SELECT ID, Nazwa, MIN(%%physloc%%AS [RID]
  FROM Duplicates
  GROUP BY ID, Nazwa
) T
ON T.RID=D.%%physloc%%
WHERE T.RID IS NULL

Tam, gdzie GROUP BY znajdzie więcej rekordów zostawiany jest ten o najmniejszym (minimalnym, najniższym) adresie. LEFT JOIN wykonany po adresach zwróci NULL tam, gdzie adres nie jest minimalny (gdy rekord jest jeden to jego adres jest minimalny, gdy jest więcej - tylko jeden może być minimalny). Ten oto mało znaczący NULL decyduje o wszystkim.

Za każdym razem gdy widzę LEFT JOIN lub RIGHT JOIN zastanawiam się, czy nie da się tego zamienić na zwykłe złączenie. LEFT/RIGHT JOIN próbuje dopasować wszystkie rekordy, a w przypadku braku dopasowania zwraca NULL. Sprawia to, że przetwarzane zbiory są większe. Czy da się tutaj wyeliminować LEFT JOIN?

Likwidacja LEFT JOIN

Oczywiście, że się da. Można bezpośrednio usuwać te rekordy, które mają wszystkie pola zgodne, ale różnią się właśnie tym minimalnym adresem. Zmodyfikowany skrypt pokazany jest poniżej:

--Wykorzystanie Row Identifier (RID)
--Złączenie odwrotne, JOIN jest dużo bardziej
--wydajne niż LEFT JOIN, wymaga podania kolumn złączenia
DELETE D
FROM Duplicates D
JOIN
(
  SELECT ID, Nazwa, MIN(%%physloc%%AS [RID]
  FROM Duplicates
  GROUP BY ID, Nazwa
) T
ON T.ID=D.ID AND T.Nazwa=D.Nazwa AND T.RID!=D.%%physloc%%

Szacunki planu wykonania pokazują, że JOIN pozwala przyspieszyć operację dwukrotnie. Dokładne oszacowanie jest jednak trudniejsze, bo zależy od ilości danych oraz liczby powtarzających się rekordów. Zbadanie tego problemu zostawiam czytelnikom.

Ukryta kolumna %%physloc%% jest tak ukryta, że nie występuje nawet w dokumentacji. Po drugie, metoda nadaje się do wykorzystania dopiero od wersji SQL Server 2008. To sprawia, że należy mieć się na baczności i mimo niewątpliwej użyteczności %%physloc%% używać jej z umiarem. Nigdy nie wiadomo, czy ta kolumna nie zginie w przyszłych wersjach lub nie zostanie zastąpiona przez jakiś udokumentowany odpowiednik.

Duplikaty i dynamiczne TOP N

Nagłówek jest pewnie dla niektórych trochę tajemniczy, ale zaraz się wszystko wyjaśni. Metoda jest bardzo sprawna wtedy, gdy duplikatów jest niewiele. Po drugie wymaga precyzyjnego napisania skryptu, bo należy dynamicznie skleić warunek po wszystkich kolumnach. Zanim przejdę do metody właściwej, należy się parę słów wyjaśnienia. TOP N w ogromnej większości wykorzystywane jest w połączeniu z instrukcją SELECT. Mało kto wie, że można użyć TOP N w instrukcjach DELETE i UPDATE. Jeżeli chcemy usunąć rekordy spełniające podane kryteria, ale tylko dwa pierwsze, napiszemy:

DELETE TOP(2) FROM Tabela

Gdy rekordów spełniających określone warunki będzie więcej (w przykładzie nie ma warunków), pozostałe nie zostaną usunięte.

Podobne efekty można uzyskać przy pomocy instrukcji SET ROWCOUNT N.

W przeciwieństwie do %%physloc%%, rozwiązanie zaprezentowane poniżej korzysta tylko z udokumentowanych funkcji.

--Dynamiczne usuwanie rekordów z wykorzystaniem TOP(COUNT(*)-1)
DECLARE @sql nvarchar(MAX)=''
SELECT @sql=@sql+'DELETE TOP ('+CAST(COUNT(*)-1 as nvarchar(8))
+') FROM Duplicates WHERE ID='+CAST(ID as nvarchar(8))
+' AND Nazwa='''+Nazwa+''''+CHAR(10)
FROM Duplicates
GROUP BY ID, Nazwa
HAVING COUNT(*)>1
EXEC(@sql)

Wadą tego rozwiązania jest dość złożony proces skryptowania i mała czytelność, zwłaszcza wtedy, gdy w tabeli jest dużo kolumn. Jest też mało wydajne, gdy w tabeli znajduje się bardzo dużo duplikatów.

Wyrażenie CTE numerujące duplikaty

Jest to chyba najlepszy i najbardziej uniwersalny sposób usuwania duplikatów, dlatego zostawiłem go na sam koniec. Wyrażenia CTE zwykle utożsamiane są z rekurencją. Trochę to krzywdzące, bo z definicji mają służyć do wyrażania wpólnych, ogólnych (ang. common) wyrażeń na tabeli. Trik polega na tym, że wyrażenie niejawnie przekazuje rekordy do instrukcji SELECT korzystającej z tego wyrażenia. To tak, jakby przekazać referencję na zewnątrz tego, co zostało wyliczone w CTE. Co to ma wspólnego z usuwaniem?

Do każdego rekordu dodamy jedną kolumnę, która będzie określała kolejny numer tego samego rekordu (tego samego w sensie wartości w kolumnach). Do numerowania użyjemy funkcji ROW_NUMBER() podzielonej (PARTITION) na podstawie wszystkich kolumn. Każda taka grupa będzie miała swoją numerację. Logiczne jest, że usuniemy te rekordy, które mają numer większy niż 1. Popatrzmy na przykładowy skrypt wykorzystujący wyrażenie CTE:

WITH RD AS
(SELECT ROW_NUMBER() OVER (PARTITION BY Nazwa,ID ORDER BY (SELECT 1)) Row,
 ID, Nazwa FROM Duplicates)
DELETE FROM RD
WHERE RD.Row>1

Zaskakujące może się wydawać umieszczenie w skrypcie fragmentu: ORDER BY (SELECT 1). Składnia ROW_NUMBER() wymaga podania sposobu sortowania, które określi porządek numerowania. Nam na takim porządku w ramach jednej grupy identycznych rekordów nie zależy. Co więcej, sortowanie to zawsze jakiś wysiłek dla bazy i strata czasu. Aby nieco oszukać składnię, która nie dopuszcza w tym miejscu stałych, można tam wstawić właśnie takie proste wyrażenie.

Jak wspomniałem, CTE przekazuje coś na kształt referencji znanej z innych języków programowania. Obiekt niby ten sam (wartości), a jednak inny (referencja na inny adres). Nie ma złączeń, nie ma tabel tymczasowych, nie trzeba po sobie sprzątać.

Podsumowanie

Wypadałoby wyciągnąć jakieś wnioski. Jedyne co mi przychodzi do głowy to to, że jeden problem może być rozwiązany na wiele różnych sposobów. Zachęcam do poszukiwań. Nie zawsze to, co wydaje się oczywiste (tabela tymczasowa) jest najlepszym rozwiązaniem. Działa, ale właśnie dlatego, że działa, powstrzymuje nas od szukania innych rozwiązań. Każde ma swoje wady i zalety, każde może lepiej pasować w konretnych przypadkach. Podejrzewam, że nie przedstawiłem wszystkich sposobów usuwania duplikatów. Zachęcam do dzielenia się swoimi technikami i pomysłami w komentarzach.

Kategoria:SQL Server

, 2013-12-20

Komentarze:

brfr (2012-11-06 15:15:50)
Witam,
Genialnie. Genialnie zadziałało.
....a jeśli dodać select nadrzędny z filtrem kolumny =1 to bez konieczności tworzenia tabeli tymczasowej uzyska się oczekiwany zestaw wierszy.
1rw1n (2018-07-30 13:49:06)
Dziękuje za artykuł! Przydał się w pracy.
Dodaj komentarz
Wyślij
Ostatnie komentarze
Przydałyby się jeszcze 2 rzeczy do cz. 3 i byłoby superanckie.
1. Na starcie sortuje wg jakiejś kolumny i tam jest już strzałeczka. Widok takiej strzałeczki daje znać użytkownikowi, że taką tabele można sortować, a na razie pojawia się ona tylko po kliknięciu.
2. Uwzględnienie polskich znaków, bo np. przy sortowaniu Nazwisk i Imion jest to bardzo uciążliwe.
Ogólnie bardzo fajnie i prosto.
PS. Jest ten artykuł z jQuery już dostępny.
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe