Mycket av kraften i relationsdatabaser kommer från att filtrera data och sammanfoga tabeller. Det är därför vi representerar dessa relationer i första hand. Men moderna databassystem tillhandahåller en annan värdefull teknik: gruppering.
Med gruppering kan du extrahera sammanfattande information från en databas. Det låter dig kombinera resultat för att skapa användbar statistisk data. Gruppering sparar dig från att skriva kod för vanliga fall som t.ex. Och det kan ge effektivare system.
Vad gör GROUP BY-klausulen?
GROUP BY, som namnet antyder, grupperar resultaten till en mindre uppsättning. Resultaten består av en rad för varje distinkt värde i den grupperade kolumnen. Vi kan visa dess användning genom att titta på några exempeldata med rader som delar några gemensamma värden.
Följande är en mycket enkel databas med två tabeller som representerar skivalbum. Du kan ställa in en sådan databas genom att skriva ett grundläggande schema för ditt valda databassystem. De album tabellen har nio rader med en primärnyckel id kolumn och kolumner för namn, artist, releaseår och försäljning:
+----+---------------------------+-----------+--------------+-------+
| 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 konstnärer bordet är ännu enklare. Den har sju rader med id- och namnkolumner:
+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+
Du kan förstå olika aspekter av GROUP BY med bara en enkel datamängd som denna. Naturligtvis skulle en datauppsättning från verkligheten ha många, många fler rader, men principerna förblir desamma.
Gruppering efter en enda kolumn
Låt oss säga att vi vill ta reda på hur många album vi har för varje artist. Börja med en typisk VÄLJ fråga för att hämta kolumnen artist_id:
SELECT artist_id FROM albums
Detta returnerar alla nio rader, som förväntat:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+
Om du vill gruppera dessa resultat efter artisten lägger du till frasen GROUP BY artist_id:
SELECT artist_id FROM albums GROUP BY artist_id
Vilket ger följande resultat:
+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+
Det finns sju rader i resultatuppsättningen, reducerade från de totalt nio i album tabell. Varje unik artist_id har en enda rad. Slutligen, för att få de faktiska räkningarna, lägg till RÄKNA
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 |
+-----------+----------+
till de valda kolumnerna: Resultaten grupperar två par rader för artisterna med id 2 och6
. Var och en har två album i vår databas.
Hur man får åtkomst till grupperad data med en aggregerad funktion Du kan ha använt RÄKNA funktion innan, särskilt i RÄKNA
SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
form enligt ovan. Den hämtar antalet resultat i en uppsättning. Du kan använda den för att få det totala antalet poster i en tabell:
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 |
+-----------+------------+
COUNT är en aggregerad funktion. Denna term hänvisar till funktioner som översätter värden från flera rader till ett enda värde. De används ofta tillsammans med GROUP BY-satsen.
SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+
Istället för att bara räkna antalet rader kan vi använda en aggregerad funktion på grupperade värden:
Den totala försäljningen som visas ovan för artisterna 2 och 6 är deras försäljning av flera album tillsammans:
SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales
Gruppering efter flera kolumner
+--------------+-------+----------+
| 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 |
+--------------+-------+----------+
Du kan gruppera efter mer än en kolumn. Inkludera bara flera kolumner eller uttryck, separerade med kommatecken. Resultaten kommer att grupperas enligt kombinationen av dessa kolumner.
Detta ger vanligtvis fler resultat än att gruppera efter en enda kolumn:
Observera att i vårt lilla exempel har bara två album samma släppår och försäljningsantal (28 år 1977).
Användbara samlade funktioner
Förutom COUNT, fungerar flera funktioner bra med GROUP. Varje funktion returnerar ett värde baserat på de poster som hör till varje resultatgrupp.
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+
COUNT() returnerar det totala antalet matchande poster. SUM() returnerar summan av alla värden i den givna kolumnen adderade. MIN() returnerar det minsta värdet i en given kolumn. MAX() returnerar det största värdet i en given kolumn. AVG() returnerar medelmedelvärdet. Det är motsvarigheten till SUM() / COUNT().
Du kan också använda dessa funktioner utan en GROUP-sats:
SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+
Använda GROUP BY med en WHERE-klausul
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 |
+---------+--------+
Precis som med en vanlig SELECT kan du fortfarande använda WHERE för att filtrera resultatuppsättningen:
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;
Nu har du bara de album som släppts efter 1990, grupperade efter artist. Du kan också använda en join med WHERE-satsen, oberoende av GROUP BY:
ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'
Observera dock att om du försöker filtrera baserat på en aggregerad kolumn:
Du får ett felmeddelande:
Kolumner baserade på aggregerade data är inte tillgängliga för WHERE-satsen. Använder HAVING-klausulen Så, hur filtrerar du resultatuppsättningen efter att en gruppering har ägt rum? De
SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;
HAR
+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+
klausul handlar om detta behov:
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;
Observera att HAVING-satsen kommer efter GROUP BY. Annars är det i huvudsak en enkel ersättning av WHERE med HAVING. Resultaten är:
+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+
Du kan fortfarande använda ett WHERE-villkor för att filtrera resultaten före grupperingen. Det kommer att fungera tillsammans med en HAVING-sats för filtrering efter grupperingen:
Endast en artist i vår databas släppte mer än ett album efter 1990:
Kombinera resultat med GROUP BY
GROUP BY-satsen är en otroligt användbar del av SQL-språket. Det kan ge sammanfattande information om data, till exempel för en innehållssida. Det är ett utmärkt alternativ till att hämta stora mängder data. Databasen hanterar denna extra arbetsbelastning bra eftersom själva designen gör den optimal för jobbet.
När du väl förstår gruppering och hur du går med i flera tabeller, kommer du att kunna använda det mesta av kraften i en relationsdatabas.
Om författaren
Bobby Jack (65 artiklar publicerade)
Bobby är en teknikentusiast som arbetat som mjukvaruutvecklare under de flesta av två decennier. Han brinner för spel, arbetar som chefredaktör på Switch Player Magazine och är fördjupad i alla aspekter av onlinepublicering och webbutveckling.
Mer från Bobby Jack free Prenumerera på vårt nyhetsbrev
Gå med i vårt nyhetsbrev för tekniska tips, recensioner,
e-böcker och exklusiva erbjudanden!
Klicka här för att prenumerera
