MySQL GROUP BY en HAVING-clausule met voorbeelden

Wat is de SQL GROUP BY-clausule?

De GROUP BY-clausule is een SQL-opdracht die wordt gebruikt groepeer rijen die dezelfde waarden hebbenHet wordt binnen de SELECT-instructie geschreven en wordt normaal gesproken samen met aggregatiefuncties gebruikt om samenvattende rapporten uit de database te genereren.

Dat is wat het doet: het vat gegevens samen opgeslagen in de database. Query's die de GROUP BY-clausule bevatten, worden gegroepeerde query's genoemd en retourneren één rij voor elk gegroepeerd item.

SQL GROUP BY-syntaxis

Nu het doel van de clausule duidelijk is, bekijk dan de syntaxis van een eenvoudige gegroepeerde query.

SELECT statements... GROUP BY column_name1[, column_name2, ...] [HAVING condition];

HIER

• "SELECT-instructies…" is de standaard SQL KIEZEN opdrachtquery.
• "GROEP DOOR kolomnaam1" is de clausule die de groepstaak uitvoertping gebaseerd op kolomnaam1.
• "[, kolomnaam2, …]" is optioneel en vertegenwoordigt andere kolomnamen wanneer de groepping wordt uitgevoerd op meer dan één kolom.
• "[AANDOENING HEBBEN]" is optioneel en wordt gebruikt om de rijen te beperken die door de GROUP BY-clausule worden beïnvloed. Het is vergelijkbaar met de WHERE-clausule, behalve dat het wordt toegepast na de groepping.

Grouping Een enkele kolom gebruiken

De snelste manier om het effect van de SQL GROUP BY-clausule te zien, is door een ongegroepeerde query te vergelijken met een gegroepeerde query. Begin met een eenvoudige query die alle geslachtsvermeldingen in de tabel 'members' retourneert.

SELECT `gender` FROM `members`;

Er worden negen rijen geretourneerd en elke waarde wordt herhaald. Stel dat we in plaats daarvan de unieke waarden voor geslacht willen. De onderstaande query voegt de GROUP BY-clausule toe.

SELECT `gender` FROM `members` GROUP BY `gender`;

Voer het bovenstaande script uit in MySQL Werkbank tegen myflixdb geeft ons de volgende resultaten.

Merk op dat er slechts twee rijen zijn geretourneerd, omdat de tabel slechts twee geslachtstypen bevat. De GROUP BY-clausule groepeerde alle "mannelijke" leden en gaf één rij voor hen terug, en deed hetzelfde met de "vrouwelijke" leden.

Grouping Meerdere kolommen gebruiken

Grouping Een filter op één kolom is vaak te grof voor een echt rapport. GROUP BY accepteert een door komma's gescheiden lijst met kolommen, en de combinatie van hun waarden definieert elke groep.

Stel dat we een lijst willen met de category_id-waarden van films en de bijbehorende jaartallen waarin de films zijn uitgebracht. Bekijk eerst de uitvoer van deze eenvoudige query.

SELECT `category_id`, `year_released` FROM `movies`;

De gemarkeerde rijen laten zien dat het resultaat duplicaten bevat. Door dezelfde query met GROUP BY uit te voeren, worden deze verwijderd.

SELECT `category_id`, `year_released` FROM `movies` GROUP BY `category_id`, `year_released`;

Voer het bovenstaande script uit in MySQL Workbench levert de volgende resultaten op in combinatie met myflixdb, zoals hieronder weergegeven.

De GROUP BY-clausule werkt op zowel category_id als year_released om te identificeren unieke rijen. De twee dubbele rijen voor categorie 6 in 2007 zijn samengevoegd tot één.

Vuistregel: Als het categorie-ID hetzelfde is, maar het jaar van uitgave verschilt, wordt de rij als uniek beschouwd. Als het categorie-ID en het jaar van uitgave voor meerdere rijen hetzelfde zijn, worden de rijen als duplicaten beschouwd en wordt er slechts één weergegeven.

Grouping en aggregatiefuncties

Het verwijderen van duplicaten is nuttig, maar de echte kracht van groeperen schuilt in...ping verschijnt wanneer het wordt gecombineerd met geaggregeerde functiesEen aggregatiefunctie berekent één waarde voor elke groep: COUNT telt rijen, SUM telt waarden op en AVG, MIN en MAX beschrijven de spreiding.

Stel dat we het totale aantal mannelijke en vrouwelijke leden in de database willen weten. Het onderstaande script doet dat.

SELECT `gender`, COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

Voer het bovenstaande script uit in MySQL Workbench levert de volgende resultaten op bij het gebruik van myflixdb.

De rijen worden gegroepeerd op basis van elke unieke geslachtswaarde, en het aantal rijen binnen elke groep wordt geteld door de aggregatiefunctie COUNT. De negen ledenrecords worden samengevoegd tot twee samenvattingsrijen.

Queryresultaten beperken met behulp van de HAVING-clausule

GroupingEen rapport hoeft niet altijd voor elke rij in een tabel te worden gegenereerd. Soms moet het rapport worden beperkt tot een bepaald criterium, en dat is de taak van de HAVING-clausule.

Stel dat we alle releasejaren willen weten voor films met categorie-ID 8. Het onderstaande script levert dat resultaat op.

SELECT * FROM `movies` GROUP BY `category_id`, `year_released` HAVING `category_id` = 8;

Voer het bovenstaande script uit in MySQL Workbench levert de volgende resultaten op in combinatie met myflixdb, zoals hieronder weergegeven.

Alleen films met categorie-ID 8 zijn behouden op basis van de HAVING-voorwaarde.

Waarschuwing: MySQL Vanaf versie 5.7 is de modus ONLY_FULL_GROUP_BY standaard ingeschakeld. In deze modus wordt een SELECT * query met een GROUP BY-clausule geweigerd, omdat movie_id, title en director niet gegroepeerd of geaggregeerd zijn. In een productieomgeving is het raadzaam de gegroepeerde kolommen expliciet te benoemen, bijvoorbeeld: SELECT category_id, year_released FROM movies GROUP BY category_id, year_released HAVING category_id = 8;

WAAR vs. HEBBEN vs. GROUP BY vs. ORDER BY

Beginners halen deze vier bijzinnen vaak door elkaar, omdat ze alle vier de resultaatset bepalen. Het verschil zit hem in... wanneer MySQL De volgende voorwaarden worden toegepast: WHERE wordt uitgevoerd voordat de rijen worden gegroepeerd, HAVING wordt uitgevoerd erna, en ORDER BY wordt als laatste uitgevoerd.

Het praktische gevolg is een prestatieprobleem. Filteren met WHERE verwijdert rijen vóór de groep.ping Het werk begint, dus een voorwaarde die niet afhankelijk is van een totaalresultaat hoort thuis in de WHERE-clausule in plaats van in de HAVING-clausule.

Veelgestelde vragen