poniedziałek, 4 sierpnia 2008

Usuwanie duplikatów

Czasami zdarza się tak, że w naszej bazie dancyh występują duplikaty. Nie powinno się to zdarzyc jeżeli są dobre klucze podstawowe w tabelach. Jedkaże kiedy ich nie mamy, lub kiedy dane są importowane z pliku płaskiego, który nie ma żadnej kontroli poprawności danych, musimy sie pozbyć tych nadmiernych informacji.

Istnieje kilka sposobów na usuwanie duplikatów. W zależności od tego czy uważamy za duplikat wiersz, który ma wszystkie wartości w kolumnach takie same jak inny wiersz w tabeli czy duplikatem będzie dla nas wiersz którego jedna kolumna ma taką samą wartość w innym wierszu, trzeba rozważyć różne techiniki usuwania nadmiernych danych.

Na początek stworzę przykładową tabelę, którą wypełnię danymi.

USE TEMPDB
GO


IF OBJECT_ID('tempdb..#KLIENCI') IS NOT NULL
DROP TABLE #KLIENCI

CREATE TABLE #KLIENCI (
ID INT,
NAZWA VARCHAR(50),
TELEFON VARCHAR(9)
)


INSERT INTO #KLIENCI (ID, NAZWA, TELEFON)
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '987456321'
UNION ALL
SELECT '3', 'NOWY SWIAT', '564789321'
UNION ALL
SELECT '1', 'CONTOSO LTD', '123456789'
UNION ALL
SELECT '2', 'MERCURY LTD', '111111111'


Pobierzmy teraz zawartość tabeli.

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
-------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111


Jak widać z powyższego listingu mamy dwa rodzaje duplikatów. Pierwszy dla klienta Contoso Ltd ma wszystkie wartości w kolumnach w obu wierszah identyczne. Drugi typ to klient Mercury Ltd (id = 2), który ma dwie różne wartości w kolumnie telefon.

Pierwszy typ duplikatów możemy usunąć korzystając z tabeli tymczasowej, którą wypełniamy unikalnymi wierszami. Następnie usuwamy wszystkie dane z naszej orginalnej tabeli a później kopiujemy zawartość tabeli tymczasowej do naszej tabeli pierwotnej.

SELECT DISTINCT * INTO #DUPLIKATY FROM #KLIENCI

TRUNCATE TABLE #KLIENCI

INSERT INTO #KLIENCI(ID, NAZWA, TELEFON) SELECT ID, NAZWA, TELEFON FROM #DUPLIKATY
DROP TABLE #DUPLIKATY


Pobierając zawartość naszej tabeli otrzymamy teraz następujący rezultat:

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
2 MERCURY LTD 987456321
3 NOWY SWIAT 564789321


Jak widać pozbyliśmy się jednego wiersza dla klienta Contoso Ltd. Technika ta będzie działać zarówno na SQL Server 2000 jak i nowszych wersjach.

Znacznie bardziej efektywną metodą i pozwalającą nam usuwać duplikaty, gdzie tylko kilka kolumn ma takie same wartości dla różnych wierszy jest technika z wykorzystaniem nowej funkcji, dostępnej od SQL Server 2005, ROW_NUMBER oraz CTE.

Korzystając z funkcji ROW_NUMBER tworzymy numery kolejnych wierszy, jednocześnie partycjonując nasze wyniki. W naszym przypadku uznajemy za duplikat wiersz który ma taką samą wartość identyfikatora klienta w dwóch lub więcej wierszach. Skorzystamy więc z funkcji ROW_NUMBER oraz bedziemy partycjonować nasze wyniki w oparciu o kolumnę id.

WITH DUPLIKATY AS (
SELECT ID, NAZWA, TELEFON, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN FROM #KLIENCI
)


Jeżeli byśmy wyświetlili zawartość naszego CTE, otrzymalibyśmy następujące rezultaty

ID NAZWA TELEFON RN
----------- ------------ --------- --
1 CONTOSO LTD 123456789 1
2 MERCURY LTD 111111111 1
2 MERCURY LTD 987456321 2
3 NOWY SWIAT 564789321 1


Teraz jedyne co musimy zrobić to usunąć nadmiarowe wiersze.

DELETE DUPLIKATY WHERE RN > 1

Pobieramy zawartość naszej tabeli

SELECT * FROM #KLIENCI

ID NAZWA TELEFON
----------- ------------ ---------
1 CONTOSO LTD 123456789
2 MERCURY LTD 111111111
3 NOWY SWIAT 564789321


Teraz mając usunięte duplikaty, możemy założyć klucz podstawowy w naszej tabeli.