(25-07-2009)

U prethodnom članku smo se osvrnuli na rješavanje problema dupliciranja uzrokovanog nedostatkom primarnog ključa. Razmotrimo sada teži slučaj, kada se čini da ključ postoji, ali je sintetički, što, ako je pogrešno dizajnirano, također može dovesti do duplikata s točke gledišta predmetno područje.

Čudno, ali kada na predavanjima govorim o nedostacima sintetičkih ključeva, svejedno stalno nailazim na činjenicu da ih studenti uvijek koriste u svojim prvim projektima baza podataka. Očigledno čovjek ima genetsku potrebu sve prenumerirati, a tu može pomoći samo psihoterapeut. :-)

Dakle, recimo da imamo stol sa Osnovni ključ id i stupac imena koji, u skladu s ograničenjima domene, mora sadržavati jedinstvene vrijednosti. Međutim, ako strukturu tablice definirate na sljedeći način

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

tada ništa ne sprječava pojavu duplikata. Trebalo se koristiti sljedeća struktura stolovi:

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

Svatko zna što treba učiniti ispravno, ali često se morate nositi s "naslijeđenom" strukturom i podacima koji krše ograničenja domene. Evo primjera:

ID ime 1 Ivan 2 Smith 3 John 4 Smith 5 Smith 6 Tom

Možete pitati: "Kako se ovaj problem razlikuje od prethodnog? Uostalom, postoji još jednostavnije rješenje - jednostavno uklonite sve retke iz svake grupe s istim vrijednostima u stupcu naziva, ostavljajući samo redak s minimalnim /maksimalna vrijednost ID-a. Na primjer, ovako:"

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

Tako je, ali nisam ti još sve rekao. :-) Zamislite da imamo podređenu tablicu T_details povezanu s tablicom T_pk putem strani kljuc:

CREATE TABLE T_details (id_pk INT FOREIGN KEY REFERENCES T_pk ON DELETE CASCADE , boja VARCHAR (10), PRIMARY KEY (id_pk, boja);

Ova tablica može sadržavati sljedeće podatke:

id_pk boja 1 plava 1 crvena 2 zelena 2 crvena 3 crvena 4 plava 6 crvena

Za veću jasnoću poslužimo se upitom

SELECT id, name, color FROM T_pk JOIN T_details ON id= id_pk;

za vidjeti imena:

id ime boja 1 John plava 1 John crvena 2 Smith zelena 2 Smith crvena 3 John crvena 4 Smith plava 6 Tom crvena

Tako se ispostavlja da su podaci koji se zapravo odnose na jednu osobu greškom dodijeljeni različitima evidencija roditelja. Osim toga, bilo je duplikata u ovoj tablici:

1 Ivan crvena 3 Ivana crvena

Očito će takvi podaci dovesti do pogrešnih analiza i izvješća. Štoviše, kaskadno brisanje rezultirat će gubitkom podataka. Na primjer, ako ostavimo samo retke s minimalnim ID-om u svakoj grupi u tablici T_pk, izgubit ćemo red

4 Smith plava

u tablici T_details. Stoga moramo uzeti u obzir obje tablice prilikom uklanjanja duplikata.

Postupak "čišćenja" podataka može se provesti u dvije faze:

1. Ažurirajte tablicu T_details dodjeljivanjem podataka koji se odnose na jedno ime ID-u s minimalnim brojem u grupi.
2. Uklonite duplikate iz T_pk tablice, ostavljajući samo retke s minimalnim ID-om u svakoj grupi sa ista vrijednost u koloni imena.

Ažuriranje tablice T_details

SELECT id_pk, name, color, RANK () OVER (PARTICION BY name, color ORDER BY name, color, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id FROM T_pk X JOIN T_details UKLJUČENO id=id_pk;

detektira prisutnost duplikata (duplicirana vrijednost > 1) i minimalna vrijednost id u grupi identičnih imena (min_id). Ovo je rezultat pokretanja ovog upita:

id_pk naziv boja dup min_id 1 John plava 1 1 1 John crvena 1 1 3 John crvena 2 1 4 Smith plava 1 2 2 Smith zelena 1 2 2 Smith crvena 1 2 6 Tom crvena 1 6

Sada trebamo zamijeniti vrijednost id_pk s vrijednošću min_pk za sve retke osim za treći, jer ovaj redak je duplikat drugog retka, kao što je naznačeno vrijednošću dup=2. Zahtjev za ažuriranje može se napisati ovako:

AŽURIRAJ T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, name, color , RANK () OVER (PARTICION BY name, color ORDER BY name, color, 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) Y ON Y.id_pk=T_d.id_pk WHERE dup =1 ;

Kada se pojavi zadatak optimizacije baze podataka ili se promijeni njezina struktura, ponekad se pojavi povezan zadatak organiziranja već prikupljenih podataka. Dobro je ako je tablica već u razvoju u normalnom obliku, a cijeli sustav je organiziran tako da ne gomila nepotrebne duplikate informacija. Ako to nije slučaj, onda se pri finalizaciji takvog sustava želite riješiti svih suvišnih podataka i sve učiniti najkvalitetnije.

U ovom ćemo članku razmotriti zadatak uklanjanja duplih redaka u tablici baze podataka. Htio bih odmah istaknuti da govorimo o o potrebi uklanjanja dvostrukih redaka. Na primjer, zapisi u tablici narudžbi s poljima “šifra narudžbe”, “šifra proizvoda”, “šifra kupca”, “datum narudžbe” mogu se razlikovati samo po šifri narudžbe, budući da jedan kupac može naručiti isti proizvod nekoliko puta na isti dan jednom. A glavni pokazatelj da je sve ispravno ovdje je prisutnost ključnog polja.

Ako vidimo tablicu punu dvostrukih polja, bez jasne potrebe za svakim unosom, onda je to upravo ono što treba popraviti.

Primjer jasno redundantne tablice:

Sada pogledajmo kako možemo riješiti ovaj problem. Ovdje se može koristiti nekoliko metoda.

1. Možete napisati funkciju za usporedbu i ponavljanje kroz sve podatke. Potrebno je puno vremena i ne želite uvijek pisati kod za jednokratnu upotrebu.

2. Drugo rješenje je stvoriti upit za odabir koji grupira podatke tako da se vraćaju samo jedinstveni redovi:

SELECT country_id, city_name
IZ mytable
GRUPIRAJ PO country_id, city_name

Dobijamo sljedeći uzorak:

Zatim zapisujemo dobiveni skup podataka u drugu tablicu.

3. B gore navedene odluke dodatno se primjenjuje programski kod ili dodatne tablice. Međutim, bilo bi prikladnije učiniti sve samo pomoću SQL upiti bez dodatne tablice. Evo primjera takvog rješenja:

DELETE a.* FROM mytable a,
(IZABERI

IZ moje tablice b

) c
GDJE
a.country_id = c.country_id
I a.ime_grada = c.ime_grada
I a.id > c.mid

Nakon izvršenja takvog upita u tablici će ostati samo jedinstveni zapisi:

Sada pogledajmo pobliže kako sve to funkcionira. Prilikom podnošenja zahtjeva za brisanje morate postaviti uvjet koji će označavati koje podatke treba izbrisati, a koje ostaviti. Moramo ukloniti sve nejedinstvene unose. Oni. ako postoji nekoliko identičnih zapisa (isti su ako imaju jednake vrijednosti country_id i city_name), tada morate uzeti jedan od redaka, zapamtiti njegov kod i izbrisati sve zapise s istim vrijednostima country_id i city_name, ali različitim kodom (iskaznica).

Niz SQL upita:

DELETE a.* FROM mytable a,

označava da će se brisanje izvršiti iz tablice mytable.

Upit odabira zatim generira pomoćnu tablicu u kojoj grupiramo zapise tako da su svi zapisi jedinstveni:

(IZABERI
b.country_id, b.city_name, MIN(b.id) mid
IZ moje tablice b
GROUP BY b.country_id, b.city_name
) c

MIN(b.id) mid – formira stupac mid (skraćenica min id), koji sadrži minimalnu vrijednost id-a u svakoj podskupini.

Rezultat je tablica koja sadrži jedinstvene zapise i ID prvog retka za svaku grupu dvostrukih zapisa.

Sada imamo dva stola. Jedan opći koji sadrži sve zapise. Iz njega će biti uklonjeni dodatni redovi. Drugi sadrži informacije o redovima koje je potrebno spremiti.

Sve što preostaje je stvoriti uvjet koji glasi: trebate izbrisati sve retke u kojima se podudaraju polja country_id i city_name, ali se ID neće podudarati. U u ovom slučaju odabire se minimalna vrijednost id-a, pa se brišu svi zapisi čiji je id veći od onog odabranog u privremenoj tablici.

Također je vrijedno napomenuti da se opisana operacija može izvesti ako u tablici postoji ključno polje. Ako iznenada naiđete na tablicu bez jedinstvenog identifikatora, jednostavno je dodajte:

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

Nakon što smo izvršili takav upit, dobivamo dodatni stupac ispunjen jedinstvenim brojčane vrijednosti za svaki red tablice.

Mi radimo sve potrebne radnje. Nakon dovršetka operacije čišćenja tablice od duplih zapisa, ovo se polje također može izbrisati.

Uklanjanje ponavljanja

Izvor baze podataka

Potreba za uklanjanjem duplikata podataka je uobičajena, posebno kada se rješavaju problemi s kvalitetom podataka u okruženjima u kojima je došlo do dupliciranja zbog nedostatka ograničenja za osiguranje jedinstvenosti podataka. Da bismo to demonstrirali, upotrijebimo sljedeći kod za pripremu primjera podataka s dvostrukim narudžbama u tablici pod nazivom MyOrders:

IF OBJECT_ID("Sales.MyOrders") NIJE NULL DROP TABLE Sales.MyOrders; GO SELECT * INTO Sales.MyOrders FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders UNION ALL SELECT * FROM Sales.Orders;

Zamislite da trebate eliminirati duplikate podataka, ostavljajući samo jednu instancu svakog s jedinstvenom vrijednosti orderid. Dvostruki brojevi označavaju se funkcijom ROW_NUMBER, particioniranjem prema navodno jedinstvenoj vrijednosti (orderid u našem slučaju) i korištenjem slučajnog redoslijeda ako vam je svejedno koji red zadržati, a koji ukloniti. Evo koda gdje funkcija ROW_NUMBER označava duplikate:

SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

Zatim morate razmotriti različite varijante ovisno o broju redaka koje je potrebno izbrisati, postotku veličine tablice, koliki je taj broj, aktivnosti proizvodnog okruženja i drugim okolnostima. Na mali broj Za izbrisane retke obično je dovoljno koristiti operaciju brisanja s punim zapisom, koja briše sve instance s brojem retka većim od jedan:

Ali ako je broj redaka koji se brišu velik - posebno kada čini veliki udio redaka tablice - brisanje s puna snimka operacije dnevnika bit će prespore. U ovom slučaju, možda biste trebali razmotriti korištenje skupne operacije zapisivanja kao što je SELECT INTO za kopiranje jedinstvenih redaka (označenih brojem 1) u drugu tablicu. Nakon toga se izvorna tablica briše novi stol dodjeljuje se naziv udaljene tablice, ponovno stvaraju ograničenja, indeksi i okidači. Evo koda za dovršeno rješenje:

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, shipperid, 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"; -- ponovno stvaranje indeksa, ograničenja i okidača

Radi jednostavnosti, ovdje nisam dodao nikakvu kontrolu transakcija, ali uvijek morate imati na umu da više korisnika može raditi s podacima u isto vrijeme. Prilikom implementacije ove metode u proizvodnom okruženju, morate slijediti sljedeći redoslijed:

Otvorena transakcija.

Nabavite bravu za stol.

Izvršiti SELECT izjava U.

Brisanje i preimenovanje objekata.

Ponovno kreirajte indekse, ograničenja i okidače.

Potvrdite transakciju.

Postoji još jedna opcija - filtrirati samo jedinstvene ili samo nejedinstvene retke. I ROW_NUMBER i RANK izračunavaju se na temelju orderid-a, otprilike ovako:

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

Primijetite da u rezultatima samo jedan redak za svaku jedinstvenu vrijednost u orderid odgovara broju retka i rangu retka. Na primjer, ako trebate ukloniti mali dio podataka, prethodni upit možete enkapsulirati u CTE definiciju, au vanjskom upitu izdati naredbu za brisanje redaka koji imaju drugačiji broj linije i rang.