Când apare sarcina de optimizare a bazei de date sau se modifică structura acesteia, uneori există o sarcină incidentală de organizare a datelor deja acumulate. Este bine dacă tabelul a fost deja adus în formă normală în timpul dezvoltării, iar întregul sistem este organizat în așa fel încât să nu acumuleze informații duplicate inutile. Dacă nu este cazul, atunci când finalizați un astfel de sistem, doriți să scăpați de toate datele redundante și să faceți totul cu cea mai înaltă calitate.

În acest articol, vom lua în considerare sarcina de a elimina rândurile duplicate dintr-un tabel de bază de date. Permiteți-mi să subliniez imediat că vorbim despre necesitatea de a elimina exact liniile duplicate. De exemplu, înregistrările din tabelul de comenzi cu câmpurile „cod comandă”, „cod produs”, „cod client”, „data comandă” pot diferi doar în codul comenzii, deoarece un client poate comanda același produs de mai multe ori pe aceeași zi.o dată. Și principalul indicator aici că totul este corect este prezența unui câmp cheie.

Dacă vedem un tabel plin cu câmpuri repetate, fără necesitatea explicită pentru fiecare intrare, atunci acesta este exact ceea ce ar trebui remediat.

Un exemplu de tabel clar redundant:

Acum să vedem cum putem rezolva această problemă. Aici pot fi aplicate mai multe metode.

1. Puteți scrie o funcție pentru a compara și a repeta peste toate datele. Aceasta este o perioadă lungă de timp și nu doriți întotdeauna să scrieți cod pentru utilizare o singură dată.

2. O altă soluție este să creați o interogare de selectare cu grupare de date, astfel încât să fie returnate numai rânduri unice:

SELECTează country_id, city_name
DIN mytable
GROUP BY country_id, city_name

Obținem următorul eșantion:

Apoi, setul de date rezultat este scris într-un alt tabel.

3. În aceste decizii adiţional cod de programare sau mese suplimentare. Cu toate acestea, ar fi mai convenabil să faceți totul folosind doar interogări SQL fără tabele suplimentare. Și iată un exemplu de astfel de soluție:

DELETE a.* FROM mytable a,
(SELECTAȚI

DIN mytable b

) c
UNDE
a.country_id = c.country_id
ȘI a.city_name = c.city_name
SI a.id > c.mid

După executarea unei astfel de interogări, în tabel vor rămâne doar înregistrările unice:

Acum să aruncăm o privire mai atentă la cum funcționează totul. Când solicitați ștergerea, trebuie să specificați o condiție care specifică ce date să ștergeți și pe care să păstrați. Trebuie să eliminăm toate intrările care nu sunt unice. Acestea. dacă există mai multe înregistrări identice (sunt aceleași dacă au aceleași valori country_id și city_name), atunci trebuie să luați una dintre linii, să vă amintiți codul și să ștergeți toate înregistrările cu aceleași valori country_id și city_name, dar o altă cod (id).

Șir de interogare SQL:

DELETE a.* FROM mytable a,

specifică că ștergerea se va face din tabelul mytable.

Interogarea selectă generează apoi un tabel auxiliar în care grupăm înregistrările astfel încât toate înregistrările să fie unice:

(SELECTAȚI
b.country_id, b.city_name, MIN(b.id) mid
DIN mytable b
GROUP BY b.country_id, b.city_name
) c

MIN(b.id) mid - generează o coloană de la mijloc (abreviere min id), care conține valoarea minimă a id-ului în fiecare subgrup.

Rezultatul este un tabel care conține înregistrări unice și id-ul primului rând pentru fiecare grup de înregistrări duplicat.

Acum avem două tabele. Un general care conține toate înregistrările. Liniile suplimentare vor fi eliminate din acesta. Al doilea conține informații despre liniile care trebuie salvate.

Rămâne doar să se formeze o condiție, unde este indicată: trebuie să ștergeți toate liniile în care câmpurile country_id și city_name se potrivesc, dar id-ul nu se va potrivi. În acest caz, este selectată valoarea minimă a id-ului, astfel încât toate înregistrările al căror id este mai mare decât cel selectat în tabelul temporar sunt șterse.

De asemenea, trebuie remarcat faptul că operația descrisă poate fi efectuată dacă există un câmp cheie în tabel. Dacă dintr-o dată apare o masă fără identificator unic apoi adauga-l:

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

După executarea unei astfel de interogări, vom obține o coloană suplimentară plină cu valori numerice unice pentru fiecare rând al tabelului.

Efectuăm toate acțiunile necesare. După finalizarea operațiunii de ștergere a tabelului de înregistrările duplicate, acest câmp poate fi și el șters.

Eliminarea repetițiilor

Sursa bazei de date

Necesitatea de a elimina duplicatele din date este foarte comună, mai ales atunci când se confruntă cu probleme de calitate a datelor în medii în care a apărut duplicarea din cauza lipsei de constrângeri care ar putea asigura unicitatea datelor. Pentru a demonstra, să folosim următorul cod pentru a pregăti un exemplu de date cu comenzi duplicate într-un tabel numit MyOrders:

IF OBJECT_ID("Vânzări.Comenzile mele") NU ESTE NULL DROP TABLE Sales.MyOrders; GO SELECT * ÎN Vânzări.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;

Imaginați-vă că trebuie să eliminați duplicarea datelor, lăsând o singură instanță fiecare cu o valoare unică de ordine. Numerele duplicate sunt marcate folosind funcția ROW_NUMBER, partiționând după o valoare presupus unică (orderid în cazul nostru) și folosind o ordonare arbitrară dacă nu vă interesează ce rând să păstrați și pe care să eliminați. Iată codul în care funcția ROW_NUMBER marchează duplicatele:

SELECT ID comandă, ROW_NUMBER() OVER(PARTIȚIE BY ID comandă ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

Apoi, trebuie să luați în considerare diferite opțiuni în funcție de numărul de rânduri care trebuie șterse, procentul dimensiunii tabelului, care este acest număr, activitatea mediului de producție și alte circumstanțe. Pentru ca un număr mic de rânduri să fie șters, este de obicei suficient să folosiți o operație de ștergere complet înregistrată care șterge toate instanțele care au un număr de rând mai mare de unu:

Dar dacă numărul de rânduri care trebuie șters este mare – mai ales când este o proporție mare a rândurilor din tabel – ștergerea cu o operațiune complet înregistrată va fi prea lentă. În acest caz, luați în considerare utilizarea unei operațiuni de înregistrare în bloc, cum ar fi SELECT INTO, pentru a copia rânduri unice (cu numărul 1) într-un alt tabel. După aceea, tabelul original este șters, apoi noul tabel primește numele tabelului șters, constrângerile de index și declanșatoarele sunt recreate. Iată codul pentru soluția finalizată:

WITH C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT orderid, custid, empid, orderdate, requireddate, shippeddate, shipperiid, freight, shipname, shipaddress, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- recreați indici, constrângeri și declanșatoare

Pentru simplitate, nu am adăugat niciun control al tranzacțiilor aici, dar ar trebui să vă amintiți întotdeauna că mai mulți utilizatori pot lucra cu date în același timp. La implementarea acestei metode într-un mediu de producție, trebuie respectată următoarea secvență:

Tranzacție deschisă.

Ia un lacăt de masă.

Executați o instrucțiune SELECT INTO.

Ștergeți și redenumiți obiectele.

Recreează indici, limite și declanșatoare.

Angajați tranzacția.

Există o altă opțiune - filtrați numai rândurile unice sau numai rândurile neunice. Ambele funcții - ROW_NUMBER și RANK - sunt calculate pe baza ordonării după ordinea, ceva de genul acesta:

SELECT ID comandă, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;

Rețineți că în rezultate, doar un rând pentru fiecare valoare unică în orderid se potrivește cu numărul rândului și cu rangul. De exemplu, dacă trebuie să ștergeți o mică parte a datelor, puteți încapsula interogarea anterioară într-o definiție CTE și, în interogarea exterioară, executați instrucțiunea de ștergere a rândurilor pentru care număr diferit linii și rang.

(25-07-2009)

În articolul anterior, ne-am uitat la rezolvarea problemei duplicatelor cauzate de absența unei chei primare. Să luăm acum în considerare un caz mai dificil, când cheia pare să fie acolo, dar este sintetică, care, dacă nu este proiectată corect, poate duce și la duplicate în ceea ce privește domeniul subiectului.

Este ciudat, dar când vorbesc despre deficiențele cheilor sintetice în cursuri, totuși dau constant peste faptul că studenții le folosesc invariabil în primele proiecte de baze de date. Aparent, o persoană are o nevoie genetică de a renumerota totul și doar un psihoterapeut poate ajuta aici. :-)

Deci, să presupunem că avem un tabel cu un id de cheie primară și un nume de coloană, care, în funcție de constrângerile domeniului, trebuie să conțină valori unice. Cu toate acestea, dacă definiți structura tabelului după cum urmează

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

atunci nimic nu împiedică apariția duplicatelor. Ar trebui să utilizați următoarea structură de tabel:

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

Toată lumea știe cum să o facă corect, dar de multe ori trebuie să te confrunți cu structuri și date „moștenite” care încalcă constrângerile domeniului. Iată un exemplu:

numele id 1 Ioan 2 Smith 3 Ioan 4 Smith 5 Smith 6 Tom

Puteți întreba: „Cum este această problemă diferită de cea anterioară? Există o soluție și mai simplă aici - doar eliminați toate rândurile din fiecare grup cu aceleași valori în coloana de nume, lăsând doar rândul cu minim / maxim valoarea id. De exemplu, astfel:"

DELETE FROM T_pk WHERE id > (SELECTARE MIN(id) FROM T_pk X WHERE X.name = T_pk.name);

Bine, dar încă nu ți-am spus totul. :-) Imaginați-vă că avem un tabel copil T_details care este legat de tabelul T_pk cu o cheie străină:

CREATE TABLE T_details (id_pk INT REFERENȚE CHEIE STRĂINE T_pk ON DELETE CASCADE , culoare VARCHAR (10 ), CHEIE PRIMARĂ (id_pk, culoare);

Acest tabel poate conține următoarele date:

culoarea id_pk 1 albastru 1 roșu 2 verde 2 roșu 3 roșu 4 albastru 6 roșu

Pentru o mai mare claritate, folosim interogarea

SELECTează id, nume, culoare FROM T_pk JOIN T_details ON id= id_pk;

pentru a vedea numele:

culoarea numelui id 1 John albastru 1 John roșu 2 Smith verde 2 Smith roșu 3 John roșu 4 Smith albastru 6 Tom roșu

Astfel, se dovedește că datele legate de fapt de o persoană au fost separate în mod eronat în diferite înregistrările părinților. În plus, în acest tabel au apărut duplicate:

1 Ioan roșu 3 Ioan roșu

Evident, astfel de date vor duce la analize și rapoarte eronate. În plus, ștergerea în cascadă va duce la pierderea datelor. De exemplu, dacă lăsăm doar rândurile cu ID-ul minim în fiecare grup în tabelul T_pk, atunci vom pierde rândul

4 Smith albastru

în tabelul T_details. Prin urmare, trebuie să luăm în considerare ambele tabele atunci când eliminăm duplicatele.

Procedura de „curățare” a datelor poate fi efectuată în două etape:

1. Actualizați tabelul T_details atribuind date referitoare la același nume la id cu număr minimîntr-un grup.
2. Eliminați duplicatele din tabelul T_pk, lăsând doar rândurile cu cel mai mic ID din fiecare grup cu aceeași valoare în coloana de nume.

Actualizarea tabelului T_details

SELECT ID_pk, nume, culoare , RANK () OVER (PARTIȚIE BY nume, culoare ORDER BY nume, culoare, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id FROM T_pk X JOIN T_details ON id=id_pk;

determină prezența duplicatelor (value dup > 1) și valoarea minimă a id-ului în grupul de nume identice (min_id). Iată rezultatul acestei interogări:

id_pk nume culoare dup min_id 1 John albastru 1 1 1 John roșu 1 1 3 John roșu 2 1 4 Smith albastru 1 2 2 Smith verde 1 2 2 Smith roșu 1 2 6 Tom roșu 1 6

Acum trebuie să înlocuim valoarea id_pk cu valoarea min_pk pentru toate rândurile, cu excepția celui de-al treilea, deoarece această linie este un duplicat al celei de-a doua rânduri, așa cum este indicat de valoarea dup=2. O solicitare de actualizare poate fi scrisă astfel:

UPDATE T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, nume, culoare , RANK () OVER (PARTIȚIE BY nume, culoare ORDER BY nume, culoare, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk. = X.name) min_id FROM T_pk X JOIN T_details ON id=id_pk) Y ON Y.id_pk=T_d.id_pk WHERE dup =1 ;