GROUP BY
1. Situatieschets
2. Inleidend woord
3. Group By
3.1 Eén enkele kolom
3.2 Meerdere kolommen
4. Having
1] Inleiding
De interesse & het opzoekingswerk voor dit onderdeel van mysql is opgeborreld toen ik volgende probleemsituatie kende: (die ik in de loop van de tutorial ook
zal blijven gebruiken)
"In een statistiekensysteem wordt bijgehouden hoeveel keer een bepaald bestand wordt gedownload. Iedere download (click) wordt in een tabel
gestoken als een rij, waarbij o.a. ip-adres, http-referer, etc. worden bijgehouden (dit terzijde), maar belangrijk is wel, het dlid wordt meegegegeven
aan elke click. In een andere tabel staan de de verschillende downloads (met hun id) geplaatst."
Probleemsituatie: hoe een top-100 lijst samenstellen op de snelst mogelijke manier?
Onze twee tabellen:
tbl_downloads
| id |
downloadnaam |
downloadurl |
| 1 |
Dj Addow - Rikeruna Fortuna |
http://www.sinterklaas.be/mixen/mix1.ogg |
| 2 |
Dj Addow - Spirit of Addow |
http://www.sinterklaas.be/mixen/mix2.ogg |
| 3 |
Dj Addow - Xaveriuna |
http://www.sinterklaas.be/mixen/mix3.ogg |
tbl_stats
| id |
dlid |
ip-adres |
komtvan |
| 1 |
1 |
81.254.236.80 |
http://www.google.be |
| 2 |
2 |
81.225.312.814 |
http://www.sitemasters.be/?pagina=downloads/downloads |
| 3 |
1 |
81.333.198.172 |
http://www.addow.be/index.php?actie=sets |
| 4 |
3 |
81.333.198.172 |
http://gamesforum.telenet.be/forum/forumdisplay.php?f=54 |
| 5 |
1 |
213.625.32.471 |
http://gamesforum.telenet.be/forum/forumdisplay.php?f=72 |
| 6 |
3 |
213.51.51.289 |
http://www.skynet.be/index.asp?lang=nl |
 top
2] Inleidend woord
Scripters die gewoon zijn van met MySQL te werken, hebben geen problemen op de duur met het selecteren van gegevens uit een database. Een voorbeeld:
mysql_query("SELECT downloadnaam FROM tbl_downloads WHERE id = '12'"); |
Maar SELECT heeft, gecombineerd met de GROUP BY component, héél wat meer in petto. Later in deze tut zullen we eveneens de HAVING component naderbij bekijken.
top
3] GROUP BY
Eén enkele kolom:
De component GROUP BY groepeert in feite de rijen die in een desbetreffende kolom dezelfde waarde hebben. Laten we dat gewoon illustreren aan de hand
van een voorbeeld:
mysql_query("SELECT dlid FROM tbl_stats GROUP BY dlid"); |
Hiermee gaan alle clicks met eenzelfde dlid (download id) gegroepeerd worden, en als tussenresultaat resulteert dat dus in:
id's | dlid
============
1,3,5 | 1
2 | 2
4,6 | 3
|
Dit zit er niet al te praktisch uit, maar als we de handige COUNT(*) component invoeren, gaat er een waaier aan mogelijkheden open:
mysql_query("SELECT dlid,COUNT(*) FROM tbl_stats GROUP BY dlid"); |
Resultaat:
dlid | aantal downloads
=======================
1 | 3
2 | 1
3 | 2
|
Meerdere kolommen:
Je kan tevens ook meerdere GROUP BY kolommen gebruiken, zodat er dus met meerdere factoren rekening gehouden kan worden. In mijn voorbeeld kan je
bvb gaan uitzoeken van welke website er per download het meeste geleeched wordt:
mysql_query("SELECT dlid,COUNT(*) FROM tbl_stats GROUP BY dlid,komtvan"); |
Resultaat:
dlid | aantal downloads per site
================================
1 | 1,1,1
2 | 1
3 | 1,1
|
Gebruik maken van deze GROUP BY gaat je denkwerk dus een pak verminderen, en het laden van je pagina alleen maar bevorderen. Zo moet je niet
alles in arrays zitten steken en gaan rangschikken, etc.
top
4] HAVING
HAVING kunnen we heel makkelijk verwoorden: net hetzelfde als WHERE, maar met dat kleine verschil dat HAVING in combinatie met GROUP BY wordt gebruikt, meerbepaald bij het tussenresultaat dat we in de eerste stap al bekomen waren (zie boven). Laten we dat snel eens bekijken ahv 2 voorbeeldjes:
Voorbeeld 1:
We gaan in onze statistieken eens nagaan, hoeveel keer gebruiker met ip-adres 81.333.198.172 mijn downloads heeft geleeched. We zijn immers
allemaal statsbeesten!
mysql_query("SELECT dlid,COUNT(*) FROM tbl_stats GROUP BY dlid HAVING ip-adres = '81.333.198.172'"); |
Resultaat:
dlid | aantal downloads van ip-adres 81.333.198.172
===================================================
1 | 1
2 | 0
3 | 1
|
Voorbeeld 2:
We willen nu enkel de aantallen bijhouden die meer dan 1x zijn geteld (aangezien ikzelf altijd 1x een testdownload uitvoer).
mysql_query("SELECT dlid FROM tbl_stats GROUP BY dlid HAVING COUNT(*) > 1"); |
Resultaat:
dlid | aantal downloads > 1
===========================
dlid 1
dlid 3
|
top
|
Wat is jouw favoriete javascript framework? (S: 18, R: 2)
(4.44) 