Hoe GROUP BY en ORDER BY gebruiken in SQL

Introductie

Gestructureerde Querytaal (SQL) databases kunnen veel gegevens opslaan en beheren over talrijke tabellen. Bij grote datasets is het belangrijk om te begrijpen hoe gegevens gesorteerd kunnen worden, vooral voor het analyseren van resultaatssets of het organiseren van gegevens voor rapporten of externe communicatie.

Twee veelvoorkomende instructies in SQL die helpen bij het sorteren van uw gegevens zijn GROUP BY en ORDER BY. Een GROUP BY-instructie sorteert gegevens door deze te groeperen op basis van de kolom(men) die u specificeert in de query en wordt gebruikt met aggregaatfuncties. Een ORDER BY stelt u in staat resultaatsets alfabetisch of numeriek en in oplopende of aflopende volgorde te organiseren.

In deze tutorial zult u queryresultaten sorteren in SQL met behulp van de GROUP BY– en ORDER BY-instructies. U zult ook oefenen met het implementeren van aggregaatfuncties en de WHERE-clausule in uw queries om de resultaten nog verder te sorteren.

Vereisten

Om deze handleiding te volgen, heb je een computer nodig met een relationeel databasebeheersysteem (RDBMS) dat SQL gebruikt. De instructies en voorbeelden in deze tutorial zijn gevalideerd met behulp van de volgende omgeving:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• MySQL geïnstalleerd en beveiligd op de server. Volg onze Hoe MySQL te installeren op Ubuntu 20.04 handleiding om dit in te stellen. Deze handleiding gaat ervan uit dat je ook een niet-root MySQL-gebruiker hebt ingesteld, zoals beschreven in Stap 3 van deze handleiding.

Om het sorteren van gegevensresultaten in deze tutorial te oefenen, heb je een database en tabel nodig met voorbeeldgegevens. Als je er geen klaar hebt om in te voegen, kun je het volgende lezen Verbinding maken met MySQL en een voorbeelddatabase instellen sectie om te leren hoe je een database en tabel kunt maken. In deze tutorial wordt verwezen naar deze voorbeelddatabase en -tabel.

Verbinding maken met MySQL en een voorbeelddatabase instellen

Als je SQL-database op een externe server draait, maak dan via SSH verbinding met je server vanaf je lokale machine:

ssh sammy@your_server_ip

Volgende, open de MySQL-prompt en vervang sammy door je MySQL-gebruikersaccountinformatie:

mysql -u sammy -p

Maak een database met de naam movieDB:

CREATE DATABASE movieDB;

Als de database succesvol is aangemaakt, ontvang je de volgende uitvoer:

Output
Query OK, 1 row affected (0.01 sec)

Om de database movieDB te selecteren, voer je de volgende USE-verklaring uit:

USE movieDB;

Output
Database changed

Na het selecteren van de database, maak een tabel aan binnen deze. Voor het voorbeeld in deze tutorial, zullen we een tabel maken die informatie opslaat over vertoningen in een lokale bioscoop. Deze tabel zal de volgende zeven kolommen bevatten:

• theater_id: slaat waarden van het gegevenstype int op voor elke vertoningsruimte van het theater, en zal dienen als de primaire sleutel, wat betekent dat elke waarde in deze kolom zal fungeren als een unieke identifier voor zijn respectievelijke rij.
• datum: gebruikt het gegevenstype DATE om de specifieke datum van vertoning op te slaan per jaar, maand en dag (YYYY-MM-DD). Dit gegevenstype voldoet aan de volgende parameters: vier cijfers voor het jaar, en een maximum van twee cijfers voor de maand en dag.
• tijd: vertegenwoordigt de geplande vertoning van de film met het gegevenstype TIME per uur, minuten en seconden (HH:MM:SS).
• film_naam: slaat de naam van de film op met het gegevenstype varchar met een maximum van 40 tekens.
• movie_genre: gebruikt het gegevenstype varchar met een maximum van 30 tekens, om informatie over het respectieve genre van elke film vast te houden.
• guest_total: toont het totale aantal gasten dat een filmvoorstelling heeft bijgewoond met het gegevenstype int.
• ticket_cost: gebruikt het gegevenstype decimal, met een precisie van vier en een schaal van één, wat betekent dat waarden in deze kolom vier cijfers kunnen hebben, en twee cijfers rechts van het decimaalteken. Deze kolom vertegenwoordigt de ticketkosten voor de specifieke filmvoorstelling.

Maak een tabel met de naam movie_theater die elk van deze kolommen bevat door het volgende CREATE TABLE-commando uit te voeren:

CREATE TABLE movie_theater (
theater_id int, 
date DATE,
time TIME, 
movie_name varchar(40),
movie_genre varchar(30),
guest_total int,
ticket_cost decimal(4,2),
PRIMARY KEY (theater_id)
); 

Vervolgens voeg je wat voorbeeldgegevens toe aan de lege tabel:

INSERT INTO movie_theater
(theater_id, date, time, movie_name, movie_genre, guest_total, ticket_cost)
VALUES
(1, '2022-05-27', '10:00:00', 'Top Gun Maverick', 'Action', 131, 18.00),
(2, '2022-05-27', '10:00:00', 'Downton Abbey A New Era', 'Drama', 90, 18.00),
(3, '2022-05-27', '10:00:00', 'Men', 'Horror', 100, 18.00),
(4, '2022-05-27', '10:00:00', 'The Bad Guys', 'Animation', 83, 18.00),
(5, '2022-05-28', '09:00:00', 'Top Gun Maverick', 'Action', 112, 8.00),
(6, '2022-05-28', '09:00:00', 'Downton Abbey A New Era', 'Drama', 137, 8.00),
(7, '2022-05-28', '09:00:00', 'Men', 'Horror', 25, 8.00),
(8, '2022-05-28', '09:00:00', 'The Bad Guys', 'Animation', 142, 8.00),
(9, '2022-05-28', '05:00:00', 'Top Gun Maverick', 'Action', 150, 13.00),
(10, '2022-05-28', '05:00:00', 'Downton Abbey A New Era', 'Drama', 118, 13.00),
(11, '2022-05-28', '05:00:00', 'Men', 'Horror', 88, 13.00),
(12, '2022-05-28', '05:00:00', 'The Bad Guys', 'Animation', 130, 13.00);

Output
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

Zodra je de gegevens hebt ingevoegd, ben je klaar om queryresultaten te sorteren in SQL.

Het gebruik van GROUP BY

De functie van een GROUP BY-statement is om records te groeperen met gedeelde waarden. Een GROUP BY-statement wordt altijd gebruikt met een aggregatiefunctie in een query. Zoals je je wellicht herinnert, vat een aggregatiefunctie informatie samen en geeft één resultaat terug. Bijvoorbeeld, je kunt de totale telling of som van een kolom opvragen en dit zal een enkele waarde in je resultaat opleveren. Met een GROUP BY-clausule kun je de aggregatiefunctie implementeren om één resultaatwaarde te verkrijgen voor elke groep die je wenst.

GROUP BY is handig om meerdere gewenste resultaten gesorteerd op uw opgegeven groep(en) terug te krijgen, in plaats van slechts één kolom. Bovendien moet GROUP BY altijd na de FROM-verklaring en de WHERE-clausule komen, als u ervoor kiest om er een te gebruiken. Hier volgt een voorbeeld van hoe een query met een GROUP BY en een aggregatiefunctie is gestructureerd:

SELECT column_1, AGGREGATE_FUNCTION(column_2) FROM table GROUP BY column_1;

Om te illustreren hoe u GROUP BY-verklaringen kunt gebruiken, stel dat u de campagne leidt voor verschillende filmuitgaven en u wilt de succes van uw marketinginspanningen evalueren. U vraagt een plaatselijke bioscoop om de gegevens die ze hebben verzameld van gasten op vrijdag en zaterdag te delen. Begin met het bekijken van de gegevens door SELECT en het *-symbool uit te voeren om “elke kolom” uit de movie_theater-tabel te selecteren:

SELECT * FROM movie_theater;

Output
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
| theater_id | date       | time     | movie_name              | movie_genre | guest_total | ticket_cost |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
|          1 | 2022-05-27 | 10:00:00 | Top Gun Maverick        | Action      |         131 |       18.00 |
|          2 | 2022-05-27 | 10:00:00 | Downton Abbey A New Era | Drama       |          90 |       18.00 |
|          3 | 2022-05-27 | 10:00:00 | Men                     | Horror      |         100 |       18.00 |
|          4 | 2022-05-27 | 10:00:00 | The Bad Guys            | Animation   |          83 |       18.00 |
|          5 | 2022-05-28 | 09:00:00 | Top Gun Maverick        | Action      |         112 |        8.00 |
|          6 | 2022-05-28 | 09:00:00 | Downton Abbey A New Era | Drama       |         137 |        8.00 |
|          7 | 2022-05-28 | 09:00:00 | Men                     | Horror      |          25 |        8.00 |
|          8 | 2022-05-28 | 09:00:00 | The Bad Guys            | Animation   |         142 |        8.00 |
|          9 | 2022-05-28 | 05:00:00 | Top Gun Maverick        | Action      |         150 |       13.00 |
|         10 | 2022-05-28 | 05:00:00 | Downton Abbey A New Era | Drama       |         118 |       13.00 |
|         11 | 2022-05-28 | 05:00:00 | Men                     | Horror      |          88 |       13.00 |
|         12 | 2022-05-28 | 05:00:00 | The Bad Guys            | Animation   |         130 |       13.00 |
+------------+------------+----------+-------------------------+-------------+-------------+-------------+
12 rows in set (0.00 sec)

Hoewel deze gegevens nuttig zijn, wilt u een diepere beoordeling uitvoeren en de resultaten sorteren voor enkele specifieke kolommen.

Aangezien u hebt gewerkt aan films in een paar verschillende genres, bent u geïnteresseerd in hoe goed ze werden ontvangen door bioscoopbezoekers. Specifiek wilt u weten wat het gemiddelde aantal mensen is dat elke filmgenre heeft bekeken. Gebruik SELECT om de verschillende soorten films uit de movie_genre-kolom op te halen. Pas vervolgens de aggregatiefunctie AVG toe op de guest_total-kolom, gebruik AS om een alias te maken voor een kolom genaamd average, en voeg de GROUP BY-verklaring toe om resultaten te groeperen op movie_genre. Door ze op deze manier te groeperen, krijgt u de gemiddelde resultaten voor elk filmgenre:

SELECT movie_genre, AVG(guest_total) AS average
FROM movie_theater 
GROUP BY movie_genre;

Output
+-------------+----------+
| movie_genre | average  |
+-------------+----------+
| Action      | 131.0000 |
| Drama       | 115.0000 |
| Horror      |  71.0000 |
| Animation   | 118.3333 |
+-------------+----------+
4 rows in set (0.00 sec)

Deze output geeft de vier gemiddelden voor elk genre binnen de movie_genre-groep weer. Op basis van deze informatie trokken Action-films het hoogste gemiddelde aantal gasten per vertoning aan.

Vervolgens, laten we zeggen dat je de omzet van het theater wilt meten over twee afzonderlijke dagen. De volgende query retourneert waarden uit de date-kolom, evenals waarden geretourneerd door de SUM-aggregaatfunctie. Specifiek zal de aggregaatfunctie SUM een wiskundige vergelijking tussen haakjes bevatten om (met behulp van de *-operator) het totale aantal gasten te vermenigvuldigen met de kosten van een ticket, voorgesteld als: SUM(guest_total * ticket_cost). Deze query bevat de AS-clausule om de alias total_revenue te verstrekken voor de kolom geretourneerd door de aggregaatfunctie. Voltooi vervolgens de query met de GROUP BY-verklaring om de queryresultaten te groeperen op de date-kolom:

SELECT date, SUM(guest_total * ticket_cost) 
AS total_revenue 
FROM movie_theater 
GROUP BY date;

Output
+------------+---------------+
| date       | total_revenue |
+------------+---------------+
| 2022-05-27 |       7272.00 |
| 2022-05-28 |       9646.00 |
+------------+---------------+
2 rows in set (0.00 sec)

Aangezien je GROUP BY hebt gebruikt om de date-kolom te groeperen, geeft je output de resultaten voor de totale omzet uit kaartverkoop voor elke dag weer, in dit geval $7,272 voor vrijdag 27 mei en $9,646 voor zaterdag 28 mei.

Stel je nu voor dat je je wilt richten op en analyseren van één film: The Bad Guys. In dit scenario wil je erachter komen hoe timing en prijspunten van invloed zijn op de keuze van een gezin om een geanimeerde film te bekijken. Gebruik voor deze query de aggregatiefunctie MAX om de maximale ticket_cost op te halen, waarbij je ervoor zorgt dat je AS gebruikt om de alias voor de kolom price_data te maken. Gebruik vervolgens de WHERE-clausule om de resultaten te verfijnen op basis van movie_name tot enkel “The Bad Guys”, en gebruik AND om ook de meest populaire filmtoffentijden te bepalen op basis van guest_total nummers die groter waren dan 100 met de vergelijkingsoperator >. Voltooi ten slotte de query met de GROUP BY-verklaring en groepeer deze op time:

SELECT time, MAX(ticket_cost) AS price_data 
FROM movie_theater
WHERE movie_name = "The Bad Guys" 
AND guest_total > 100
GROUP BY time;

Output
+----------+------------+
| time     | price_data |
+----------+------------+
| 09:00:00 |       8.00 |
| 05:00:00 |      13.00 |
+----------+------------+
2 rows in set (0.00 sec)

Volgens deze output woonden meer gasten de film The Bad Guys bij tijdens de vroege matinee om 9:00 uur, die het meer betaalbare prijspunt had van $8.00 per ticket. Deze resultaten tonen echter ook aan dat filmgasten de hogere ticketprijs van $13.00 betaalden om 17:00 uur, wat suggereert dat gezinnen voorstellingen verkiezen die niet te laat op de dag zijn en iets meer willen betalen voor een ticket. Dit lijkt een redelijke beoordeling te zijn in vergelijking met de tijd van 22:00 uur toen de film The Bad Guys slechts 83 gasten had en de prijs per ticket $18.00 was. Deze informatie kan nuttig zijn om aan de bioscoopmanager te verstrekken als bewijs dat het openen van meer matinee- en vroege avondtijden de opkomst kan verhogen voor gezinnen die een keuze maken op basis van een voorkeurstijd en prijspunt.

Let op dat hoewel GROUP BY bijna altijd wordt gebruikt met een aggregatiefunctie, er uitzonderingen kunnen zijn, zij het onwaarschijnlijk. Als u echter uw resultaten wilt groeperen zonder een aggregatiefunctie, kunt u de DISTINCT-verklaring gebruiken om hetzelfde resultaat te bereiken. Een DISTINCT-clausule verwijdert eventuele duplicaten in een resultaatset door de unieke waarden in de kolom terug te geven, en het kan alleen worden gebruikt met een SELECT-verklaring. Bijvoorbeeld, als u alle films bij elkaar wilt groeperen op naam, kunt u dat doen met de volgende query:

SELECT DISTINCT movie_name FROM movie_theater;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| Downton Abbey A New Era |
| Men                     |
| The Bad Guys            |
+-------------------------+
4 rows in set (0.00 sec)

Zoals u zich herinnert van het bekijken van alle gegevens in de tabel, waren er duplicaten van de filmnamen omdat er meerdere vertoningen waren. Daarom verwijderde DISTINCT die duplicaten en groepeerde effectief de unieke waarden onder de enkele kolom movie_name. Dit is effectief identiek aan de volgende query, die een GROUP BY-verklaring bevat:

SELECT movie_name FROM movie_theater GROUP BY movie_name;

Nu je hebt geoefend met het gebruik van GROUP BY met aggregatiefuncties, leer je vervolgens hoe je je queryresultaten kunt sorteren met de ORDER BY-verklaring.

DOOR ORDER BY TE GEBRUIKEN

De functie van de ORDER BY-verklaring is om resultaten te sorteren in oplopende of aflopende volgorde op basis van de kolom(men) die u specificeert in de query. Afhankelijk van het gegevenstype dat wordt opgeslagen door de kolom die u erna specificeert, zal ORDER BY ze in alfabetische of numerieke volgorde organiseren. Standaard sorteert ORDER BY resultaten in oplopende volgorde; als u echter de volgorde aflopend wilt hebben, moet u het trefwoord DESC opnemen in uw query. U kunt ook de ORDER BY-verklaring gebruiken met GROUP BY, maar deze moet daarna komen om correct te functioneren. Net als bij GROUP BY moet ORDER BY ook na de FROM-verklaring en de WHERE-clausule komen. De algemene syntaxis voor het gebruik van ORDER BY is als volgt:

SELECT column_1, column_2 FROM table ORDER BY column_1;

Laten we doorgaan met de voorbeeldgegevens voor de bioscoop en oefenen met het sorteren van resultaten met ORDER BY. Begin met de volgende query die waarden ophaalt uit de kolom guest_total en die numerieke waarden organiseert met een ORDER BY-verklaring:

SELECT guest_total FROM movie_theater 
ORDER BY guest_total;

Output
+-------------+
| guest_total |
+-------------+
|          25 |
|          83 |
|          88 |
|          90 |
|         100 |
|         112 |
|         118 |
|         130 |
|         131 |
|         137 |
|         142 |
|         150 |
+-------------+
12 rows in set (0.00 sec)

Aangezien uw query een kolom heeft gespecificeerd met numerieke waarden, heeft de ORDER BY-verklaring de resultaten georganiseerd in numerieke en oplopende volgorde, beginnend met 25 onder de kolom guest_total.

Als u de kolom in aflopende volgorde wilt bestellen, voegt u het trefwoord DESC toe aan het einde van de query. Bovendien, als u de gegevens wilt ordenen op de tekens onder movie_name, zou u dat specificeren in uw query. Laten we dat type query uitvoeren met ORDER BY om de movie_name-kolom met tekenwaarden in aflopende volgorde te ordenen. Sorteer de resultaten nog verder door een WHERE-clausule op te nemen om de gegevens op te halen over films die om 22:00 uur worden vertoond uit de time-kolom:

SELECT movie_name FROM movie_theater
WHERE time = '10:00:00' 
ORDER BY movie_name DESC;

Output
+-------------------------+
| movie_name              |
+-------------------------+
| Top Gun Maverick        |
| The Bad Guys            |
| Men                     |
| Downton Abbey A New Era |
+-------------------------+
4 rows in set (0.01 sec)

Deze resultaatset vermeldt de vier verschillende filmvoorstellingen om 22:00 uur in aflopende alfabetische volgorde, te beginnen met Top Gun Maverick tot Downtown Abbey A New Era.

Voor deze volgende query combineert u de ORDER BY– en GROUP BY-statements met de aggregaatfunctie SUM om resultaten te genereren over de totale ontvangen omzet voor elke film. Maar laten we zeggen dat het filmtheater het totale aantal gasten verkeerd heeft geteld en vergat speciale feesten mee te tellen die vooraf tickets hadden gekocht en gereserveerd voor een groep van 12 personen bij elke vertoning.

In deze query gebruik SUM en voeg de extra 12 gasten toe bij elke filmvoorstelling door de operator voor optelling + te implementeren en vervolgens 12 toe te voegen aan de guest_total. Zorg ervoor dat je dit tussen haakjes plaatst. Vermenigvuldig vervolgens dit totaal met de ticket_cost met de operator *, en voltooi de wiskundige vergelijking door de haakjes aan het einde te sluiten. Voeg de AS-clausule toe om een alias te maken voor de nieuwe kolom met de naam total_revenue. Gebruik vervolgens GROUP BY om de resultaten van total_revenue te groeperen voor elke film op basis van de gegevens die zijn opgehaald uit de kolom movie_name. Gebruik ten slotte ORDER BY om de resultaten onder de nieuwe kolom total_revenue te organiseren in oplopende volgorde:

SELECT movie_name, SUM((guest_total + 12) * ticket_cost) 
AS total_revenue
FROM movie_theater 
GROUP BY movie_name 
ORDER BY total_revenue;

Output
+-------------------------+---------------+
| movie_name              | total_revenue |
+-------------------------+---------------+
| Men                     |       3612.00 |
| Downton Abbey A New Era |       4718.00 |
| The Bad Guys            |       4788.00 |
| Top Gun Maverick        |       5672.00 |
+-------------------------+---------------+
4 rows in set (0.00 sec)

Deze resultaten vertellen ons de totale omzet voor elke film met de extra verkoop van 12 gasttickets en organiseren de totale kaartverkoop in oplopende volgorde van laag naar hoog. Hieruit leren we dat Top Gun Maverick de meeste kaartverkoop ontving, terwijl Men de minste ontving. Ondertussen waren de films The Bad Guys en Downton Abbey A New Era zeer dicht bij elkaar wat betreft totale kaartverkoop.

In dit gedeelte heb je verschillende manieren geoefend om de ORDER BY-verklaring te implementeren en hoe je de gewenste volgorde kunt specificeren, zoals oplopende en aflopende volgorde voor zowel karakter- als numerieke gegevenswaarden. Je hebt ook geleerd hoe je de WHERE-clausule kunt opnemen om je resultaten te verfijnen, en een query uitgevoerd met zowel de GROUP BY– als ORDER BY-verklaringen met een aggregaatfunctie en een wiskundige vergelijking.

Conclusie

Het begrijpen van hoe je de GROUP BY en ORDER BY statements moet gebruiken is belangrijk om je resultaten en gegevens te sorteren. Of je nu meerdere resultaten onder één groep wilt organiseren, een van je kolommen in alfabetische en aflopende volgorde wilt organiseren, of beide tegelijkertijd wilt doen; het is aan jou en je gewenste resultaat(en). Je hebt ook geleerd over andere manieren om je resultaten nog verder te sorteren met de WHERE clause. Als je meer wilt leren, bekijk dan onze tutorial over Hoe Wildcards te Gebruiken in SQL om te oefenen met het filteren van resultaten met de LIKE clause.