Groepen vinden m.b.v. ROW_NUMBER()

Gepubliceerd op 10/4/2013

Een tijdje geleden heb ik nog een geweldige toepassing gevonden van ROW_NUMBER(), de functie waar ik in februari nog een blog over schreef. Deze functie kun je gebruiken om groepen van gegevens te vinden. De credits voor dit stuk moeten gaan naar Itzik Ben-Gan die in het boek 'SQL Server MVP Deep Dives' een hoofdstuk geschreven heeft over 'gaps and islands'. De onderstaande oplossing is door hem bedacht en is zó mooi dat ik hem graag voor jullie beschrijf.

 

Stel dat we verhuisgegevens in een tabel opslaan en dat iemand een aantal keren is verhuisd:

SELECT *
FROM Verhuizing
WHERE KlantID = 1
ORDER BY Begindatum

We willen weten in welke periodes Viktor in welk land gewoond heeft. Als mens zie je dat meteen:

Viktor heeft van januari 2008 tot juli 2010 in Nederland gewoond, daarna van juli 2010 tot maart 2012 in België, dan eventjes in Nederland, van december 2012 tot februari 2013 in Duitsland en nu weer in Nederland.

 

Maar hoe vind je dit met SQL? Het lijkt logisch om MIN(Begindatum) en MAX(Einddatum) per land te gebruiken. Maar dat zou deze resultaten geven:

SELECT Land, MIN(Begindatum) StartPeriodeInLand, MAX(Einddatum) EindePeriodeInLand
FROM Verhuizing
WHERE KlantID = 1
GROUP BY Land

De resultaten voor België en Duitsland kloppen, maar de periode in Nederland overlapt nu de periode in België. We kunnen dus geen gebruik maken van MIN() en MAX() per land omdat deze persoon vaker in Nederland woont. (Er wordt overigens ook geen rekening gehouden met het feit dat de Einddatum van het laatste record leeg is.)

 

We moeten de landen groeperen maar tegelijkertijd rekening houden met de bijbehorende datum. Dat kunnen we doen door twee ROW_NUMBER() functies te gebruiken: één ROW_NUMBER() per Begindatum en één per Land én Begindatum. Omdat de Begindatum uniek is en omdat we deze datum in beide ROW_NUMBERs gebruiken is het verschil tussen de beide ROW_NUMBERs per land per periode altijd hetzelfde:

SELECT *
,
ROW_NUMBER() OVER (PARTITION BY KlantID ORDER BY Begindatum) RowNr1
, ROW_NUMBER() OVER (PARTITION BY KlantID ORDER BY Land, Begindatum) RowNr2
, ROW_NUMBER() OVER (PARTITION BY KlantID ORDER BY Begindatum)
   -
ROW_NUMBER() OVER (PARTITION BY KlantID ORDER BY Land, Begindatum) Verschil
FROM Verhuizing
WHERE KlantID = 1
ORDER BY Begindatum

En die wetenschap kunnen we gebruiken om de data te groeperen. Daarna kunnen we wél met MIN() en MAX() de eerste en de laatste datum van deze nieuwe groepering bepalen:

WITH cte AS (
SELECT *
,
ROW_NUMBER() OVER (PARTITION BY KlantID ORDER BY Begindatum)
   -
ROW_NUMBER() OVER (PARTITION BY KlantID ORDER BY Land, Begindatum) Verschil
FROM Verhuizing
WHERE KlantID = 1
)
SELECT Land, MIN(Begindatum) StartPeriodeInLand, MAX(Einddatum) EindePeriodeInLand
FROM cte
GROUP BY Land, Verschil
ORDER BY StartPeriodeInLand

En dat geeft ons de periode per land rekening houdend met het feit dat er verschillende perioden bestaan waarin dezelfde data (NL) herhaald wordt.

 

De truc is dat je de partitionering (PARTITION BY) gelijk houdt, maar dat je m.b.v. de ORDER BY in de beide ROW_NUMBER() functies de groepen creëert. En dat je vervolgens een GROUP BY het verschil doet.

 

Zo simpel als je het eenmaal weet...

 

Terug naar Artikelen

Stuur een e-mail