INLEIDING
Op het einde van het jaar is het traditioneel tijd voor de lijstjes, elke lijst die wekelijks een top samenstelt, doet dat in de kerstperiode voor het hele jaar en komt dan met een “Top X van het jaar Y”. Als IT’er stel ik me dan automatisch de vraag “welke query zou er schuilen achter die samengestelde Top X?”. Uiteraard weten wij niet hoe hun database er precies uitziet, maar toch wil ik graag een poging doen om een fictieve SQL-statement samen te stellen.
DATA
Aangezien we ergens moeten beginnen, stel ik eerst even vast hoe de tabel eruit ziet die de gegevens bevat met de Top.
rakingid | year | week | song | songranking |
1 | 2018 | 23 | AAA | 49 |
2 | 2018 | 24 | BBB | 4 |
3 | 2018 | 24 | AAA | 46 |
4 | 2018 | 25 | BBB | 6 |
5 | 2018 | 25 | AAA | 4 |
6 | 2018 | 25 | CCC | 8 |
… | | | | |
Opmerking: In een echte database zal de song geen tekst bevatten maar de ID van een liedje, waarvan de details in een aparte tabel zijn opgeslagen. In dit voorbeeld hebben we voor het gemak echter het liedje als tekst opgeslagen.
BEREKENING
Om deze berekening goed te kunnen uitvoeren, splitsen we de opzoeking in een aantal stappen zodat het duidelijk wordt hoe het opzoeken precies in zijn werk gaat.
Basis-query
Eerst maken we een simpele query om alle gegevens op te halen van het jaar 2018, zodat oude rankings niet worden meegeteld
SELECT * FROM ranking WHERE year = 2018
Groeperen per liedje
Daarna gaan we alle records groeperen per liedje om een lijst te krijgen die we verder zullen ordenen
SELECT * FROM ranking WHERE year = 2018 GROUP BY song
Aantal vermeldingen opzoeken
Vervolgens zoeken we het aantal vermeldingen per liedje op (= rankingcount) en geven die samen met de naam weer
SELECT song, count(songranking) as rankingcount FROM ranking WHERE year = 2018 GROUP BY song ORDER BY rankingcount DESC
Score berekenen
Tot slot willen we rekening houden met de plaats van het liedje elke week aangezien een liedje dat telkens hoger staat in de lijst, ook in de finale lijst hoger moet eindigen. Hiervoor kennen we het punten toe waarbij we de plaats in de hitparade aftrekken van 51 (zodat de hoogste 50 punten krijgt, de 2e 49 punten enz tot het liedje op plaats 50 nog 1 punt krijgt toegekend)
SELECT song, count(songranking) as rankingcount, sum(51-songranking) as rankingscore FROM ranking WHERE year = 2018 GROUP BY song ORDER BY rankingscore DESC
RESULTATEN
Als we met bovenstaande gegevens het resultaat uitrekenen, dan krijgen we de volgende scores:
song | rankingcount | rankingscore |
BBB | 2 | 92 |
AAA | 3 | 54 |
CCC | 1 | 43 |
Zoals je kan zien, staat het liedje BBB hoger dan AAA, terwijl het er een week langer heeft ingestaan. De plaatsen op de ranglijst waren echter veel lager, waardoor de plaats in de finale ranking eveneens daalt
CONCLUSIE
Het is af en toe wel eens leuk om een real-life berekening te gaan uitwerken om te zien hoe dit gebeurt. Zo blijven we onze hersenen gezond houden en kunnen we die theorie van weleer nog eens in de praktijk toepassen!