Dit artikel laat zien hoe u dubbele rijen in een databasetabel kunt vinden. Dit is een veel voorkomende beginnersvraag. De basistechniek is eenvoudig. Ik zal ook enkele variaties laten zien, zoals hoe je “duplicaten in twee kolommen” kunt vinden (een recente vraag over het #mysql IRC-kanaal).
Hoe vind ik dubbele rijen
De eerste stap is om te definiëren wat een rij precies tot een duplicaat van een andere rij maakt. Meestal is dit eenvoudig: ze hebben dezelfde waarde in een bepaalde kolom. Ik neem dit als een werkdefinitie voor dit artikel, maar je kunt moet u de onderstaande zoekopdrachten wijzigen als uw idee van “dupliceren” ingewikkelder is.
Voor dit artikel gebruik ik deze voorbeeldgegevens:
De eerste twee rijen hebben dezelfde waarde in de day kolom, dus als ik deze als duplicaten beschouw, is hier een zoekopdracht om ze te vinden. De zoekopdracht gebruikt een GROUP BY -clausule om alle rijen met dezelfde day -waarde in één “groep” te plaatsen en vervolgens de grootte van de group:
De gedupliceerde rijen hebben een groter aantal dan één. Als u alleen rijen wilt zien die gedupliceerd zijn, moet u een HAVING clausule (niet een WHERE clausule), zoals deze:
Dit is de basistechniek: groepeer op de kolom die duplicaten bevat en toon alleen die groepen met meer dan één rij.
Waarom kan je geen WHERE-component gebruiken?
A WHERE -component filtert de rijen voordat ze worden gegroepeerd. Een HAVING -component filtert ze na het groeperen. Daarom kunt u geen WHERE clausule in de bovenstaande zoekopdracht.
Hoe verwijder je dubbele rijen
Een gerelateerde vraag is hoe je de ‘dubbele’ rijen verwijdert zodra je vind ze. Een veel voorkomende t vragen wanneer het opschonen van slechte gegevens is om alle duplicaten op één na te verwijderen, zodat u de juiste indexen en primaire sleutels op de tafel kunt zetten en voorkomen dat duplicaten opnieuw in de tabel terechtkomen.
Nogmaals, de eerste wat u moet doen, is ervoor zorgen dat uw definitie duidelijk is. Welke rij wilt u precies behouden? De eerste? Degene met de grootste waarde van een kolom? Voor dit artikel ga ik ervan uit dat u de ‘eerste’ rij wilt behouden, die met de kleinste waarde van de kolom id. Dat betekent dat je elke andere rij wilt verwijderen.
Waarschijnlijk is de gemakkelijkste manier om dit te doen met een tijdelijke tabel. Vooral in MySQL zijn er enkele beperkingen voor het selecteren uit een tabel en het bijwerken ervan in dezelfde query. U kunt deze omzeilen, zoals ik uitleg in mijn artikel Hoe te selecteren uit een updatedoel in MySQL, maar ik zal deze complicaties gewoon vermijden en een tijdelijke tabel gebruiken.
De exacte definitie van de taak is om elke rij met een duplicaat te verwijderen, behalve de rij met de minimale waarde id voor die groep. U moet dus niet alleen de rijen vinden waar er meer dan één in de groep zijn, maar u moet ook de rij vinden die u wilt behouden. U kunt dat doen met de functie MIN(). Hier zijn enkele zoekopdrachten om de tijdelijke tabel te maken en de gegevens te vinden die u nodig hebt om DELETE uit te voeren:
Nu u deze gegevens heeft, kunt u doorgaan met het verwijderen de ‘slechte’ rijen. Er zijn veel manieren om dit te doen, en sommige zijn beter dan andere (zie mijn artikel over veel-op-een problemen in SQL), maar nogmaals, ik zal de fijnere punten vermijden en je gewoon een standaardsyntaxis laten zien die zou moeten werken in elke RDBMS die subquery’s ondersteunt:
Als uw RDBMS geen subquery’s ondersteunt, of als het efficiënter is, wilt u misschien een multi-table delete uitvoeren. De syntaxis hiervoor verschilt per systeem, dus u moet de documentatie van uw systeem raadplegen. Mogelijk moet u dit allemaal in een transactie doen om te voorkomen dat andere gebruikers de gegevens wijzigen terwijl u aan het werk bent, als dat een probleem is.
Hoe u duplicaten in meerdere kolommen kunt vinden
Iemand heeft onlangs een soortgelijke vraag gesteld op het #mysql IRC-kanaal:
Ik heb een tabel met kolommen
bencdie twee andere tabellenbenckoppelt, en ik wil om alle rijen te vinden die duplicaten hebben inbofc.
Het was moeilijk te begrijpen wat dit precies betekende, maar na enig gesprek begreep ik het: de persoon wilde in staat zijn om unieke indexen op kolommen b en c afzonderlijk.
Het is vrij eenvoudig om rijen te vinden met dubbele waarden in de ene of de andere kolom, zoals ik je hierboven heb laten zien: groepeer gewoon op die kolom mn en tel de groepsgrootte. En het is gemakkelijk om hele rijen te vinden die exact duplicaten zijn van andere rijen: groepeer gewoon op zoveel kolommen als u nodig heeft.Maar het is moeilijker om rijen te identificeren die een gedupliceerde b -waarde of een gedupliceerde c -waarde hebben. Neem de volgende voorbeeldtabel, die ongeveer is wat de persoon beschreef:
Nu kun je gemakkelijk zien dat er enkele ‘dubbele’ rijen in deze tabel zijn, maar geen twee rijen hebben eigenlijk hetzelfde tuple {b, c}. Daarom is dit wat moeilijker op te lossen.
Query’s die niet werken
Als je in twee kolommen groepeert, krijg je verschillende resultaten, afhankelijk van hoe je groepeert en tel. Dit is waar de IRC-gebruiker stomverbaasd over raakte. Soms vonden zoekopdrachten enkele duplicaten, maar andere niet. Hier zijn enkele dingen die deze persoon heeft geprobeerd:
Deze zoekopdracht retourneert elke rij in de tabel, met een COUNT(*) van 1, wat verkeerd gedrag lijkt te zijn, maar dat is het eigenlijk niet. Waarom? Omdat de > 1 zich binnen de COUNT() bevindt. Het is vrij gemakkelijk over het hoofd te zien, maar deze vraag is eigenlijk hetzelfde als
Waarom? Omdat (b > 1) een booleaanse uitdrukking is. Dat is helemaal niet wat je wilt. U wilt
Dit geeft natuurlijk nul rijen terug, omdat er geen dubbele {b, c} tupels zijn. De persoon heeft veel andere combinaties geprobeerd van HAVING clausules en OR’s en AND’s, gegroepeerd op één kolom en de andere geteld, enzovoort:
Niets vond echter alle duplicaten. Wat ik denk dat het het meest frustrerend maakte, is dat het gedeeltelijk werkte, waardoor de persoon dacht dat het bijna de juiste vraag was … misschien zou gewoon een andere variant het begrijpen …
In feite is het onmogelijk om te doen met dit soort eenvoudige GROUP BY zoekopdracht. Waarom is dit? De reden hiervoor is dat wanneer u op één kolom groepeert, u dezelfde waarden van de andere kolom over meerdere groepen verdeelt. U kunt dit visueel zien door op die kolommen te ordenen, en dat is wat groeperen doet. Sorteer eerst op kolom b en kijk hoe ze zijn gegroepeerd:
| a | b | c |
|---|---|---|
| 7 | 1 | 1 |
| 8 | 1 | 2 |
| 9 | 1 | 3 |
| 10 | 2 | 1 |
| 11 | 2 | 2 |
| 12 | 2 | 3 |
| 13 | 3 | 1 |
| 14 | 3 | 2 |
| 15 | 3 | 3 |
Wanneer u (groep) bestelt op kolom b, worden de dubbele waarden in kolom c zijn verdeeld in verschillende groepen, dus je kunt ze niet tellen met COUNT(DISTINCT c) zoals de persoon probeerde te doen. Geaggregeerde functies zoals COUNT() werken alleen binnen een groep en hebben geen toegang tot rijen die in andere groepen zijn geplaatst. Evenzo, wanneer u bestelt op c, worden de dubbele waarden in de kolom b verdeeld over verschillende groepen. Het is niet mogelijk om deze zoekopdracht te laten doen wat gewenst is.
Enkele correcte oplossingen
Waarschijnlijk is de eenvoudigste oplossing om de duplicaten voor elke kolom afzonderlijk te vinden en UNION ze samen, als volgt:
De what_col kolom in de uitvoer geeft aan in welke kolom de dubbele waarde is gevonden. Een andere benadering is om subquery’s:
Dit is waarschijnlijk veel minder efficiënt dan de UNION benadering, en zal elke gedupliceerde rij tonen, niet alleen de waarden die gedupliceerd zijn. Nog een andere benadering is om self-joins uit te voeren tegen gegroepeerde subquery’s in de FROM -clausule. Dit is ingewikkelder om correct te schrijven, maar kan nodig zijn voor sommige complexe gegevens, of voor efficiëntie:
Elk van deze vragen is voldoende, en ik weet zeker dat er ook andere manieren zijn. Als je UNION kunt gebruiken, is dit waarschijnlijk het gemakkelijkst.
