Group by
Een GROUP BY is in principe altijd in combinatie met een aggregate functie.
Stel we hebben de volgende tabel scores:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
+ -------------- + ----------------- +
| player_id | player_score |
+ -------------- + ----------------- +
| 2 | 41 |
| 1 | 50 |
| 1 | 60 |
| 3 | 55 |
| 2 | 59 |
| 3 | 54 |
+ -------------- + ----------------- +
| player_id | player_score |
+ -------------- + ----------------- +
| 2 | 41 |
| 1 | 50 |
| 1 | 60 |
| 3 | 55 |
| 2 | 59 |
| 3 | 54 |
+ -------------- + ----------------- +
We willen nu uit de tabel per speler de hoogste score opvragen, je ziet dan weleens queries zoals dit:
Code (php)
1
SELECT player_id, player_score FROM scores GROUP BY player_id ORDER BY player_score
Deze query wordt door MySQL gewoon uitgevoerd (in andere databases krijg je een foutmelding), maar met dit resultaat:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
+ -------------- + ----------------- +
| player_id | player_score |
+ -------------- + ----------------- +
| 3 | 55 |
| 1 | 50 |
| 2 | 41 |
+ -------------- + ----------------- +
| player_id | player_score |
+ -------------- + ----------------- +
| 3 | 55 |
| 1 | 50 |
| 2 | 41 |
+ -------------- + ----------------- +
Je ziet dat het resultaat van deze query als een tang op een varken slaat.
Er wordt namelijk eerst gegroepeerd dan pas gesorteerd.
Laten we dit dus maar gewoon even in een fatsoenlijke query zetten:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Query Output:
> SELECT s.player_id,
p.player_name,
MAX(s.player_score) AS score
FROM scores s
JOIN players p USING (player_id)
GROUP BY s.player_id, p.player_name
ORDER BY score DESC
+ -------------- + ---------------- + ---------- +
| player_id | player_name | score |
+ -------------- + ---------------- + ---------- +
| 1 | Kees | 60 |
| 2 | Piet | 59 |
| 3 | Karel | 55 |
+ -------------- + ---------------- + ---------- +
> SELECT s.player_id,
p.player_name,
MAX(s.player_score) AS score
FROM scores s
JOIN players p USING (player_id)
GROUP BY s.player_id, p.player_name
ORDER BY score DESC
+ -------------- + ---------------- + ---------- +
| player_id | player_name | score |
+ -------------- + ---------------- + ---------- +
| 1 | Kees | 60 |
| 2 | Piet | 59 |
| 3 | Karel | 55 |
+ -------------- + ---------------- + ---------- +
Dit is het resultaat wat we willen.
In standaard SQL is het de regel dat kolommen in de SELECT list in een aggregate functie staan of gebruikt worden in de GROUP BY clause.
Conditioneel groeperen
GROUP BY ... HAVING (....) dus.
Bijvoorbeeld, we willen uit de scores tabel de spelers hebben wiens minimale score boven het algemeen gemiddelde ligt:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Query Output:
> SELECT s.player_id,
p.player_name,
MIN(s.player_score) AS score
FROM scores AS s
JOIN players AS p USING (player_id)
GROUP BY s.player_id, p.player_name
HAVING (MIN(s.player_score) > (SELECT AVG(player_score) FROM scores))
ORDER BY score DESC
+ -------------- + ---------------- + ---------- +
| player_id | player_name | score |
+ -------------- + ---------------- + ---------- +
| 3 | Karel | 54 |
+ -------------- + ---------------- + ---------- +
> SELECT s.player_id,
p.player_name,
MIN(s.player_score) AS score
FROM scores AS s
JOIN players AS p USING (player_id)
GROUP BY s.player_id, p.player_name
HAVING (MIN(s.player_score) > (SELECT AVG(player_score) FROM scores))
ORDER BY score DESC
+ -------------- + ---------------- + ---------- +
| player_id | player_name | score |
+ -------------- + ---------------- + ---------- +
| 3 | Karel | 54 |
+ -------------- + ---------------- + ---------- +
Let op de subquery in de HAVINg clause; als ik dat niet gedaan had was het gemiddelde per speler berekend, wat in deze context natuurlijk geen resultaat opleverd.
Als een aggregate functie wordt toegepast zonder GROUP BY dan berekend ie over de gehele tabel.
« vorige pagina | volgende pagina »
Inhoudsopgave
- Inleiding
- Joins algemeen
- Inner en outer join
- Cross en natural join
- Joins in de praktijk (1)
- Subqueries
- Joins in de praktijk (2)
- Group by
- Group by voorbeelden
- Conditional staments
- Nog meer voorbeelden
- Nawoord