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!