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:

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:

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:

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:

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:

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:

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.

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:

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.

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

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:

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