SQL leren: Window functions en ROW_NUMBER
Een nummer per rij
"Op het podium wil ik de jongste renners in de spotlights", zegt Griet. Daarvoor moet je ze nummeren, van jong naar oud. Met `GROUP BY` lukt dat niet: dan verdwijnen de renners in een groepje.
Een venster over de rijen
Een window function rekent iets uit over een reeks rijen, het venster, maar laat elke rij gewoon staan. Dat is het grote verschil met GROUP BY.
ROW_NUMBER() geeft elke rij een volgnummer. Na OVER zeg je tussen haakjes in welke volgorde er genummerd wordt: hier van oud naar jong.
SELECT naam, geboortedatum, ROW_NUMBER() OVER (ORDER BY geboortedatum) AS nr FROM renners| naam | geboortedatum | nr |
|---|---|---|
| Kasper Holm | 1996-04-21 | 1 |
| Jesper Kuipers | 1997-01-19 | 2 |
| Jens Moreels | 1997-11-23 | 3 |
| Louis Garnier | 1998-07-15 | 4 |
| Senne Pauwels | 1998-12-05 | 5 |
| Lars Vermeulen | 1999-03-14 | 6 |
| Thijs Bakker | 1999-08-28 | 7 |
| Arne Van Hecke | 2000-05-09 | 8 |
Het totaal naast elke rij
Ook SUM, COUNT en AVG kunnen een window function zijn. Met een lege OVER () is het venster de hele tabel: elke rij krijgt het totaal ernaast, en geen enkele rij verdwijnt.
Zo kan je per rij vergelijken met het geheel, iets wat met GROUP BY niet lukt.
SELECT aankomst, km, SUM(km) OVER () AS totaal FROM etappes| aankomst | km | totaal |
|---|---|---|
| Lennik | 164 | 701 |
| Lennik | 18 | 701 |
| Galmaarden | 178 | 701 |
| Pepingen | 185 | 701 |
| Halle | 156 | 701 |
Filteren op het nummer
WHERE loopt vóór de window functions: op dat moment bestaat het nummer nog niet. Daarom mag een window function niet in WHERE.
De oplossing: nummer eerst in een WITH of een subquery, en filter daarna op de kolom. Zo vind je de drie jongste renners.
WITH genummerd AS (
SELECT naam, geboortedatum, ROW_NUMBER() OVER (ORDER BY geboortedatum DESC) AS nr
FROM renners
)
SELECT naam, nr FROM genummerd WHERE nr <= 3| naam | nr |
|---|---|
| Ruben de Graaf | 1 |
| Ben Schmit | 2 |
| Milan Dhondt | 3 |
Probeer het zelf
Toon de aankomstplaats en de afstand van elke etappe, met een volgnummer van de langste (1) naar de kortste.
Klaar voor meer?
De volledige les heeft 6 oefeningen, van makkelijk naar uitdagend, met hints, punten en een reeks die je kan opbouwen.
Begin aan de les