Toen ik voor het eerst een rapportage moest maken waarin klanten gerangschikt werden op basis van hun totale bestellingen, terwijl ik ook per klant het percentage van de totale omzet wilde tonen, liep ik vast in een wirwar van subqueries. Drie geneste SELECT statements later realiseerde ik me dat er een elegantere oplossing moest zijn. Window functions bleken precies datgene te zijn waar ik naar zocht: een manier om complexe berekeningen uit te voeren over sets van rijen, zonder de resultaten te groeperen of de oorspronkelijke rijen te verliezen.
Window functions werken fundamenteel anders dan reguliere aggregate functions zoals SUM() of COUNT(). Waar een gewone SUM() alle rijen samenvat tot één resultaat, behoudt een window function alle originele rijen en voegt er berekende waarden aan toe. Dit maakt analyses mogelijk die anders onmogelijk zouden zijn zonder complexe joins of subqueries.
Running totals en cumulatieve berekeningen
Een van de meest praktische toepassingen van window functions is het berekenen van running totals. Stel je hebt een tabel met dagelijkse verkopen en wilt per rij zien wat de cumulatieve omzet tot die datum is. Met een traditionele aanpak zou je voor elke rij een subquery nodig hebben die alle voorgaande datums opsomt.
SELECT
verkoopdatum,
dagomzet,
SUM(dagomzet) OVER (ORDER BY verkoopdatum) as cumulatieve_omzet
FROM verkopen
ORDER BY verkoopdatum;
De OVER clause definieert het "window" waarover de berekening plaatsvindt. In dit geval ordenon we op verkoopdatum, waardoor SUM() voor elke rij alle voorgaande rijen meeneemt in de berekening. Het resultaat is een running total die groeit naarmate we verder in de tijd gaan.
Voor maandelijkse analyses pas ik vaak een variant toe waarbij ik per maand een running total bijhoud, maar aan het begin van elke nieuwe maand weer opnieuw start. Dit doe ik door PARTITION BY toe te voegen aan het window.
SELECT
verkoopdatum,
MONTH(verkoopdatum) as maand,
dagomzet,
SUM(dagomzet) OVER (
PARTITION BY YEAR(verkoopdatum), MONTH(verkoopdatum)
ORDER BY verkoopdatum
) as maandelijkse_cumulatieve_omzet
FROM verkopen
ORDER BY verkoopdatum;
PARTITION BY zorgt ervoor dat de window function opnieuw begint voor elke unieke combinatie van jaar en maand. Dit geeft me per dag de cumulatieve omzet binnen die specifieke maand, zonder dat vorige maanden meetellen.
Ranking en vergelijkingen tussen rijen
Rankings zijn een ander gebied waar window functions uitblinken. ROW_NUMBER(), RANK(), en DENSE_RANK() bieden elk een andere manier om rijen te rangschikken. Ik kom vaak situaties tegen waarin ik de top 3 producten per categorie wil identificeren, of klanten wil rangschikken op basis van hun aankoopgedrag.
SELECT
product_naam,
categorie,
verkoop_aantal,
ROW_NUMBER() OVER (PARTITION BY categorie ORDER BY verkoop_aantal DESC) as positie,
RANK() OVER (PARTITION BY categorie ORDER BY verkoop_aantal DESC) as ranking
FROM product_verkopen;
Het verschil tussen ROW_NUMBER() en RANK() wordt duidelijk wanneer er gelijke waarden zijn. ROW_NUMBER() geeft altijd unieke nummers, ook bij gelijke waarden, terwijl RANK() dezelfde waarde geeft aan gelijke resultaten en vervolgens nummers overslaat. DENSE_RANK() doet hetzelfde als RANK() maar slaat geen nummers over.
Voor percentiele berekeningen werk ik vaak met NTILE(), dat rijen verdeelt in een opgegeven aantal groepen. Dit is handig voor het creëren van kwartielen of decielen.
SELECT
klant_id,
totale_besteding,
NTILE(4) OVER (ORDER BY totale_besteding DESC) as kwartiel
FROM klant_uitgaven;
Deze query verdeelt klanten in vier groepen op basis van hun uitgaven, waarbij kwartiel 1 de 25% hoogste spenders bevat en kwartiel 4 de laagste.
Vergelijkingen met vorige en volgende waarden
LAG() en LEAD() functions stellen me in staat om waarden uit vorige of volgende rijen op te halen. Dit is bijzonder nuttig voor trend analyses waar ik de groei ten opzichte van de vorige periode wil berekenen.
SELECT
verkoopdatum,
dagomzet,
LAG(dagomzet) OVER (ORDER BY verkoopdatum) as vorige_dag_omzet,
dagomzet - LAG(dagomzet) OVER (ORDER BY verkoopdatum) as verschil_vorige_dag,
ROUND(
(dagomzet - LAG(dagomzet) OVER (ORDER BY verkoopdatum)) /
LAG(dagomzet) OVER (ORDER BY verkoopdatum) * 100, 2
) as groeipercentage
FROM verkopen
ORDER BY verkoopdatum;
LAG() haalt standaard de waarde uit de vorige rij op, maar je kunt ook verder terug kijken door een tweede parameter mee te geven. LAG(dagomzet, 7) zou de waarde van zeven rijen eerder ophalen, handig voor week-op-week vergelijkingen.
Voor seizoensanalyses combineer ik LAG() vaak met PARTITION BY om vergelijkingen te maken binnen dezelfde periode van verschillende jaren.
SELECT
verkoopdatum,
dagomzet,
LAG(dagomzet) OVER (
PARTITION BY MONTH(verkoopdatum), DAY(verkoopdatum)
ORDER BY YEAR(verkoopdatum)
) as zelfde_dag_vorig_jaar
FROM verkopen
WHERE MONTH(verkoopdatum) = 12 -- Alleen december
ORDER BY verkoopdatum;
Deze aanpak geeft me voor elke dag in december de omzet van diezelfde dag in het voorgaande jaar, perfect voor het analyseren van seizoenspatronen.
Geavanceerde window frame specificaties
Window functions worden nog krachtiger wanneer je specifieke frame boundaries definieert. De ROWS en RANGE clauses laten je precies bepalen welke rijen in je berekening worden meegenomen. Voor bewegende gemiddeldes over een specifiek aantal dagen schrijf ik vaak queries zoals deze.
SELECT
verkoopdatum,
dagomzet,
AVG(dagomzet) OVER (
ORDER BY verkoopdatum
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as zeven_dagen_gemiddelde
FROM verkopen
ORDER BY verkoopdatum;
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW zorgt ervoor dat voor elke rij de huidige rij plus de zes voorafgaande rijen worden gebruikt in de berekening. Dit geeft een glijdend gemiddelde over zeven dagen dat veel stabieler is dan dagelijkse fluctuaties.
Voor complexere scenario's waarin ik een venster wil definiëren op basis van een waardenbereik in plaats van een vast aantal rijen, schakel ik over naar RANGE. Dit is vooral nuttig bij tijdreeksdata waar rijen mogelijk niet voor elke dag bestaan.
SELECT
verkoopdatum,
dagomzet,
SUM(dagomzet) OVER (
ORDER BY verkoopdatum
RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW
) as dertig_dagen_totaal
FROM verkopen
ORDER BY verkoopdatum;
RANGE BETWEEN INTERVAL 30 DAY PRECEDING zorgt ervoor dat alle rijen binnen 30 dagen van de huidige datum worden meegenomen, ongeacht hoeveel rijen dat zijn. Dit is robuuster dan ROWS wanneer je dataset gaten heeft.
Window functions hebben mijn manier van databasequery's schrijven fundamentaal veranderd. Waar ik vroeger complexe subqueries en tijdelijke tabellen nodig had, kan ik nu elegante oplossingen schrijven die zowel beter leesbaar zijn als sneller uitvoeren. Het kost even tijd om te wennen aan het denken in windows en partities, maar eenmaal onder de knie zijn ze een onmisbaar onderdeel van mijn SQL toolkit geworden.