Alt du trenger å vite om SQL GROUP BY -erklæringen

Alt du trenger å vite om SQL GROUP BY -erklæringen

Mye av kraften i relasjonsdatabaser kommer fra å filtrere data og kombinere tabeller. Det er derfor vi representerer disse forholdene i utgangspunktet. Men moderne databasesystemer gir en annen verdifull teknikk: gruppering.





Med gruppering kan du trekke ut sammendragsinformasjon fra en database. Den lar deg kombinere resultater for å lage nyttige statistiske data. Gruppering sparer deg for å skrive kode for vanlige saker, for eksempel gjennomsnittslister over tall. Og det kan gi mer effektive systemer.



Hva gjør GROUP BY -klausulen?

GROUP BY, som navnet antyder, grupperer resultatene til et mindre sett. Resultatene består av en rad for hver distinkte verdi i den grupperte kolonnen. Vi kan vise bruken ved å se på noen eksempeldata med rader som deler noen vanlige verdier.





hvordan kjøre en .bat -fil

Følgende er en veldig enkel database med to tabeller som representerer rekordalbum. Du kan sette opp en slik database ved å skrive et grunnleggende skjema for ditt valgte databasesystem. De album tabellen har ni rader med en primærnøkkel id kolonne og kolonner for navn, artist, utgivelseså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 |
+----+---------------------------+-----------+--------------+-------+

De artister bordet er enda enklere. Den har syv rader 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å ulike aspekter ved GROUP BY med bare et enkelt datasett som dette. Selvfølgelig vil et datasett i virkeligheten ha mange, mange flere rader, men prinsippene forblir de samme.

Gruppering etter en enkelt kolonne

La oss si at vi vil finne ut hvor mange album vi har for hver artist. Start med en typisk PLUKKE UT spørring for å hente artist_id -kolonnen:



SELECT artist_id FROM albums

Dette returnerer alle ni rader, som forventet:

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

Legg til uttrykket for å gruppere disse resultatene etter artisten GROUP BY artist_id :



SELECT artist_id FROM albums GROUP BY artist_id

Som gir følgende resultater:

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

Det er syv rader i resultatsettet, redusert fra de totalt ni i album bord. Hver unik artist_id har en enkelt rad. Til slutt, for å få de faktiske tallene, legg til TELLE(*) til de valgte kolonnene:

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

Resultatene grupperer to par med rader for artistene med id -er 2 og 6 . Hver har to album i vår database.

I slekt: The Essential SQL Commands Cheat Sheet for Beginners

Slik får du tilgang til grupperte data med en samlet funksjon

Du har kanskje brukt TELLE fungere før, spesielt i TELLE(*) form som sett ovenfor. Det henter antall resultater i et sett. Du kan bruke den til å få totalt antall poster i en tabell:

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

COUNT er en samlet funksjon. Dette begrepet refererer til funksjoner som oversetter verdier fra flere rader til en enkelt verdi. De brukes ofte i forbindelse med GROUP BY -setningen.

I stedet for bare å telle antall rader, kan vi bruke en samlet funksjon på grupperte verdier:

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 totale salget vist ovenfor for artistene 2 og 6 er salget av flere album sammen:

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

Gruppering etter flere kolonner

Du kan gruppere etter mer enn én kolonne. Bare ta med flere kolonner eller uttrykk, atskilt med kommaer. Resultatene grupperes i henhold til kombinasjonen av disse kolonnene.

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

Dette vil vanligvis gi flere resultater enn gruppering etter 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 |
+--------------+-------+----------+

Vær oppmerksom på at i vårt lille eksempel har bare to album samme utgivelsesår og salgstall (28 i 1977).

Nyttige samlede funksjoner

Bortsett fra COUNT fungerer flere funksjoner godt med GROUP. Hver funksjon returnerer en verdi basert på postene som tilhører hver resultatgruppe.

  • COUNT () returnerer det totale antallet matchende poster.
  • SUM () returnerer summen av alle verdiene i den gitte kolonnen lagt opp.
  • MIN () returnerer den minste verdien i en gitt kolonne.
  • MAX () returnerer den største verdien i en gitt kolonne.
  • AVG () returnerer gjennomsnittsgjennomsnittet. Det tilsvarer SUM () / COUNT ().

Du kan også bruke disse funksjonene uten en GROUP -klausul:

kan ikke installere noe på windows 10
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Bruke GROUP BY med en WHERE -klausul

På samme måte som med en vanlig SELECT, kan du fortsatt bruke WHERE til å filtrere resultatsettet:

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

Nå har du bare albumene utgitt etter 1990, gruppert etter artist. Du kan også bruke en join med WHERE -klausulen, uavhengig av 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 |
+---------+--------+

Vær imidlertid oppmerksom på at hvis du prøver å filtrere basert 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 feilmelding:

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

Kolonner basert på samlede data er ikke tilgjengelige for WHERE -leddet.

Bruke HAVING -klausulen

Så hvordan filtrerer du resultatsettet etter at en gruppering har funnet sted? De HAR klausulen omhandler dette behovet:

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

Vær oppmerksom på at HAVING -klausulen kommer etter GROUP BY. Ellers er det egentlig en enkel erstatning av WHERE med HAVING. Resultatene er:

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

Du kan fortsatt bruke en HVOR -betingelse til å filtrere resultatene før gruppering. Det vil fungere sammen med en HAVING -klausul for filtrering etter gruppering:

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;

Bare en artist i vår database ga ut mer enn ett album etter 1990:

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

Kombinere resultater med GROUP BY

GROUP BY -setningen er en utrolig nyttig del av SQL -språket. Den kan gi oppsummerende informasjon om data, for eksempel en innholdsside. Det er et utmerket alternativ til å hente store mengder data. Databasen håndterer denne ekstra arbeidsmengden godt siden selve designen gjør den optimal for jobben.

Når du forstår gruppering og hvordan du kobler til flere tabeller, vil du kunne utnytte det meste av kraften i en relasjonsdatabase.

Dele Dele kvitring E -post Hvordan spørre flere databasetabeller samtidig med SQL -koblinger

Lær hvordan du bruker SQL -joins for å effektivisere søk, spare tid og få deg til å føle deg som en SQL -bruker.

iphone se fast på eple -logoen
Les neste Relaterte temaer
  • Programmering
  • SQL
Om forfatteren Bobby Jack(58 artikler publisert)

Bobby er en teknologientusiast som jobbet som programvareutvikler i de fleste to tiårene. Han brenner for spill, jobber som Reviews Editor i Switch Player Magazine, og er fordypet i alle aspekter av online publisering og webutvikling.

Mer fra Bobby Jack

Abonner på vårt nyhetsbrev

Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!

Klikk her for å abonnere