Home-theater-designers
Een groot deel van de kracht van relationele databases komt van het filteren van gegevens en het samenvoegen van tabellen. Daarom vertegenwoordigen wij in de eerste plaats die relaties. Maar moderne databasesystemen bieden nog een andere waardevolle techniek: groeperen.
Met groeperen kunt u samenvattende informatie uit een database halen. Hiermee kunt u resultaten combineren om bruikbare statistische gegevens te creëren. Door te groeperen hoeft u geen code te schrijven voor veelvoorkomende gevallen, zoals het middelen van cijferlijsten. En het kan zorgen voor efficiëntere systemen.
Wat doet de GROUP BY-clausule?
GROUP BY, zoals de naam al doet vermoeden, groepeert resultaten in een kleinere set. De resultaten bestaan uit één rij voor elke afzonderlijke waarde van de gegroepeerde kolom. We kunnen het gebruik ervan laten zien door enkele voorbeeldgegevens te bekijken met rijen die enkele gemeenschappelijke waarden delen.
hoe de ps4-ventilator schoon te maken zonder hem uit elkaar te halen
Het volgende is een zeer eenvoudige database met twee tabellen die recordalbums vertegenwoordigen. U kunt zo'n database opzetten door: een basisschema schrijven voor het door u gekozen databasesysteem. De albums tabel heeft negen rijen met een primaire sleutel ID kaart kolom en kolommen voor naam, artiest, jaar van uitgave en verkoop:
+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+De artiesten tafel is nog eenvoudiger. Het heeft zeven rijen met kolommen voor ID en naam:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+U kunt verschillende aspecten van GROUP BY begrijpen met slechts een eenvoudige dataset zoals deze. Natuurlijk zou een real-life dataset veel, veel meer rijen hebben, maar de principes blijven hetzelfde.
Groeperen op een enkele kolom
Laten we zeggen dat we willen weten hoeveel albums we voor elke artiest hebben. Begin met een typische KIES query om de kolom artist_id op te halen:
SELECT artist_id FROM albumsDit retourneert alle negen rijen, zoals verwacht:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+Om deze resultaten te groeperen op artiest, voegt u de zin toe GROEP OP artiest_id :
SELECT artist_id FROM albums GROUP BY artist_idWat de volgende resultaten geeft:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+Er zijn zeven rijen in de resultatenset, verminderd van het totaal van negen in de albums tafel. Elk uniek artiest_id heeft een enkele rij. Ten slotte, om de werkelijke tellingen te krijgen, voegt u toe GRAAF(*) naar de geselecteerde kolommen:
SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+De resultaten groeperen twee paar rijen voor de artiesten met ids 2 en 6 . Elk heeft twee albums in onze database.
Verwant: De essentiële SQL-commando's Cheatsheet voor beginners
Toegang krijgen tot gegroepeerde gegevens met een geaggregeerde functie
Je hebt misschien de GRAAF functie eerder, met name in de GRAAF(*) vorm zoals hierboven te zien is. Het haalt het aantal resultaten in een set op. U kunt het gebruiken om het totale aantal records in een tabel te krijgen:
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+COUNT is een aggregatiefunctie. Deze term verwijst naar functies die waarden uit meerdere rijen omzetten in een enkele waarde. Ze worden vaak gebruikt in combinatie met de GROUP BY-instructie.
In plaats van alleen het aantal rijen te tellen, kunnen we een aggregatiefunctie toepassen op gegroepeerde waarden:
SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+De totale verkoop die hierboven wordt weergegeven voor artiesten 2 en 6 is de gecombineerde verkoop van hun meerdere albums:
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+Groeperen op meerdere kolommen
U kunt groeperen op meer dan één kolom. Voeg gewoon meerdere kolommen of uitdrukkingen toe, gescheiden door komma's. De resultaten worden gegroepeerd volgens de combinatie van deze kolommen.
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, salesDit levert doorgaans meer resultaten op dan groeperen op een enkele kolom:
+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+Merk op dat in ons kleine voorbeeld slechts twee albums hetzelfde jaar van uitgave en verkoopcijfers hebben (28 in 1977).
Handige aggregatiefuncties
Afgezien van COUNT werken verschillende functies goed met GROUP. Elke functie retourneert een waarde op basis van de records die bij elke resultaatgroep horen.
- COUNT() retourneert het totale aantal overeenkomende records.
- SUM() retourneert het totaal van alle waarden in de gegeven kolom opgeteld.
- MIN() retourneert de kleinste waarde in een bepaalde kolom.
- MAX() retourneert de grootste waarde in een bepaalde kolom.
- AVG() retourneert het gemiddelde gemiddelde. Het is het equivalent van SUM() / COUNT().
U kunt deze functies ook gebruiken zonder een GROUP-clausule:
beste virtual reality-games voor Android
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+GROUP BY gebruiken met een WHERE-clausule
Net als bij een normale SELECT kun je nog steeds WHERE gebruiken om de resultatenset te filteren:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+Nu heb je alleen die albums die na 1990 zijn uitgebracht, gegroepeerd op artiest. U kunt ook een join gebruiken met de WHERE-component, onafhankelijk van de GROUP BY:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id +---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+Houd er echter rekening mee dat als u probeert te filteren op basis van een geaggregeerde kolom:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;U krijgt een foutmelding:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'Kolommen op basis van geaggregeerde gegevens zijn niet beschikbaar voor de WHERE-component.
De HAVING-clausule gebruiken
Dus, hoe filter je de resultatenset nadat een groepering heeft plaatsgevonden? De HEBBEN clausule gaat in op deze behoefte:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;Merk op dat de HAVING-component na de GROUP BY komt. Anders is het in wezen een eenvoudige vervanging van de WHERE met HAVING. De resultaten zijn:
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+U kunt nog steeds een WHERE-voorwaarde gebruiken om de resultaten vóór de groepering te filteren. Het werkt samen met een HAVING-clausule om te filteren na de groepering:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;Slechts één artiest in onze database heeft na 1990 meer dan één album uitgebracht:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+Resultaten combineren met GROUP BY
De GROUP BY-instructie is een ongelooflijk nuttig onderdeel van de SQL-taal. Het kan beknopte informatie van gegevens geven, bijvoorbeeld voor een inhoudspagina. Het is een uitstekend alternatief voor het ophalen van grote hoeveelheden data. De database kan deze extra werklast goed aan, omdat het ontwerp het optimaal maakt voor de taak.
Als u eenmaal begrijpt hoe u groepeert en hoe u aan meerdere tabellen kunt deelnemen, kunt u de meeste kracht van een relationele database benutten.
Deel Deel Tweeten E-mail Meerdere databasetabellen tegelijk opvragen met SQL-joinsLeer hoe u SQL-joins gebruikt om query's te stroomlijnen, tijd te besparen en u het gevoel te geven dat u een krachtige SQL-gebruiker bent.
amazon zegt dat het pakket is afgeleverd, maar dat was het nietLees volgende Gerelateerde onderwerpen
- Programmeren
- SQL
Bobby is een technologie-enthousiasteling die bijna twee decennia als softwareontwikkelaar heeft gewerkt. Hij is gepassioneerd door gaming, werkt als recensie-editor bij Switch Player Magazine en is ondergedompeld in alle aspecten van online publiceren en webontwikkeling.
Meer van Bobby JackAbonneer op onze nieuwsbrief
Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!
Klik hier om je te abonneren