När uppgiften att optimera en databas uppstår eller dess struktur ändras, uppstår ibland en relaterad uppgift att organisera den redan ackumulerade datan. Det är bra om tabellen redan är given i utvecklingen normal form, och hela systemet är organiserat på ett sådant sätt att det inte ackumulerar onödig duplicerad information. Om så inte är fallet, när du slutför ett sådant system, vill du bli av med all redundant data och göra allt med högsta kvalitet.
I den här artikeln kommer vi att överväga uppgiften att ta bort dubbletter av rader i en databastabell. Det skulle jag genast vilja påpeka vi pratar om om behovet av att ta bort dubbletter av linjer. Till exempel kan poster i beställningstabellen med fälten "orderkod", "produktkod", "kundkod", "beställningsdatum" endast skilja sig åt i beställningskoden, eftersom en kund kan beställa samma produkt flera gånger på samma dag en gång. Och huvudindikatorn här på att allt är korrekt är närvaron nyckelfält.
Om vi ser en tabell full av dubbletter av fält, utan något tydligt behov för varje post, så är det precis detta som behöver fixas.
Ett exempel på en klart överflödig tabell:
Låt oss nu titta på hur vi kan lösa detta problem. Flera metoder kan användas här.
1. Du kan skriva en funktion för att jämföra och iterera genom all data. Det är lång tid, och att skriva kod för disponibel Jag vill inte alltid.
2. En annan lösning är att skapa en urvalsfråga som grupperar data så att endast unika rader returneras:
SELECT country_id, city_name
FRÅN mytable
GROUP BY country_id, city_name
Vi får följande exempel:
Sedan skriver vi den resulterande datamängden i en annan tabell.
3. B ovanstående beslut tillägg gäller programkod eller ytterligare bord. Det skulle dock vara bekvämare att göra allt med enbart SQL-frågor utan ytterligare tabeller. Och här är ett exempel på en sådan lösning:
DELETE a.* FRÅN mytable a,
(VÄLJ
FRÅN mytable b
) c
VAR
a.country_id = c.country_id
OCH a.city_name = c.city_name
OCH a.id > c.mid
Endast efter att ha kört en sådan fråga unika rekord:
Låt oss nu titta närmare på hur det hela fungerar. När du begär radering måste du ställa in ett villkor som anger vilka uppgifter som ska raderas och vilka som ska lämnas. Vi måste ta bort alla icke-unika poster. De där. om det är flera identiska register(de är samma om de har lika värden country_id och city_name), då måste du ta en av raderna, komma ihåg dess kod och ta bort alla poster med samma country_id och city_name värden, men en annan kod (id).
SQL-frågesträng:
DELETE a.* FRÅN mytable a,
indikerar att raderingen kommer att utföras från mytable-tabellen.
Select-frågan genererar sedan en hjälptabell där vi grupperar posterna så att alla poster är unika:
(VÄLJ
b.country_id, b.city_name, MIN(b.id) mid
FRÅN mytable b
GROUP BY b.country_id, b.city_name
) c
MIN(b.id) mid – bildar kolumnen mid (förkortning min id), som innehåller det lägsta id-värdet i varje undergrupp.
Resultatet är en tabell som innehåller unika poster och första rad-id för varje grupp av dubbletter av poster.
Nu har vi två bord. En allmän som innehåller alla poster. Extra rader kommer att tas bort från den. Den andra innehåller information om de rader som behöver sparas.
Allt som återstår är att skapa ett villkor som säger: du måste ta bort alla rader där fälten country_id och city_name matchar, men id:t kommer inte att matcha. I I detta fall det lägsta id-värdet är valt, så alla poster vars id är större än det som valts i den temporära tabellen raderas.
Det är också värt att notera att den beskrivna operationen kan utföras om det finns ett nyckelfält i tabellen. Om du plötsligt stöter på en tabell utan en unik identifierare, lägg bara till den:
ALTER TABLE ` mytable` ADD `id` INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`)
Efter att ha utfört en sådan fråga får vi ytterligare en kolumn fylld med unika numeriska värden för varje rad i tabellen.
Vi gör allt nödvändiga åtgärder. Efter att åtgärden för att rensa tabellen med dubbletter av poster är klar, kan detta fält också raderas.
(25-07-2009)
I den tidigare artikeln tittade vi på att lösa dubblettproblemet som orsakas av en saknad primärnyckel. Låt oss nu överväga mer Svårt fall när det verkar finnas en nyckel, men den är syntetisk, vilket, om den inte är korrekt utformad, också kan leda till dubbletter ur synvinkel ämnesområde.
Det är konstigt, men när jag pratar på föreläsningar om bristerna med syntetiska nycklar, stöter jag ändå på att studenter alltid använder dem i sina första databasprojekt. Tydligen har en person ett genetiskt behov av att numrera om allt, och bara en psykoterapeut kan hjälpa till här. :-)
Så låt oss säga att vi har ett bord med primärnyckel id och en namnkolumn, som i enlighet med domänrestriktioner måste innehålla unika värden. Men om du definierar tabellstrukturen enligt följande
CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY , namn VARCHAR (50 ));då inget hindrar uppkomsten av dubbletter. Följande tabellstruktur bör användas:
CREATE TABLE T_pk (id INT IDENTITY PRIMARY KEY, namn VARCHAR (50) UNIQUE);Alla vet vad som är rätt att göra, men ofta måste du hantera "legacy" struktur och data som bryter mot domänbegränsningar. Här är ett exempel:
id-namn 1 John 2 Smith 3 John 4 Smith 5 Smith 6 TomDu kanske frågar: "Hur skiljer sig det här problemet från det föregående Det finns trots allt en ännu enklare lösning här - ta helt enkelt bort alla rader från varje grupp med samma värden i namnkolumnen, och lämna bara raden med lägsta/högsta id-värde Till exempel så här:"
DELETE FROM T_pk WHERE id > (VÄLJ MIN (id) FROM T_pk X WHERE X.name = T_pk.name);Det stämmer, men jag har inte berättat allt ännu. :-) Föreställ dig att vi har en underordnad tabell T_details länkad till tabellen T_pk med en främmande nyckel:
CREATE TABLE T_details (id_pk INT UTLÄNDSKA NYCKELREFERENSER T_pk PÅ DELETE CASCADE , färg VARCHAR (10), PRIMÄRNYCKEL (id_pk, färg);Den här tabellen kan innehålla följande data:
id_pk färg 1 blå 1 röd 2 grön 2 röd 3 röd 4 blå 6 rödFör större tydlighet, låt oss använda frågan
VÄLJ id, namn, färg FRÅN T_pk JOIN T_details PÅ id= id_pk;för att se namn:
id namn färg 1 John blå 1 John röd 2 Smith grön 2 Smith röd 3 John röd 4 Smith blå 6 Tom rödDet visar sig alltså att data som faktiskt avser en person felaktigt allokerades till olika föräldraregister. Dessutom fanns det dubbletter i den här tabellen:
1 John red 3 John red
Uppenbarligen kommer sådana uppgifter att leda till felaktiga analyser och rapporter. Dessutom kommer kaskadradering att resultera i dataförlust. Till exempel, om vi bara lämnar raderna med minsta ID i varje grupp i T_pk-tabellen, kommer vi att förlora raden
4 Smith blå
i tabellen T_details. Därför måste vi ta hänsyn till båda tabellerna när vi eliminerar dubbletter.
Data "rengöring" kan utföras i två steg:
- Uppdatera tabellen T_details genom att tilldela data relaterade till ett namn till id:t med det minsta antalet i gruppen.
- Ta bort dubbletter från T_pk-tabellen, lämna endast rader med minsta ID i varje grupp med samma värde i namnkolumnen.
Uppdaterar tabellen T_details
VÄLJ id_pk, namn, färg , RANK () ÖVER (PARTITION 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 PÅ id=id_pk;bestämmer förekomsten av dubbletter (dup-värde > 1) och det lägsta id-värdet i en grupp med identiska namn (min_id). Här är resultatet av att köra den här frågan:
id_pk namn färg dup min_id 1 John blå 1 1 1 John röd 1 1 3 John röd 2 1 4 Smith blå 1 2 2 Smith grön 1 2 2 Smith röd 1 2 6 Tom röd 1 6Nu måste vi ersätta id_pk-värdet med min_pk-värdet för alla rader utom den tredje, eftersom denna rad är en dubblett av den andra raden, vilket indikeras av värdet dup=2. En uppdateringsbegäran kan skrivas så här:
UPPDATERA T_details SET id_pk=min_id FROM T_details T_d JOIN (VÄLJ id_pk, namn, färg , RANK () OVER (PARTITION BY name, color ORDER BY name, color, 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 ;Ta bort upprepningar
DatabaskällaBehovet av att deduplicera data är vanligt, särskilt när man tar itu med datakvalitetsproblem i miljöer där dubbelarbete har uppstått på grund av brist på begränsningar för att säkerställa dataunikhet. För att demonstrera, använd följande kod för att förbereda ett exempel på data med dubbletter av beställningar i en tabell med namnet MyOrders:
IF OBJECT_ID("Sales.MyOrders") INTE ÄR NULL DROP TABELL Sales.MyOrders; GÅ VÄLJ * INTO Försäljning.Mina beställningar FRÅN Försäljning.Beställningar UNION ALLA VÄLJ * FRÅN Försäljning.Beställningar UNION ALLA VÄLJ * FRÅN Försäljning.Beställningar;
Föreställ dig att du behöver eliminera dubbletter av data och lämnar bara en instans av varje med ett unikt orderid-värde. Dubblettnummer markeras med funktionen ROW_NUMBER, partitionering med ett förmodat unikt värde (orderid i vårt fall) och använder slumpmässig ordning om du inte bryr dig om vilken rad du ska behålla och vilken du ska ta bort. Här är koden där funktionen ROW_NUMBER markerar dubbletter:
SELECT orderid, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders;
Då måste du överväga olika varianter beroende på antalet rader som behöver tas bort, procentandelen av tabellstorleken, vad det är för antalet, aktiviteten i produktionsmiljön och andra omständigheter. När antalet rader som raderas är litet räcker det vanligtvis att använda en fullständigt loggad raderingsoperation, som tar bort alla instanser med ett radnummer större än en:
Men om antalet rader som raderas är stort - särskilt när det utgör en stor andel av tabellens rader - radering med full inspelning loggoperationer kommer att vara för långsamma. I det här fallet kanske du vill överväga att använda en bulkloggningsoperation som SELECT INTO för att kopiera de unika raderna (numrerade 1) till en annan tabell. Därefter raderas den ursprungliga tabellen nytt bord namnet på fjärrtabellen tilldelas, begränsningar, index och triggers återskapas. Här är koden för den färdiga lösningen:
MED C AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY orderid ORDER BY (SELECT NULL)) AS n FROM Sales.MyOrders) VÄLJ orderid, custid, empid, orderdate, requireddate, shippeddate, shipperid, freight, shipname, shipaddress, shipcity, shipregion, ship postalcode, shipcountry INTO Sales.OrdersTmp FROM C WHERE n = 1; DROP TABLE Sales.MyOrders; EXEC sp_rename "Sales.OrdersTmp", "MyOrders"; -- Återskapa index, begränsningar och utlösare
För enkelhetens skull har jag inte lagt till någon transaktionskontroll här, men du måste alltid komma ihåg att flera användare kan arbeta med datan samtidigt. När du implementerar den här metoden i en produktionsmiljö måste du följa följande sekvens:
Öppen transaktion.
Skaffa ett bordslås.
Kör SELECT-sats IN I.
Ta bort och byt namn på objekt.
Återskapa index, begränsningar och utlösare.
Begär transaktionen.
Det finns ett annat alternativ - att filtrera endast unika eller endast icke-unika rader. Både ROW_NUMBER och RANK beräknas baserat på orderid, ungefär så här:
SELECT orderid, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum, RANK() OVER(ORDER BY orderid) AS rnk FROM Sales.MyOrders;
Observera att resultaten bara har en rad för varje unikt värde orderid matchar radnumret och rangordningen. Om du till exempel behöver ta bort en liten del av datan kan du kapsla in den tidigare begäran i en CTE-definition och sedan extern begäran exekvera instruktionen för att ta bort rader som har annat nummer linjer och rang.
