Meniul
AcasăYandex

Eliminați duplicatele cu valoare sql mare. Eliminarea repetițiilor în T-SQL

Eliminarea repetițiilor

Sursa bazei de date

Necesitatea deduplicarii datelor este comună, în special atunci când se abordează problemele de calitate a datelor în medii în care a apărut duplicarea din cauza lipsei de constrângeri pentru a 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 datele duplicate, lăsând doar o singură instanță din fiecare cu o valoare de ordine unică. Numerele duplicate sunt marcate folosind funcția ROW_NUMBER, împărțind după o valoare presupus unică (orderid în cazul nostru) și folosind ordinea aleatorie 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;

Atunci trebuie să luați în considerare diferite varianteîn funcție de numărul de rânduri care trebuie șterse, procentul de dimensiunea tabelului, care este acel număr, activitatea mediului de producție și alte circumstanțe. Când numărul de rânduri șterse este mic, este de obicei suficient să folosiți o operațiune de ștergere complet înregistrată, care șterge toate instanțele cu un număr de rând mai mare de unu:

Dar dacă numărul de rânduri care sunt șterse este mare - mai ales când constituie o mare parte din rândurile tabelului - ștergerea cu înregistrare completă operațiunile de jurnal vor fi prea lente. În acest caz, ați putea dori să luați în considerare utilizarea unei operațiuni de înregistrare în bloc, cum ar fi SELECT INTO, pentru a copia rândurile unice (numerotate 1) într-un alt tabel. După aceasta, tabelul original este șters masa noua este atribuit numele tabelului de la distanță, sunt recreate constrângerile, indecșii și declanșatoarele. 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"; -- recrearea indicilor, constrângerilor și declanșatorilor

Pentru simplitate, nu am adăugat niciun control al tranzacțiilor aici, dar trebuie să vă amintiți întotdeauna că mai mulți utilizatori pot lucra cu datele în același timp. Când implementați această metodă într-un mediu de producție, trebuie să urmați următoarea secvență:

    Tranzacție deschisă.

    Obțineți un lacăt de masă.

    Executați instrucțiunea SELECT INTO.

    Ștergeți și redenumiți obiectele.

    Recreează indici, constrângeri și declanșatoare.

    Angajați tranzacția.

Există o altă opțiune - pentru a filtra numai rânduri unice sau numai non-unice. Atât ROW_NUMBER, cât și RANK sunt calculate pe baza ordinii, 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;

Observați că în rezultate, doar un rând pentru fiecare valoare unică din orderid se potrivește cu numărul rândului și rangul rândului. De exemplu, dacă trebuie să eliminați o mică parte date, puteți încapsula interogarea anterioară într-o definiție CTE și, în interogarea exterioară, puteți emite o instrucțiune pentru a șterge rândurile care au număr diferit linii şi rang.

(25-07-2009)

În articolul anterior, ne-am uitat la rezolvarea problemei duplicate cauzate de lipsa unei chei primare. Să luăm acum în considerare un caz mai dificil, când cheia pare să fie acolo, dar este sintetică, care, proiectată greșit, poate duce și la duplicări din punct de vedere. domeniul subiectului.

Este ciudat, dar când vorbesc la prelegeri despre deficiențele cheilor sintetice, mă întâlnesc în mod constant cu 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 conformitate cu restricțiile de domeniu, 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. Trebuie utilizată următoarea structură de tabel:

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

Toată lumea știe ce trebuie făcut, dar de multe ori trebuie să te confrunți cu o structură „moștenită” și cu date care încalcă constrângerile de domeniu. 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ă? La urma urmei, există o soluție și mai simplă aici - pur și simplu eliminați toate rândurile din fiecare grup cu aceleași valori în coloana de nume, lăsând doar rândul cu valoarea minimă/maximă a ID. De exemplu, astfel:"

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

Așa e, dar încă nu ți-am spus totul. :-) Imaginați-vă că avem un tabel copil T_details legat de tabelul T_pk de cheie externă:

CREATE TABLE T_details (id_pk INT REFERINȚ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, să folosim interogarea

SELECT 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 care se referă de fapt la o singură persoană au fost alocate din greșeală altora înregistrările părinților. În plus, au existat duplicate în acest tabel:

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 din tabelul T_pk, 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 legate de un nume id-ului cu numărul minim din grup.
  2. Eliminați duplicatele din tabelul T_pk, lăsând doar rândurile cu id-ul minim în 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;

detectează prezența duplicatelor (valoare dup > 1) și valoarea minima id într-un grup de nume identice (min_id). Iată rezultatul rulării 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 ;

Când apare sarcina de optimizare a unei baze de date sau structura acesteia se modifică, uneori apare o sarcină conexă de organizare a datelor deja acumulate. Este bine dacă tabelul este deja dat în dezvoltare formă normală, 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. Aș dori să subliniez imediat că despre care vorbim despre necesitatea de a elimina liniile duplicate. De exemplu, înregistrările din tabelul de comandă 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 câmp cheie.

Dacă vedem un tabel plin de câmpuri duplicat, fără o nevoie clară pentru fiecare intrare, atunci acesta este exact ceea ce trebuie 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 prin toate datele. Este nevoie de mult timp și nu doriți întotdeauna să scrieți cod pentru o singură utilizare.


2. O altă soluție este să creați o interogare de selectare care grupează datele 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 scriem setul de date rezultat într-un alt tabel.


3. B deciziile de mai sus suplimentar se aplică codul programului sau mese suplimentare. Cu toate acestea, ar fi mai convenabil să faci totul numai folosind interogări SQL fără mese 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ă setați o condiție care să indice ce date trebuie șterse și care ar trebui lăsate. Trebuie să eliminăm toate intrările care nu sunt unice. Acestea. dacă există mai multe înregistrări identice (sunt aceleași dacă au valori egale 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 un cod diferit (id).

Șir de interogare SQL:

DELETE a.* FROM mytable a,

indică faptul că ștergerea va fi efectuată 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 – formează coloana mid (abrevierea 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. Una generală care conține toate înregistrările. Liniile suplimentare vor fi eliminate din acesta. Al doilea conține informații despre rândurile care trebuie salvate.

Tot ce rămâne este să creați o condiție care să precizeze: 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 î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, este de remarcat faptul că operația descrisă poate fi efectuată dacă există un câmp cheie în tabel. Dacă deodată întâlnești o masă fără identificator unic, apoi adăugați-l:

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

După ce am executat o astfel de interogare, obținem o coloană suplimentară plină cu unice valori numerice pentru fiecare rând al tabelului.

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