Alt hvad du behøver at vide om SQL GROUP BY -erklæringen

Alt hvad du behøver at vide om SQL GROUP BY -erklæringen

Meget af relationsdatabasernes magt kommer fra filtrering af data og sammenføjning af tabeller. Det er derfor, vi i første omgang repræsenterer disse forbindelser. Men moderne databasesystemer giver en anden værdifuld teknik: gruppering.





Gruppering giver dig mulighed for at udtrække resuméoplysninger fra en database. Det lader dig kombinere resultater for at oprette nyttige statistiske data. Gruppering sparer dig for at skrive kode til almindelige sager, f.eks. Gennemsnitslister over tal. Og det kan skabe mere effektive systemer.



Hvad gør GROUP BY -klausulen?

GROUP BY, som navnet antyder, grupperer resultaterne til et mindre sæt. Resultaterne består af en række for hver særskilt værdi af den grupperede kolonne. Vi kan vise dets anvendelse ved at se på nogle eksempeldata med rækker, der deler nogle fælles værdier.





hvordan man bruger tl

Det følgende er en meget enkel database med to tabeller, der repræsenterer pladealbum. Du kan oprette en sådan database ved skrive et grundlæggende skema for dit valgte databasesystem. Det album tabellen har ni rækker med en primær nøgle id kolonne og kolonner for navn, kunstner, udgivelsesår og salg:

+----+---------------------------+-----------+--------------+-------+
| 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 |
+----+---------------------------+-----------+--------------+-------+

Det kunstnere bordet er endnu enklere. Den har syv rækker med id- og navnekolonner:



+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+

Du kan forstå forskellige aspekter af GROUP BY med blot et simpelt datasæt som dette. Selvfølgelig ville et datasæt i virkeligheden have mange, mange flere rækker, men principperne forbliver de samme.

Gruppering efter en enkelt kolonne

Lad os sige, at vi vil finde ud af, hvor mange albums vi har til hver kunstner. Start med en typisk VÆLG forespørgsel for at hente kolonnen artist_id:



SELECT artist_id FROM albums

Dette returnerer alle ni rækker som forventet:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+

Hvis du vil gruppere disse resultater efter kunstneren, skal du tilføje sætningen GRUPPE AF artist_id :



SELECT artist_id FROM albums GROUP BY artist_id

Hvilket giver følgende resultater:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+

Der er syv rækker i resultatsættet, reduceret fra de i alt ni i album bord. Hver unik kunstner_id har en enkelt række. Endelig, for at få de faktiske tællinger, tilføj TÆLLE(*) til de valgte kolonner:

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 |
+-----------+----------+

Resultaterne grupperer to par rækker for kunstnerne med id'er 2 og 6 . Hver har to album i vores database.

Relaterede: Essential SQL Commands Cheat Sheet for Beginners

Sådan får du adgang til grupperede data med en samlet funktion

Du har muligvis brugt TÆLLE funktion før, især i TÆLLE(*) form som set ovenfor. Det henter antallet af resultater i et sæt. Du kan bruge den til at få det samlede antal poster i en tabel:

SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

COUNT er en samlet funktion. Dette udtryk refererer til funktioner, der oversætter værdier fra flere rækker til en enkelt værdi. De bruges ofte sammen med GROUP BY -erklæringen.

I stedet for bare at tælle antallet af rækker, kan vi anvende en samlet funktion til grupperede værdier:

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 |
+-----------+------------+

Det samlede salg vist ovenfor for kunstnere 2 og 6 er deres flere albums salg tilsammen:

SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+

Gruppering efter flere kolonner

Du kan gruppere efter mere end én kolonne. Inkluder bare flere kolonner eller udtryk, adskilt af kommaer. Resultaterne grupperes efter kombinationen af ​​disse kolonner.

SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales

Dette vil typisk give flere resultater end gruppering efter en enkelt kolonne:

+--------------+-------+----------+
| 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 |
+--------------+-------+----------+

Bemærk, at i vores lille eksempel har kun to album samme udgivelsesår og salgstal (28 i 1977).

Nyttige samlede funktioner

Bortset fra COUNT fungerer flere funktioner godt med GROUP. Hver funktion returnerer en værdi baseret på de poster, der tilhører hver resultatgruppe.

  • COUNT () returnerer det samlede antal matchende poster.
  • SUM () returnerer summen af ​​alle værdier i den givne kolonne tilsat.
  • MIN () returnerer den mindste værdi i en given kolonne.
  • MAX () returnerer den største værdi i en given kolonne.
  • AVG () returnerer gennemsnittet. Det svarer til SUM () / COUNT ().

Du kan også bruge disse funktioner uden en GROUP -klausul:

symptomer på en svigtende harddisk
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Brug af GROUP BY med en WHERE -klausul

Ligesom med en normal SELECT kan du stadig bruge WHERE til at filtrere resultatsættet:

SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id +-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+

Nu har du kun de albums udgivet efter 1990, grupperet efter kunstner. Du kan også bruge en joinforbindelse til WHERE -klausulen uafhængigt af 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 |
+---------+--------+

Bemærk dog, at hvis du prøver at filtrere baseret på en samlet kolonne:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;

Du får en fejl:

ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Kolonner baseret på aggregerede data er ikke tilgængelige for WHERE -klausulen.

Brug af HAVING -klausulen

Så hvordan filtrerer du resultatsættet, efter at en gruppering har fundet sted? Det AT HAVE klausul omhandler dette behov:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

Bemærk, at HAVING -klausulen kommer efter GROUP BY. Ellers er det i det væsentlige en simpel udskiftning af WHERE med HAVING. Resultaterne er:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

Du kan stadig bruge en WHERE -betingelse til at filtrere resultaterne før grupperingen. Det fungerer sammen med en HAVING -klausul til filtrering efter grupperingen:

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;

Kun én kunstner i vores database udgav mere end ét album efter 1990:

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

Kombination af resultater med GROUP BY

GROUP BY -sætningen er en utrolig nyttig del af SQL -sproget. Det kan give resuméoplysninger om data, f.eks. Til en indholdsside. Det er et glimrende alternativ til at hente store mængder data. Databasen håndterer denne ekstra arbejdsbyrde godt, da selve designet gør den optimal til jobbet.

Når du forstår gruppering og hvordan du slutter dig til flere tabeller, vil du kunne udnytte det meste af kraften i en relationsdatabase.

Del Del Tweet E -mail Sådan forespørges flere databasetabeller på én gang med SQL -tilslutninger

Lær, hvordan du bruger SQL -joins til at strømline forespørgsler, spare tid og få dig til at føle dig som en SQL -bruger.

ting at gøre på computeren
Læs Næste Relaterede emner
  • Programmering
  • SQL
Om forfatteren Bobby Jack(58 artikler udgivet)

Bobby er en teknologientusiast, der arbejdede som softwareudvikler i det meste af to årtier. Han brænder for spil, arbejder som Reviews Editor hos Switch Player Magazine og er nedsænket i alle aspekter af onlineudgivelse og webudvikling.

Mere fra Bobby Jack

Abonner på vores nyhedsbrev

Tilmeld dig vores nyhedsbrev for at få tekniske tips, anmeldelser, gratis e -bøger og eksklusive tilbud!

Klik her for at abonnere