Gdy pojawia się zadanie optymalizacji bazy danych lub zmienia się jej struktura, czasami pojawia się okazjonalne zadanie uporządkowania już zgromadzonych danych. Dobrze, jeśli tabela została już doprowadzona do normalnej postaci podczas tworzenia, a cały system jest zorganizowany w taki sposób, aby nie gromadził niepotrzebnych zduplikowanych informacji. Jeśli tak nie jest, to finalizując taki system, chcesz pozbyć się wszelkich zbędnych danych i zrobić wszystko z najwyższą jakością.

W tym artykule rozważymy zadanie usunięcia zduplikowanych wierszy w tabeli bazy danych. Od razu zaznaczę, że rozmawiamy o potrzebie usunięcia dokładnie zduplikowanych wierszy. Np. rekordy w tabeli zamówień z polami „kod zamówienia”, „kod produktu”, „kod klienta”, „data zamówienia” mogą różnić się tylko kodem zamówienia, ponieważ jeden klient może kilkakrotnie zamówić ten sam produkt na tego samego dnia. raz. A głównym wskaźnikiem tutaj, że wszystko jest w porządku, jest obecność pola kluczowego.

Jeśli widzimy tabelę przepełnioną powtarzającymi się polami, bez wyraźnej potrzeby każdego wpisu, to jest to dokładnie to, co należy naprawić.

Przykład wyraźnie nadmiarowej tabeli:

Zobaczmy teraz, jak możemy rozwiązać ten problem. Można tutaj zastosować kilka metod.

1. Możesz napisać funkcję do porównania i iteracji wszystkich danych. To długo i nie zawsze chcesz pisać kod do jednorazowego użytku.

2. Innym rozwiązaniem jest utworzenie zapytania wybierającego z grupowaniem danych, tak aby zwracane były tylko unikalne wiersze:

SELECT identyfikator kraju, nazwa_miasta
Z mojej tabeli
GROUP BY identyfikator_kraju, nazwa_miasta

Otrzymujemy następującą próbkę:

Następnie wynikowy zestaw danych jest zapisywany w innej tabeli.

3. W te decyzje dodatkowy kod programowania lub dodatkowe stoły. Jednak wygodniej byłoby robić wszystko za pomocą samych zapytań SQL bez dodatkowych tabel. A oto przykład takiego rozwiązania:

USUŃ a.* Z mytablicy a,
(WYBIERZ

Z mytable b

) C
GDZIE
a.id_kraju = c.id_kraju
AND a.city_name = c.city_name
ORAZ a.id > c.mid

Po wykonaniu takiego zapytania w tabeli pozostaną tylko unikalne rekordy:

Teraz przyjrzyjmy się bliżej, jak to wszystko działa. Prosząc o usunięcie, należy określić warunek określający, które dane należy usunąć, a które zachować. Musimy usunąć wszystkie nieunikalne wpisy. Tych. jeśli istnieje kilka identycznych rekordów (są takie same, jeśli mają te same wartości country_id i city_name), to musisz wziąć jedną z linii, zapamiętać jej kod i usunąć wszystkie rekordy z tymi samymi wartościami country_id i city_name, ale różnymi kod (id).

Ciąg zapytania SQL:

USUŃ a.* Z mytablicy a,

określa, że ​​usunięcie zostanie wykonane z tabeli mytable.

Zapytanie wybierające generuje następnie tabelę pomocniczą, w której grupujemy rekordy tak, aby wszystkie rekordy były unikatowe:

(WYBIERZ
b.id_kraju, b.nazwa_miasta, MIN(b.id) w połowie
Z mytable b
GROUP BY b.country_id, b.city_name
) C

MIN(b.id) mid - generuje kolumnę mid (skrót min id), która zawiera minimalną wartość identyfikatora w każdej podgrupie.

Wynikiem jest tabela zawierająca unikalne rekordy i identyfikator pierwszego wiersza dla każdej grupy zduplikowanych rekordów.

Teraz mamy dwa stoły. Jeden generał zawierający wszystkie rekordy. Dodatkowe linie zostaną z niego usunięte. Drugi zawiera informacje o liniach do zapisania.

Pozostaje tylko utworzyć warunek, w którym jest to wskazane: musisz usunąć wszystkie wiersze, w których pola country_id i city_name pasują, ale identyfikator nie będzie pasował. W takim przypadku wybierana jest minimalna wartość identyfikatora, więc wszystkie rekordy, których identyfikator jest większy niż wybrany w tabeli tymczasowej, są usuwane.

Należy również zauważyć, że opisaną operację można wykonać, jeśli w tabeli znajduje się pole kluczowe. Jeśli nagle pojawi się tabela bez unikalnego identyfikatora, po prostu ją dodaj:

ALTER TABLE ` mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`)

Wykonując takie zapytanie otrzymujemy dodatkową kolumnę wypełnioną unikalnymi wartościami liczbowymi dla każdego wiersza tabeli.

Przeprowadzamy wszystkie niezbędne czynności. Po zakończeniu operacji czyszczenia tabeli ze zduplikowanych rekordów, to pole można również usunąć.

Usuwanie powtórzeń

Źródło bazy danych

Konieczność wyeliminowania duplikatów z danych jest bardzo powszechna, zwłaszcza gdy mamy do czynienia z problemami z jakością danych w środowiskach, w których duplikacja powstała z powodu braku ograniczeń, które mogłyby zapewnić unikalność danych. Aby to zademonstrować, użyjmy poniższego kodu, aby przygotować przykład danych ze zduplikowanymi zamówieniami w tabeli o nazwie MyOrders:

IF OBJECT_ID("Sales.MyOrders") NIE JEST NULL DROP TABLE Sales.MyOrders; GO SELECT * INTO Sprzedaż.MojeZamówienia FROM Sprzedaż.Zamówienia UNION ALL SELECT * FROM Sprzedaż.Zamówienia UNION ALL SELECT * FROM Sprzedaż.Zamówienia;

Wyobraź sobie, że musisz wyeliminować duplikację danych, pozostawiając tylko jedną instancję z unikalną wartością identyfikatora zamówienia. Zduplikowane liczby są oznaczane przy użyciu funkcji ROW_NUMBER, dzieląc się według rzekomo unikalnej wartości (w naszym przypadku orderid) i używając dowolnej kolejności, jeśli nie obchodzi Cię, który wiersz zachować, a który usunąć. Oto kod, w którym funkcja ROW_NUMBER oznacza duplikaty:

SELECT identyfikator zamówienia, ROW_NUMBER() OVER(PARTITION BY identyfikator zamówienia ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

Następnie należy rozważyć różne opcje w zależności od liczby wierszy do usunięcia, procentu wymiaru tabeli, tego, jaka jest ta liczba, aktywności środowiska produkcyjnego i innych okoliczności. Aby usunąć niewielką liczbę wierszy, zwykle wystarczy użyć operacji usuwania z pełnym logowaniem, która usuwa wszystkie wystąpienia o numerze wiersza większym niż jeden:

Ale jeśli liczba wierszy do usunięcia jest duża — zwłaszcza gdy jest to duża część wierszy w tabeli — usuwanie za pomocą pełnej zarejestrowanej operacji będzie zbyt wolne. W takim przypadku rozważ użycie operacji rejestrowania zbiorczego, takiej jak SELECT INTO, aby skopiować unikatowe wiersze (o numerze 1) do innej tabeli. Następnie oryginalna tabela jest usuwana, a nowej tabeli jest nadawana nazwa usuniętej tabeli, odtwarzane są ograniczenia indeksu i wyzwalacze. Oto kod ukończonego rozwiązania:

WITH C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT identyfikator zamówienia, custid, empid, data zamówienia, wymagana data, data wysyłki, identyfikator nadawcy, fracht, nazwa statku, adres statku, miasto statku, region statku, kod pocztowy statku, kraj statku DO SPRZEDAŻY.ZamówieniaTmp Z C GDZIE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "Moje zamówienia"; -- odtworzyć indeksy, ograniczenia i wyzwalacze

Dla uproszczenia nie dodałem tutaj żadnej kontroli transakcji, ale zawsze należy pamiętać, że z danymi może pracować kilku użytkowników jednocześnie. Wdrażając tę ​​metodę w środowisku produkcyjnym, należy przestrzegać następującej kolejności:

Otwarta transakcja.

Zdobądź blokadę stołu.

Wykonaj instrukcję SELECT INTO.

Usuń i zmień nazwy obiektów.

Odtwórz indeksy, limity i wyzwalacze.

Zatwierdź transakcję.

Jest jeszcze inna opcja - odfiltruj tylko unikalne lub tylko nieunikalne wiersze. Obie funkcje - ROW_NUMBER i RANK - są obliczane na podstawie kolejności według orderid, mniej więcej tak:

SELECT identyfikatorzamówienia, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;

Zwróć uwagę, że w wynikach tylko jeden wiersz dla każdej unikalnej wartości w identyfikatorze kolejności odpowiada numerowi wiersza i pozycji. Na przykład, jeśli chcesz usunąć niewielką część danych, możesz zawrzeć poprzednie zapytanie w definicji CTE, a w zapytaniu zewnętrznym wykonać instrukcję usunięcia wierszy, dla których inny numer linie i ranga.

(25-07-2009)

W poprzednim artykule przyjrzeliśmy się rozwiązaniu problemu duplikatów spowodowanych brakiem klucza podstawowego. Rozważmy teraz trudniejszy przypadek, gdy klucz wydaje się istnieć, ale jest syntetyczny, co, jeśli nie zostanie prawidłowo zaprojektowane, może również prowadzić do duplikatów pod względem Tematyka.

To dziwne, ale kiedy mówię o mankamentach kluczy syntetycznych na wykładach, to ciągle natykam się na to, że studenci niezmiennie używają ich w swoich pierwszych projektach bazodanowych. Podobno człowiek ma genetyczną potrzebę przenumerowania wszystkiego i tylko psychoterapeuta może tu pomóc. :-)

Załóżmy więc, że mamy tabelę z identyfikatorem klucza podstawowego i nazwą kolumny, która zgodnie z ograniczeniami domeny musi zawierać unikalne wartości. Jeśli jednak zdefiniujesz strukturę tabeli w następujący sposób

CREATE TABLE T_pk(id INT IDENTITY PRIMARY KEY, nazwa VARCHAR(50));

wtedy nic nie stoi na przeszkodzie pojawieniu się duplikatów. Powinieneś użyć następującej struktury tabeli:

CREATE TABLE T_pk (identyfikator INT IDENTITY PRIMARY KEY , nazwa VARCHAR (50 ) UNIQUE );

Każdy wie, jak to zrobić dobrze, ale często masz do czynienia z „odziedziczoną” strukturą i danymi, które naruszają ograniczenia domeny. Oto przykład:

imię i nazwisko 1 Jan 2 Kowal 3 Jan 4 Kowal 5 Kowal 6 Tom

Możesz zapytać: „Czym różni się ten problem od poprzedniego? Tutaj jest jeszcze prostsze rozwiązanie – wystarczy usunąć wszystkie wiersze z każdej grupy z tymi samymi wartościami w kolumnie nazwy, pozostawiając tylko wiersz z minimum/maksimum id wartość. Na przykład tak:"

DELETE FROM T_pk WHERE id > (SELECT MIN(id) FROM T_pk X WHERE X.nazwa = T_pk.nazwa);

Racja, ale nie powiedziałem jeszcze wszystkiego. :-) Wyobraź sobie, że mamy tabelę potomną T_details, która jest powiązana z tabelą T_pk z kluczem obcym:

CREATE TABLE T_details (id_pk INT ODNIESIENIA DO KLUCZY OBCYCH T_pk ON DELETE CASCADE , kolor VARCHAR (10 ), PRIMARY KEY (id_pk, kolor);

Ta tabela może zawierać następujące dane:

id_pk kolor 1 niebieski 1 czerwony 2 zielony 2 czerwony 3 czerwony 4 niebieski 6 czerwony

Dla większej przejrzystości używamy zapytania

SELECT id, nazwa, kolor FROM T_pk JOIN T_details ON id= id_pk;

aby zobaczyć nazwiska:

kolor nazwy identyfikatora 1 Jan niebieski 1 Jan czerwony 2 Smith zielony 2 Smith czerwony 3 Jan czerwony 4 Smith niebieski 6 Tom czerwony

Okazuje się więc, że dane faktycznie dotyczące jednej osoby zostały błędnie podzielone na różne rekordy rodziców. Ponadto w tej tabeli pojawiły się duplikaty:

1 Jan czerwony 3 Jan czerwony

Oczywiście takie dane będą prowadzić do błędnych analiz i raportów. Co więcej, kaskadowe usuwanie spowoduje utratę danych. Na przykład, jeśli zostawimy tylko wiersze z minimalnym identyfikatorem w każdej grupie w tabeli T_pk, to stracimy wiersz

4 Kowal niebieski

w tabeli T_details. Dlatego przy eliminacji duplikatów musimy wziąć pod uwagę obie tabele.

Procedurę „czyszczenia” danych można przeprowadzić w dwóch etapach:

1. Zaktualizuj tabelę T_details, przypisując dane związane z tą samą nazwą do id with minimalna liczba w grupie.
2. Usuń duplikaty z tabeli T_pk, pozostawiając tylko wiersze o najniższym identyfikatorze w każdej grupie z taką samą wartością w kolumnie name.

Aktualizacja tabeli T_details

SELECT id_pk, nazwa, kolor , RANK () OVER (PARTITION BY nazwa, kolor ORDER BY nazwa, kolor, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.nazwa = X.nazwa) min_id FROM T_pk X JOIN T_details WŁ id=id_pk;

określa obecność duplikatów (wartość dup > 1) oraz minimalną wartość id w grupie identycznych nazw (min_id). Oto wynik tego zapytania:

id_pk nazwa kolor dup min_id 1 Jan niebieski 1 1 1 Jan czerwony 1 1 3 Jan czerwony 2 1 4 Kowal niebieski 1 2 2 Kowal zielony 1 2 2 Kowal czerwony 1 2 6 Kowal czerwony 1 6

Teraz musimy zastąpić wartość id_pk wartością min_pk dla wszystkich wierszy z wyjątkiem trzeciego, ponieważ ten wiersz jest duplikatem drugiego wiersza, na co wskazuje wartość dup=2. Żądanie aktualizacji można napisać w następujący sposób:

UPDATE T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, nazwa, kolor , RANK () OVER (PARTITION BY nazwa, kolor ORDER BY nazwa, kolor, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk. = X.name) min_id FROM T_pk X DOŁĄCZ T_details ON id=id_pk) Y ON Y.id_pk=T_d.id_pk WHERE dup =1 ;