(25-07-2009)

Edellisessä artikkelissa tarkastelimme puuttuvan ensisijaisen avaimen aiheuttaman kaksoisongelman ratkaisemista. Tarkastellaanpa nyt vaikeampaa tapausta, jolloin avain näyttää olevan siellä, mutta se on synteettistä, mikä voi väärin suunniteltuna johtaa myös kaksoiskappaleisiin. aihealue.

Outoa, mutta kun puhun luennoilla synteettisten avainten puutteista, törmään kuitenkin jatkuvasti siihen, että opiskelijat käyttävät niitä poikkeuksetta ensimmäisissä tietokantaprojekteissaan. Ilmeisesti ihmisellä on geneettinen tarve numeroida kaikki uudelleen, ja vain psykoterapeutti voi auttaa tässä. :-)

Oletetaan, että meillä on pöytä pääavain id ja nimisarake, joiden tulee verkkotunnuksen rajoitusten mukaisesti sisältää yksilöllisiä arvoja. Jos kuitenkin määrität taulukon rakenteen seuraavasti

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

silloin mikään ei estä kaksoiskappaleiden ilmestymistä. Olisi pitänyt käyttää seuraavaa rakennetta taulukot:

LUO TAULUKKO T_pk (id INT IDENTITY PRIMARY KEY, nimi VARCHAR (50) UNIQUE);

Kaikki tietävät oikein, mutta usein joudut käsittelemään "vanhaa" rakennetta ja dataa, joka rikkoo verkkotunnuksen rajoituksia. Tässä on esimerkki:

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

Saatat kysyä: "Miten tämä ongelma eroaa edellisestä, tässä on vielä yksinkertaisempi ratkaisu - poista yksinkertaisesti kaikki rivit jokaisesta ryhmästä, joilla on samat arvot nimisarakkeessa, jättäen vain rivin, jossa on minimi/maksimi id-arvo esimerkiksi näin:"

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

Se on totta, mutta en ole vielä kertonut kaikkea. :-) Kuvittele, että meillä on alitaulukko T_details, joka on linkitetty taulukkoon T_pk by vieras avain:

LUO TAULUKKO T_details (id_pk INT ULKOMAAN AVAIN VIITTEET T_pk POISTA KASSADISTA , väri VARCHAR (10), PRIMARY KEY (id_pk, väri);

Tämä taulukko voi sisältää seuraavat tiedot:

id_pk väri 1 sininen 1 punainen 2 vihreä 2 punainen 3 punainen 4 sininen 6 punainen

Selvyyden vuoksi käytetään kyselyä

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

nähdäksesi nimet:

id-nimen väri 1 John sininen 1 John punainen 2 Smithin vihreä 2 Smithin punainen 3 John punainen 4 Smith sininen 6 Tom punainen

Siten käy ilmi, että tiedot, jotka todella liittyvät yhteen henkilöön, on erehdyksessä kohdistettu eri henkilöille vanhempien tietueita. Lisäksi tässä taulukossa oli kaksoiskappaleita:

1 Johanneksen punainen 3 Johanneksen punainen

On selvää, että tällaiset tiedot johtavat virheellisiin analyyseihin ja raportteihin. Lisäksi, kaskadin poistaminen johtaa tietojen menetykseen. Jos esimerkiksi jätämme T_pk-taulukon jokaiseen ryhmään vain rivit, joilla on minimitunnus, menetämme rivin.

4 Smithinsininen

T_details -taulukossa. Siksi meidän on otettava molemmat taulukot huomioon kaksoiskappaleiden poistamisessa.

Tietojen "puhdistus" voidaan suorittaa kahdessa vaiheessa:

1. Päivitä T_details-taulukko määrittämällä yhteen nimeen liittyvät tiedot tunnukselle, jonka vähimmäisnumero on ryhmässä.
2. Poista kaksoiskappaleet T_pk-taulukosta jättäen jokaiseen ryhmään vain rivit, joilla on vähimmäistunnus sama arvo nimisarakkeessa.

Päivitetään T_details-taulukkoa

SELECT id_pk, nimi, väri , RANK () YLI (OSIO nimen mukaan, väri ORDER BY nimen mukaan, väri, id_pk) dup ,(SELECT MIN (id) FROM T_pk WHERE T_pk.name = X.name) min_id FROM T_pk X JOIN T_details PÄÄLLÄ id=id_pk;

havaitsee kaksoiskappaleiden olemassaolon (dup-arvo > 1) ja minimiarvo id identtisten nimien ryhmässä (min_id). Tässä on tämän kyselyn tulos:

id_pk nimi color dup min_id 1 John sininen 1 1 1 John punainen 1 1 3 John punainen 2 1 4 Smith sininen 1 2 2 Smith vihreä 1 2 2 Smith punainen 1 2 6 Tom punainen 1 6

Nyt meidän on korvattava id_pk-arvo min_pk-arvolla kaikilla paitsi kolmannella rivillä, koska tämä rivi on kopio toisesta rivistä, kuten arvo dup=2 osoittaa. Päivityspyyntö voidaan kirjoittaa näin:

PÄIVITYS T_details SET id_pk=min_id FROM T_details T_d JOIN (SELECT id_pk, nimi, väri , RANK () YLI (OSIO nimen MUKAAN, väri ORDER BY nimen, väri, id_pk) dup ,(VALITSE 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 ;

Kun tietokannan optimointitehtävä tai sen rakenne muuttuu, syntyy joskus siihen liittyvä tehtävä jo kerätyn tiedon järjestäminen. On hyvä, jos taulukko on jo kehitysvaiheessa normaalissa muodossa ja koko järjestelmä on järjestetty niin, ettei siihen kerry turhaa päällekkäistä tietoa. Jos näin ei ole, niin tällaisen järjestelmän viimeistelyssä haluat päästä eroon kaikesta tarpeettomasta tiedosta ja tehdä kaikki mahdollisimman laadukkaasti.

Tässä artikkelissa tarkastelemme tehtävää poistaa päällekkäiset rivit tietokantataulukosta. Haluaisin heti huomauttaa siitä me puhumme päällekkäisten rivien poistamisen tarpeesta. Esimerkiksi tilaustaulukon tietueet kentillä "tilauskoodi", "tuotekoodi", "asiakaskoodi", "tilauspäivämäärä" voivat erota vain tilauskoodista, koska yksi asiakas voi tilata saman tuotteen useita kertoja samana päivänä kerran. Ja tärkein indikaattori tässä siitä, että kaikki on oikein, on avainkentän läsnäolo.

Jos näemme taulukon täynnä päällekkäisiä kenttiä ilman selvää tarvetta jokaiselle merkinnälle, tämä on juuri se, mikä on korjattava.

Esimerkki selkeästi tarpeettomasta taulukosta:

Katsotaan nyt, kuinka voimme ratkaista tämän ongelman. Tässä voidaan soveltaa useita menetelmiä.

1. Voit kirjoittaa funktion vertaillaksesi ja iteroidaksesi kaikkia tietoja. Se kestää kauan, etkä aina halua kirjoittaa koodia kertakäyttöön.

2. Toinen ratkaisu on luoda valintakysely, joka ryhmittelee tiedot siten, että vain yksilölliset rivit palautetaan:

SELECT country_id, city_name
Mytablesta
GROUP BY country_id, city_name

Saamme seuraavan näytteen:

Sitten kirjoitamme tuloksena olevan tietojoukon toiseen taulukkoon.

3. B edellä mainitut päätökset lisää sovelletaan ohjelmakoodi tai lisätaulukoita. Olisi kuitenkin mukavampaa tehdä kaikki vain käyttämällä SQL-kyselyt ilman lisätaulukoita. Ja tässä on esimerkki tällaisesta ratkaisusta:

POISTA a.* Mytablesta a,
(VALITSE

FROM mytable b

) c
MISSÄ
a.country_id = c.country_id
JA a.kaupungin_nimi = c.kaupungin_nimi
JA a.id > c.mid

Tällaisen kyselyn suorittamisen jälkeen taulukkoon jäävät vain yksilölliset tietueet:

Katsotaanpa nyt tarkemmin, miten se kaikki toimii. Poistoa pyytäessäsi tulee asettaa ehto, joka kertoo, mitkä tiedot tulee poistaa ja mitkä jättää. Meidän on poistettava kaikki ei-yksilölliset merkinnät. Nuo. jos on useita identtisiä tietueita (ne ovat samat, jos niillä on samat country_id- ja city_name-arvot), sinun on otettava yksi riveistä, muistettava sen koodi ja poistettava kaikki tietueet, joilla on samat country_id- ja city_name-arvot, mutta eri koodi. (id).

SQL-kyselymerkkijono:

POISTA a.* Mytablesta a,

osoittaa, että poisto suoritetaan mytable-taulukosta.

Valintakysely luo sitten aputaulukon, johon ryhmittelemme tietueet siten, että kaikki tietueet ovat yksilöllisiä:

(VALITSE
b.maan_tunnus, b.kaupungin_nimi, MIN(b.id) mid
FROM mytable b
RYHMÄ MUKAAN b.maan_tunnus, b.kaupungin_nimi
) c

MIN(b.id) mid – muodostaa sarakkeen mid (lyhenne min id), joka sisältää vähimmäistunnuksen arvon kussakin alaryhmässä.

Tuloksena on taulukko, joka sisältää yksilölliset tietueet ja ensimmäisen rivin tunnuksen jokaiselle päällekkäisten tietueiden ryhmälle.

Nyt meillä on kaksi pöytää. Yksi yleinen, joka sisältää kaikki tietueet. Ylimääräiset rivit poistetaan siitä. Toinen sisältää tiedot tallennettavista riveistä.

Jäljelle jää vain ehto, jossa todetaan: sinun on poistettava kaikki rivit, joissa country_id- ja city_name-kentät vastaavat, mutta id ei täsmää. SISÄÄN tässä tapauksessa vähimmäistunnuksen arvo valitaan, joten kaikki tietueet, joiden id on suurempi kuin väliaikaisessa taulukossa valittu, poistetaan.

On myös syytä huomata, että kuvattu toiminto voidaan suorittaa, jos taulukossa on avainkenttä. Jos kohtaat yhtäkkiä taulukon ilman yksilöllistä tunnistetta, lisää se:

ALTER TABLE ` mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , LISÄÄ ENSISIJAINEN AVAIN (`id`)

Kun olet suorittanut tällaisen kyselyn, saamme ylimääräisen sarakkeen, joka on täytetty ainutlaatuisella numeerisia arvoja jokaiselle taulukon riville.

Teemme kaiken Tarvittavat toimet. Kun päällekkäisten tietueiden taulukon tyhjennystoiminto on suoritettu, tämä kenttä voidaan myös poistaa.

Toistojen poistaminen

Tietokannan lähde

Tarve poistaa tietojen kaksoiskappaleet ovat yleisiä, etenkin kun puututaan tietojen laatuongelmiin ympäristöissä, joissa päällekkäisyyksiä on esiintynyt, koska tietojen yksilöllisyyden takaamiseksi ei ole rajoituksia. Havainnollistamiseksi laaditaan seuraava koodi esimerkki tiedoista, joissa on päällekkäisiä tilauksia taulukossa nimeltä MyOrders:

JOS OBJECT_ID("Sales.MyOrders") EI OLE NULL DROP TABLE Sales.MyOrders; SIIRRY VALITSE * SISÄÄN Myyntiin.Omat tilaukset Myynnistä.Tilaukset UNIONIN KAIKKI VALITSE * Alkaen Myynti.Tilaukset UNIONIN KAIKKI VALITSE * Myynti.Tilaukset;

Kuvittele, että sinun on poistettava päällekkäiset tiedot ja jätettävä vain yksi esiintymä jokaisesta yksilöllisen järjestysarvon kanssa. Päällekkäiset numerot merkitään ROW_NUMBER-funktiolla, osioiden oletettavasti ainutlaatuisella arvolla (tapauksessamme orderid) ja satunnaisella järjestyksellä, jos et välitä, mikä rivi säilytetään ja mikä poistetaan. Tässä on koodi, jossa ROW_NUMBER-funktio merkitsee kaksoiskappaleita:

SELECT tilaustunnus, ROW_NUMBER() YLI(OSIO järjestyksen mukaan ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;

Sitten pitää harkita erilaisia ​​muunnelmia riippuen poistettavien rivien määrästä, taulukon koon prosenteista, määrästä, tuotantoympäristön toiminnasta ja muista olosuhteista. klo pieni määrä Poistetuille riveille riittää yleensä täyden lokikirjauksen poistotoiminto, joka poistaa kaikki esiintymät, joiden rivinumero on suurempi kuin yksi:

Mutta jos poistettavien rivien määrä on suuri - varsinkin kun se muodostaa suuren osan taulukon riveistä - poistamalla täysi tallennus lokitoiminnot ovat liian hitaita. Tässä tapauksessa sinun kannattaa harkita joukkolokitoimintoa, kuten SELECT INTO, kopioidaksesi yksilölliset rivit (numeroitu 1) toiseen taulukkoon. Tämän jälkeen alkuperäinen taulukko poistetaan uusi pöytä etätaulukon nimi määritetään, rajoitukset, indeksit ja liipaisimet luodaan uudelleen. Tässä on valmiin ratkaisun koodi:

WITH C AS (VALITSE *, ROW_NUMBER() YLI(OSIO TOIMITUSJÄRJESTÄ (SELECT NULL)) AS n FROM Sales.MyOrders) SELECT orderid, custid, empid, tilauspäivämäärä, vaadittu päivämäärä, lähetyspäivämäärä, lähetyspäivä, rahti, toimitusnimi, toimitusosoite, shipcity, shipregion, shippostalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "Omat tilaukset"; - Indeksien, rajoitusten ja laukaisimien luominen uudelleen

Yksinkertaisuuden vuoksi en ole lisännyt tähän tapahtumanhallintaa, mutta sinun tulee aina muistaa, että useat käyttäjät voivat käsitellä tietoja samanaikaisesti. Kun otat tämän menetelmän käyttöön tuotantoympäristössä, sinun on noudatettava seuraavaa järjestystä:

Avaa kauppa.

Hanki pöydän lukko.

Suorittaa SELECT-lause INTO.

Poista ja nimeä uudelleen kohteita.

Luo uudelleen indeksejä, rajoituksia ja laukaisimia.

Sitouta kauppa.

On myös toinen vaihtoehto - suodattaa vain yksilölliset tai vain ei-yksilölliset rivit. Sekä ROW_NUMBER että RANK lasketaan järjestyksen perusteella, jotenkin näin:

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

Huomaa, että tuloksissa vain yksi rivi kullekin järjestysnumeron yksilölliselle arvolle vastaa rivin numeroa ja rivin sijoitusta. Esimerkiksi, jos sinun on poistettava pieni osa dataa, voit kapseloida edellisen kyselyn CTE-määritelmään ja antaa ulompaan kyselyyn käskyn poistaa rivit, joilla on eri numero rivit ja arvosanat.