Dubbele rijen vinden m.b.v. ROW_NUMBER()

Gepubliceerd op 27/2/2013

Sinds SQL Server 2005 bestaan er zogenaamde 'window functions'; functies die werken op (of met) een deel van de dataset tegelijk. Alsof je een raamwerk over de dataset heen legt en in elk 'venstertje' hetzelfde doet. Het is niet altijd direct duidelijk wat het praktische nut van deze functies is. Maar de laatste tijd gebruik ik de functie ROW_NUMBER() vaak om de data die gebruikt moet worden in een datawarehouse te controleren. Zo kun je met deze functie erg makkelijk dubbele rijen vinden vóórdat je ze gaat toevoegen.

 

Maar eerst even een stukje over ROW_NUMBER(). Zoals de naam al suggereert geeft deze functie een nummer aan elke rij. Klinkt simpel maar toch werkt

SELECT ROW_NUMBER()
FROM Klant

 

niet.

 

Dat komt omdat SQL (en jij waarschijnlijk) wil dat er elke keer als je het statement draait hetzelfde uitkomt. En om dat te garanderen moet SQL weten welke rij je nummer 1 wilt geven en wat de volgens rij is, enzovoorts. Daarom moet bij ROW_NUMBER() altijd aangegeven worden wat de ORDER BY is die je wilt gebruiken. Stel bijvoorbeeld dat je je klanten wilt nummer op alfabetische volgorde dan gebruik je:

SELECT ROW_NUMBER() OVER (ORDER BY KlantNaam) Rijnummer
, KlantNaam
FROM Klant

Maar zo is hij nog niet heel erg zinvol. Om een rijnummer te krijgen kun je immers ook een identity kolom gebruiken (of, in SQL Server 2012, een sequence). ROW_NUMBER() wordt pas interessant als we de clausule PARTITION BY gaan gebruiken. Met PARTITION BY kunnen we groepen definiëren (de eerder genoemde 'vensters') waarop de ROW_NUMBER() moet werken. Zo kunnen we dus een bijvoorbeeld rijnummer bepalen per land. Voor elke groep begint het nummer weer bij 1 en loopt op via de opgegeven ORDER BY:

SELECT ROW_NUMBER() OVER (PARTITION BY Land ORDER BY KlantNaam) Rijnummer
, KlantNaam
, Land
FROM Klant

Maar hoe is bovenstaande kennis nu te gebruiken om dubbele rijen te vinden? Om dat te doen moeten we ROW_NUMBER() combineren met een subquery. Eerst moeten we bepalen wat de unieke sleutel is van onze data. Welke gegevens verwachten we maar één keer in de dataset aan te treffen (dit wordt ook wel de 'business key' genoemd). Vervolgens maken we een select query en voegen we daaraan een ROW_NUMBER toe met een PARTITION BY op die unieke sleutel. Klopt onze veronderstelling dat de gegevens uniek zijn dan zou die ROW_NUMBER() dus alleen maar 1 op mogen leveren (want de ROW_NUMBER() begint per PARTITION BY opnieuw met tellen en we kiezen een PARTITION die maar één keer voorkomt). Om de dubbele rijen te vinden, hoeven we dus alleen maar te selecteren waar de ROW_NUMBER functie iets anders oplevert dan 1.

 

Stel bijvoorbeeld dat je een goed lopende webwinkel hebt waar bestellingen uit heel Europa binnen komen. De bestellingen worden opgeslagen in een tabel waarin de klant, de besteldatum, het artikel en het aantal opgeslagen wordt:

SELECT *
FROM Verkopen
ORDER BY Besteldatum

We willen hieruit een lijst van unieke klanten selecteren en we veronderstellen dat de combinatie KlantNaam en Land een klant uniek identificeert. Dan gebruiken we dus als functie: ROW_NUMBER() OVER (PARTITION BY KlantNaam, Land ORDER BY KlantNaam).

SELECT ROW_NUMBER() OVER (PARTITION BY KlantNaam, Land ORDER BY KlantNaam) Rijnummer
, KlantNaam
, Adres
, Land
FROM Verkopen

Om alle rijen te vinden die niet uniek te identificeren zijn door deze combinatie van velden gebruik je:

 

SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY KlantNaam, Land ORDER BY KlantNaam) Rijnummer
, KlantNaam
, Adres
, Land
FROM Verkopen
) alles
WHERE Rijnummer <> 1

Als we weten dat er dubbele rijen in de data voorkomen en we slechts één rij willen selecteren (bijvoorbeeld om de data op te schonen voor een INSERT) dan hoeven we alleen maar een handige sortering te kiezen (bijvoorbeeld op een datum) en alle rijen te kiezen met rijnummer 1.

 

Stel bijvoorbeeld dat we er toch van overtuigd blijven dat een KlantNaam en Land een klant uniek identificeert. We willen een lijst maken van de klanten en het meest recente adres van de klant selecteren.

SELECT *
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY KlantNaam, Land ORDER BY Besteldatum DESC) Rijnummer
, KlantNaam
, Adres
, Land
FROM Verkopen
) alles
WHERE Rijnummer = 1

Door de ORDER BY Besteldatum DESC krijgt de rij met de meest recente besteldatum rijnummer 1. Door de selectie op Rijnummer = 1 vallen de dubbele rijen er vanzelf uit. En zo heb je een lijstje met de klanten en hun meest recente adres.

 

(Overigens is het natuurlijk 'best practice' om in een ETL programma dat een DWH vult de dubbele rijen niet onopgemerkt verloren te laten gaan, maar ze ergens te registreren. Maar dat is een ander verhaal...)

 

 

Terug naar Artikelen

Stuur een e-mail