Zdublowane wiersze w tabeli

Czasami się zdarza że w tabeli jest sporo takich samych wiersz które niepotrzebnie zaśmiecają tabelę, a czasami powodują „błędy” podczas odczytu danych. Dziś w pracy się spotkałem z taką tabelą gdzie przez pomyłkę został uruchomiony skrypt z insertami do tabeli wrzucający kilkaset wierszy. Dziś pokażę jak w prosty sposób ich się pozbyć pozostawiając unikalne niezdublowane wiersze.

Na początek stworzymy sobie prostą tabelę zawierającą trzy kolumny.

USE [Database1]
GO
CREATE TABLE [dbo].[duplikaty](
[randomID] [int] NULL,
[nazwa] [nchar](10) NULL,
[region] [nchar](10) NULL
) ON [PRIMARY];
GO

W pierwszej kolumnie będzie wpisana losowa liczba w zakresie 0-10. W drugiej i trzeciej kolumnie będzie string połączony w losową liczbą od 1 do 10. A więc napełniamy tabelę 10.000 wierszami

DECLARE @intCounter as INT = 1;
DECLARE @naz1 as INT = 1;
DECLARE @reg1 as INT = 1;
WHILE @intCounter <= 10000 BEGIN SET @naz1 = ROUND(rand()*10,0); SET @reg1 = ROUND(rand()*10,0); --PRINT 'Bieżąca wartość licznika to: '+ CAST(@intCounter AS VARCHAR); INSERT INTO [dbo].[duplikaty]([randomID],[nazwa],[region]) VALUES (ROUND(rand()*10,0),'nazwa'+CAST(@naz1 as nvarchar(10)),'region'+CAST(@reg1 as nvarchar(10))) SET @IntCounter = @IntCounter + 1; END;

Sprawdzamy czy dane się zapisały

select * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region3   
0           nazwa0     region7   
0           nazwa0     region0   
0           nazwa0     region4     
...
10          nazwa9     region3   
10          nazwa9     region6   
10          nazwa9     region8   
10          nazwa9     region4   

(10000 rows affected)

W tabeli jest 10.000 wierszy ale z pewnością wiele wierszy się powtarza. Sprawdzamy ile jest unikalnych wierszy

select DISTINCT * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region3   
0           nazwa0     region7   
0           nazwa0     region0   
0           nazwa0     region4     
...
10          nazwa9     region3   
10          nazwa9     region6   
10          nazwa9     region8   
10          nazwa9     region4   

(1316 rows affected)

Tak więc czeka nas usunięcie ponad 8.684 wierszy

Możemy jeszcze sobie sprawdzić ile jest wierszy dla każdego randomID

SELECT randomID, COUNT(*) as count
FROM [Database1].[dbo].[duplikaty]
GROUP BY randomID
order by randomID;

randomID    count
----------- -----------
0           488
1           993
2           1024
3           949
4           982
5           1028
6           984
7           1055
8           1004
9           1030
10          463

(11 rows affected)

Poniższe zapytanie przypisuje każdemu wierszowi kolejny numer. W przypadku zdublowanych wierszy będą to kolejne liczby np

10          nazwa9     region9    1
10          nazwa9     region9    2
10          nazwa9     region9    3 

SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty];

randomID    nazwa      region     DuplicateCount
----------- ---------- ---------- --------------------
0           nazwa0     region0    1
0           nazwa0     region1    1
0           nazwa0     region1    2
0           nazwa0     region10   1
0           nazwa0     region10   2
0           nazwa0     region2    1
...
10          nazwa9     region8    3
10          nazwa9     region8    4
10          nazwa9     region8    5
10          nazwa9     region9    1
10          nazwa9     region9    2
10          nazwa9     region9    3

(10000 rows affected)

naszym zadaniem będzie teraz usunięcie wszystkich wierszy z DuplicateCount większym niż 1. Przed uruchomieniem DELETE najpierw sprawdzamy co nam wykona polecenie (dobra praktyka!)

WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
SELECT * FROM CTE WHERE DuplicateCount > 1;

randomID    nazwa      region     duplicatecount
----------- ---------- ---------- --------------------
0           nazwa0     region1    2
0           nazwa0     region10   2
0           nazwa0     region2    2
0           nazwa0     region2    3
0           nazwa0     region3    2
...
10          nazwa9     region8    4
10          nazwa9     region8    5
10          nazwa9     region9    2
10          nazwa9     region9    3

(8684 rows affected)

Select wzrócił nam 8683 wiersze do usunięcia. Zamieniamy SELECT na DELETE i uruchamiamy polecenia

WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
DELETE FROM CTE WHERE DuplicateCount > 1;

(8684 rows affected)

Sprawdzamy ile wierszy zostało w tabeli:

select COUNT(*) as [REKORDY] from [Database1].[dbo].[duplikaty];

REKORDY
-----------
1316

(1 row affected)

Sprawdzamy czy nie ma dwóch takich samych wierszy (powinno zwrócić tyle samo co powyższe zapytanie):

select DISTINCT * from [Database1].[dbo].[duplikaty];

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region0   
0           nazwa0     region1   
0           nazwa0     region10  
0           nazwa0     region2   
0           nazwa0     region3
...
10          nazwa9     region6   
10          nazwa9     region7   
10          nazwa9     region8   
10          nazwa9     region9   

(1316 rows affected)

Czyszczenie tabeli zostało zakończone poprawnie. Oczywiście można to samo zrobić na kilka innych sposobów m.in. wykorzystując wspomniane wcześniej narzędzia Integration Services (SSIS).

Nie będę tu już opisywał jak stworzyć za pomocą Visual Studio taki projekt. Możecie to znaleźć na wcześniejszych wpisach na mojej stronie.
Do naszego projektu wrzucamy DataFlowTask.

W środku tworzymy OLEDBSource

Konfigurujemy połączenie do naszej bazy i tabeli ze zdublowanymi wierszami

W projekcie do OLEDBSource (który powinien już nie "świecić" się na czerwono) podłączamy element SORT (on się jeszcze świeci na czerwono bo go jeszcze nie skonfigurowaliśmy).

W konfiguracji SORT'a na dole ekranu zaznaczamy opcję "Remove rows with duplicate sort values"

Podłączamy element Delivered Column

A na ich połączeniu wstawiamy "Data Viewer" który pozwoli nam na podglądnięcie przesyłanych danych.

Uruchamiamy nasz projekt

Pojawia się tabelka z 1316 unikalnymi wierszami naszej tabeli

Taką samą ilość wierszy widzimy na schemacie przepływu w naszym projekcie.

Ten wpis został opublikowany w kategorii Microsoft SQL, SSIS. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz