Korduste eemaldamine

Andmebaasi allikas

Andmete dubleerimise vajadus on tavaline, eriti kui käsitletakse andmekvaliteedi probleeme keskkondades, kus dubleerimine on tekkinud andmete unikaalsust tagavate piirangute puudumise tõttu. Demonstreerimiseks kasutame järgmist koodi, et koostada tabelis nimega MyOrders duplikaattellimustega andmete näide:

KUI OBJECT_ID("Müük.Minutellimused") EI OLE NULL DROPP TABLE Sales.MyOrders; MINGE VALI * SISSE Müük.Minutellimused müügist.tellimused LIIT KÕIK VALI * MÜÜK.Tellimused LIIT KÕIK VALI * Müük.Tellimused;

Kujutage ette, et peate eemaldama dubleerivad andmed, jättes ainult ühe eksemplari iga kordumatu järjestusväärtusega. Dubleerivad numbrid märgitakse funktsiooni ROW_NUMBER abil, jagades need väidetavalt kordumatu väärtusega (meie puhul järjekord) ja kasutades juhuslikku järjestust, kui te ei hooli, millist rida säilitada ja milline eemaldada. Siin on kood, kus funktsioon ROW_NUMBER märgib duplikaate:

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

Siis peate kaaluma erinevad variandid olenevalt kustutamist vajavate ridade arvust, tabeli suuruse protsendist, selle arvust, tootmiskeskkonna tegevusest ja muudest asjaoludest. Kell väike arv Kustutatud ridade puhul piisab tavaliselt täieliku logimise kustutamise operatsioonist, mis kustutab kõik eksemplarid, mille reanumber on suurem kui üks:

Kui aga kustutatavate ridade arv on suur – eriti kui see moodustab suure osa tabeli ridadest –, kustutatakse täielik salvestus logitoimingud on liiga aeglased. Sel juhul võiksite kaaluda hulgilogimise toimingu (nt SELECT INTO) kasutamist unikaalsete ridade (numbriga 1) kopeerimiseks teise tabelisse. Pärast seda algne tabel kustutatakse uus laud kaugtabeli nimi määratakse, piirangud, indeksid ja päästikud luuakse uuesti. Siin on valminud lahenduse kood:

WITH C AS (SELECT *, ROW_NUMBER() OVER(PARTITTION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT orderid, custid, empid, orderdate, vajalik kuupäev, tarnekuupäev, tarneaeg, kaubaveo, tarnenimi, laeva aadress, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; Drop TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- indeksite, piirangute ja käivitajate taasloomine

Lihtsuse huvides ei ole ma siia lisanud tehingute kontrolli, kuid peate alati meeles pidama, et andmetega saavad korraga töötada mitu kasutajat. Selle meetodi rakendamisel tootmiskeskkonnas peate järgima järgmist järjestust:

Ava tehing.

Hankige laualukk.

Käivitage SELECT avaldus INTO.

Objektide kustutamine ja ümbernimetamine.

Luua uuesti indeksid, piirangud ja päästikud.

Sooritage tehing.

On veel üks võimalus – filtreerida ainult kordumatud või mitteunikaalsed read. Nii ROW_NUMBER kui ka RANK arvutatakse järjekorra alusel, umbes nii:

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

Pange tähele, et tulemustes vastab ainult üks rida iga kordumatu väärtuse kohta järjekorras rea numbrile ja rea ​​järjestusele. Näiteks kui teil on vaja eemaldada väike osa andmeid, saate kapseldada eelmise päringu CTE definitsiooni ja anda välimises päringus käsu kustutada ridu, millel on erinev number read ja auaste.

Kui tekib andmebaasi optimeerimise ülesanne või selle struktuur muutub, tekib mõnikord sellega seotud ülesanne juba kogutud andmete korrastamine. Hea, kui tabel on arenduse käigus juba normaalsel kujul ja kogu süsteem on korraldatud nii, et sinna ei koguneks tarbetut dubleerivat infot. Kui see nii ei ole, siis sellise süsteemi viimistlemisel soovite vabaneda kõigist üleliigsetest andmetest ja teha kõike kõrgeima kvaliteediga.

Selles artiklis käsitleme andmebaasi tabelist dubleerivate ridade eemaldamise ülesannet. Sellele tahaksin kohe tähelepanu juhtida me räägime dubleerivate ridade eemaldamise vajaduse kohta. Näiteks tellimuste tabeli kirjed väljadega „tellimuse kood“, „tootekood“, „kliendi kood“, „tellimuse kuupäev“ võivad erineda ainult tellimuse koodi poolest, kuna üks klient saab tellida sama toodet mitu korda üks kord samal päeval. Ja siin on peamine näitaja, et kõik on õige, võtmevälja olemasolu.

Kui näeme tabelit, mis on täis dubleerivaid välju, mille iga kirje jaoks pole selget vajadust, siis tuleb just see parandada.

Näide selgelt üleliigsest tabelist:

Nüüd vaatame, kuidas saame selle probleemi lahendada. Siin saab kasutada mitmeid meetodeid.

1. Saate kirjutada funktsiooni kõigi andmete võrdlemiseks ja itereerimiseks. See võtab kaua aega ja te ei soovi alati kirjutada koodi ühekordseks kasutamiseks.

2. Teine lahendus on luua valikupäring, mis rühmitab andmed nii, et tagastatakse ainult kordumatud read:

VALI riigi_id, linna_nimi
Mytable'ist
GROUP BY riigi_id, linna_nimi

Saame järgmise näidise:

Seejärel kirjutame saadud andmestiku teise tabelisse.

3. B ülaltoodud otsused kehtib täiendav programmi kood või lisatabeleid. Mugavam oleks aga kõike teha ainult kasutades SQL päringud ilma lisatabeleid. Ja siin on näide sellisest lahendusest:

KUSTUTA a.* Mytableist a,
(VALI

Mytablest b

) c
KUS
a.riigi_id = c.riigi_id
JA a.linna_nimi = c.linna_nimi
JA a.id > c.mid

Pärast sellise päringu täitmist jäävad tabelisse ainult kordumatud kirjed:

Nüüd vaatame lähemalt, kuidas see kõik töötab. Kustutamist taotledes tuleb seada tingimus, mis näitab, millised andmed tuleb kustutada ja millised jätta. Peame eemaldama kõik mitteunikaalsed kirjed. Need. kui on mitu identset kirjet (need on samad, kui neil on võrdsed riigi_id ja linna_nimi väärtused), siis peate võtma ühe ridadest, meeles pidama selle koodi ja kustutama kõik kirjed, millel on samad riigi_id ja linna_nimi väärtused, kuid erinev kood (id).

SQL päringustring:

KUSTUTA a.* Mytableist a,

näitab, et kustutamine toimub müütabeli tabelist.

Valimispäring genereerib seejärel abitabeli, kuhu rühmitame kirjed nii, et kõik kirjed oleksid kordumatud:

(VALI
b.riigi_id, b.linna_nimi, MIN(b.id) kesk
Mytablest b
GROUP BY BY b.country_id, b.city_name
) c

MIN(b.id) mid – moodustab veeru mid (lühend min id), millesse minimaalne väärtus id, igas alarühmas.

Tulemuseks on tabel, mis sisaldab kordumatuid kirjeid ja iga topeltkirjete rühma esimese rea ID-d.

Nüüd on meil kaks lauda. Üks üldine, mis sisaldab kõiki kirjeid. Sellest eemaldatakse lisaread. Teine sisaldab teavet salvestamist vajavate ridade kohta.

Jääb üle vaid luua tingimus, mis ütleb: tuleb kustutada kõik read, kus riigi_id ja linna_nimi väljad kattuvad, kuid id ei ühti. IN sel juhul valitakse minimaalne id väärtus, seega kustutatakse kõik kirjed, mille id on suurem kui ajutises tabelis valitud.

Samuti väärib märkimist, et kirjeldatud toimingut saab teha, kui tabelis on võtmeväli. Kui satute ootamatult unikaalse identifikaatorita tabelini, lisage see lihtsalt:

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

Pärast sellise päringu täitmist saame täiendava veeru, mis on täidetud ainulaadsega arvväärtusi iga tabeli rea kohta.

Me teeme kõike vajalikud toimingud. Pärast duplikaatkirjete tabeli kustutamise toimingu lõpetamist saab selle välja ka kustutada.

(25-07-2009)

Eelmises artiklis vaatlesime puuduvast primaarvõtmest põhjustatud duplikaatprobleemi lahendamist. Vaatleme nüüd keerulisemat juhtumit, kus võti näib olevat olemas, kuid see on sünteetiline, mis võib vale disaini korral viia ka vaatenurgast dubleerimiseni. ainevaldkond.

Kummaline, aga rääkides loengutes sünteetiliste võtmete puudustest, puutun sellegipoolest pidevalt kokku tõsiasjaga, et tudengid kasutavad neid alati oma esimestes andmebaasiprojektides. Ilmselt on inimesel geneetiline vajadus kõik ümber nummerdada ja siin saab aidata vaid psühhoterapeut. :-)

Ütleme nii, et meil on laud esmane võti id ja nime veerg, mis vastavalt domeenipiirangutele peavad sisaldama kordumatuid väärtusi. Kui aga määratlete tabeli struktuuri järgmiselt

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

siis ei takista miski duplikaatide tekkimist. Oleks pidanud kasutama järgmine struktuur tabelid:

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

Kõik teavad, mida õigesti teha, kuid sageli peate tegelema pärandstruktuuri ja andmetega, mis rikuvad domeenipiiranguid. Siin on näide:

id nimi 1 Johannes 2 Sepp 3 Johannes 4 Sepp 5 Sepp 6 Tom

Võite küsida: "Kuidas see probleem erineb eelmisest, on siin veelgi lihtsam lahendus - lihtsalt eemaldage nimeveerust kõik samade väärtustega read, jättes alles ainult see rida? minimaalne/maksimaalne ID väärtus, näiteks nii:"

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

See on õige, aga ma pole sulle veel kõike rääkinud. :-) Kujutage ette, et meil on alamtabel T_details, mis on lingitud tabeliga T_pk by võõrvõti:

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

See tabel võib sisaldada järgmisi andmeid:

id_pk värv 1 sinine 1 punane 2 roheline 2 punane 3 punane 4 sinine 6 punane

Suurema selguse huvides kasutame päringut

SELECT id, nimi, värv FROM T_pk JOIN T_details ON id= id_pk;

nimede nägemiseks:

id nime värv 1 Jaani sinine 1 Jaani punane 2 Sepp roheline 2 Sepp punane 3 Jaani punane 4 Sepp sinine 6 Toom punane

Seega selgub, et andmed, mis tegelikult on seotud ühe isikuga, eraldati ekslikult erinevatele vanemate kirjed. Lisaks oli selles tabelis duplikaate:

1 Jaanipunane 3 Jaanipunane

Ilmselgelt toovad sellised andmed kaasa ekslikud analüüsid ja aruanded. Enamgi veel, kaskaadi kustutamine toob kaasa andmete kadumise. Näiteks kui jätame tabelisse T_pk igasse rühma ainult minimaalse ID-ga read, kaotame rea

4 Smith sinine

tabelis T_details. Seetõttu peame duplikaatide kõrvaldamisel arvesse võtma mõlemat tabelit.

Andmete puhastamise protseduuri saab läbi viia kahes etapis:

1. Värskendage tabelit T_details, määrates rühmas minimaalse arvuga ID-le ühe nimega seotud andmed.
2. Eemaldage tabelist T_pk duplikaadid, jättes igas rühmas ainult minimaalse ID-ga read sama väärtus nime veerus.

Tabeli T_details värskendamine

SELECT id_pk, nimi, värv , RANK () ÜLE (PARTITION BY nime, värv ORDER BY nime, värv, 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;

määrab duplikaatide olemasolu (dup väärtus > 1) ja minimaalse id väärtuse identsete nimede rühmas (min_id). Siin on selle päringu käitamise tulemus:

id_pk nimi color dup min_id 1 Jaani sinine 1 1 1 Jaani punane 1 1 3 Jaani punane 2 1 4 Sepp sinine 1 2 2 Sepp roheline 1 2 2 Sepp punane 1 2 6 Tom punane 1 6

Nüüd peame asendama id_pk väärtuse väärtusega min_pk kõigi ridade jaoks, välja arvatud kolmas, sest see rida on teise rea duplikaat, mida näitab väärtus dup=2. Värskendustaotluse saab kirjutada järgmiselt:

UPDATE T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, name, color , RANK () OVER (PARTITION BY BY, color ORDER BY BY, color, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE. = X.nimi) 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 ;