Voorbereiding op SQL-sollicitatiegesprekken · Les

De truc met het verschil van rijnummers

ROW_NUMBER van een reeks aftrekken om opeenvolgende waarden in eilanden te groeperen

Les 2 van 413 stappen

De truc met het verschil van rijnummers is een gratis Voorbereiding op SQL-sollicitatiegesprekken-les op CoddyKit. Dit is les 2 van 4. Je kunt de volledige les hieronder gratis lezen en daarna in de browser praktisch oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Voorbereiding op SQL-sollicitatiegesprekken. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Voorbereiding op SQL-sollicitatiegesprekken bevat in totaal 4 lessen.

De elegantste eilandsleutel

De truc met het verschil tussen rijnummers is de techniek die interviewers het liefst zien voor eilanden met opeenvolgende gehele getallen of datums. Je berekent de groepssleutel met één aftrekking, zonder LAG en zonder cumulatieve som.

Het hele idee is: trek een ROW_NUMBER af van de waarde zelf. In elke reeks opeenvolgende waarden nemen zowel de waarde als het rijnummer bij elke stap precies met 1 toe, waardoor hun verschil constant blijft over de hele reeks. Die constante is je eilandsleutel.

Waarom het verschil constant blijft

Denk aan twee aangrenzende rijen in een opeenvolgende reeks. Van de ene naar de volgende rij neemt de waarde met 1 toe en neemt het rijnummer met 1 toe. Trek je ze van elkaar af, dan vallen de twee keer +1 tegen elkaar weg, zodat value - row_number niet verandert.

Maar zodra er een gat is, springt de waarde met meer dan 1 omhoog, terwijl het rijnummer nog steeds maar met 1 toeneemt. Het verschil verschuift naar een nieuwe constante waarde. Precies die verschuiving scheidt het ene eiland van het volgende.

Dit zien in onze gegevens

Denk terug aan de inlogdagen 1, 2, 3, 7, 8, 10. Zet het rijnummer en het verschil naast elkaar:

day 1, rn 1, diff 0
day 2, rn 2, diff 0
day 3, rn 3, diff 0
day 7, rn 4, diff 3
day 8, rn 5, diff 3
day 10, rn 6, diff 4

De verschillen (0,0,0,3,3,4) delen de rijen perfect op in de drie eilanden. Hetzelfde verschil betekent hetzelfde eiland.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Samenvouwen tot eilanden

Met het verschil als groepssleutel is de uiteindelijke query een standaardbewerking om de gegevens samen te vouwen. Stop het verschil in een CTE en gebruik GROUP BY ervoor:

Dit levert dezelfde drie eilanden op als eerder, maar de SQL is korter en duidelijker dan de versie met LAG en een cumulatieve som. Voor reeksen met gehele getallen of gelijke stappen is dit de aanpak waar je als eerste naar moet grijpen.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

De adder: waarden moeten telkens met één toenemen

De eenvoudige verschiltruc gaat ervan uit dat de reeks per stap met precies 1 toeneemt. Dat klopt voor aaneengesloten gehele getallen en opeenvolgende kalenderdagen, maar de truc gaat niet meer op als je waarden met een andere vaste stap toenemen of als er duplicaten voorkomen.

Zelfs de even waarden 2,4,6,8 lijken dan gaten te bevatten bij een aftrekking van waarde min rijnummer.
Duplicaten verstoren de uitlijning, omdat het rijnummer blijft oplopen terwijl de waarde dat niet doet.

Als je deze beperking kent en weet hoe je die oplost, beheers je meer dan alleen een uit het hoofd geleerde truc.

Reeksen met vaste stappen herstellen

Als waarden met een bekende constante k toenemen in plaats van met 1, normaliseer je ze eerst: deel de waarde door k (of gebruik value / k voor gehele getallen), zodat elke stap weer 1 is. Trek daarna het rijnummer af.

Gebruik bijvoorbeeld voor even getallen met een stap van 2 day_no / 2 - ROW_NUMBER(). De genormaliseerde waarde neemt nu per opeenvolgend element met 1 toe, waardoor de eigenschap van het constante verschil terugkeert.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Toepassen op datums

Datums zijn de meest voorkomende praktische variant. Je kunt kalenderdatums niet rechtstreeks van een rijnummer aftrekken, dus zet je de datum eerst om in een dagentelling. Trek in Postgres een vaste referentiedatum af om een geheel aantal dagen te krijgen en pas daarna dezelfde truc toe.

Omdat opeenvolgende kalenderdagen 1 van elkaar verschillen, is het verschil tussen de dagentelling en het rijnummer binnen een eiland opnieuw constant.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Datumverschillen in verschillende dialecten

De stap van datum naar geheel getal verschilt per database-engine, en interviewers waarderen kennis van verschillende dialecten:

Postgres: trek een datumliteral af: login_date - DATE '2000-01-01' levert een geheel getal op.
MySQL: gebruik DATEDIFF(login_date, '2000-01-01').
SQL Server: gebruik DATEDIFF(day, '2000-01-01', login_date).

Bij sommige engines is er een nog slimmere aanpak: trek met intervalrekenkunde rechtstreeks ROW_NUMBER dagen van de datum af en gebruik daarna GROUP BY op de resulterende ankerdatum.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Partities per groep toevoegen

Voor eilanden per gebruiker partitioneer je het rijnummer op de groepskolom. Cruciaal is dat de groepssleutel daarna ook de partitioneringskolom moet bevatten, omdat twee verschillende gebruikers toevallig dezelfde verschilwaarde kunnen opleveren.

Gebruik dus GROUP BY op zowel user_id als het berekende verschil. Als je user_id in de uiteindelijke GROUP BY vergeet, maak je een subtiele fout waar interviewers graag op letten.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Truc of LAG: welke gebruik je

Je hebt nu twee solide technieken in je gereedschapskist. Kies bewust:

Verschil van rijnummers: de kortste en duidelijkste aanpak voor reeksen met gelijkmatige stappen (aaneengesloten gehele getallen, opeenvolgende datums). De eerste keus wanneer aangrenzend betekent: 'verschilt met een constante waarde'.
LAG plus cumulatieve som: flexibeler wanneer aangrenzend niet betekent dat de numerieke stap vastligt, bijvoorbeeld bij 'dezelfde status als de vorige rij' of onregelmatige aangepaste regels.

Leg in het interview uit waarvoor je kiest en waarom; de redenering maakt meer indruk dan de syntaxis.

Dubbele waarden veilig afhandelen

Als een waarde kan worden herhaald en je toch één eiland per opeenvolgende reeks wilt, ontdubbel je de gegevens eerst met DISTINCT of een groeperingsstap, zodat het rijnummer één-op-één overeenkomt met de waarden. Je kunt ook DENSE_RANK gebruiken in plaats van ROW_NUMBER, zodat gelijke waarden dezelfde rang krijgen.

Vraag de interviewer altijd of duplicaten kunnen voorkomen; de juiste aanpak hangt ervan af of duplicaten binnen een reeks moeten meetellen als uitbreiding of genegeerd moeten worden.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Korte controle

Zorg dat je begrijpt waarom de truc werkt.

Beantwoord dit in de interactieve les

Samenvatting: de verschiltruc

Je hebt nu de eenvoudigste eilandssleutel:

Sleutelformule: value - ROW_NUMBER() OVER (ORDER BY value) is constant binnen elke opeenvolgende reeks.
Gebruik GROUP BY op het verschil om begin, einde en lengte te krijgen.
Voor reeksen met een vaste stap normaliseer je eerst door de stap te delen.
Voor datums zet je de waarden met de verschilfunctie van het dialect om in een geheel aantal dagen.
Per groep: gebruik PARTITION BY voor het rijnummer en neem de groepskolom op in de uiteindelijke GROUP BY.
Voorkom problemen met duplicaten met DISTINCT of DENSE_RANK.

Nu verleggen we de focus van eilanden naar de lege ruimtes: gaten vinden.

Gratis beginnen

Leer SQL met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 30
Lessen: 120

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “De truc met het verschil van rijnummers” gratis?

Ja — de volledige tekst van “De truc met het verschil van rijnummers” kun je hier gratis op het web lezen. Als je interactief wilt oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is, en de rest van de cursus Voorbereiding op SQL-sollicitatiegesprekken wilt ontgrendelen, kun je upgraden naar CoddyKit PRO. De cursus Voorbereiding op SQL-sollicitatiegesprekken bevat in totaal 4 lessen.

Wat leer ik in “De truc met het verschil van rijnummers”?

ROW_NUMBER van een reeks aftrekken om opeenvolgende waarden in eilanden te groeperen Je oefent met Voorbereiding op SQL-sollicitatiegesprekken door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met Voorbereiding op SQL-sollicitatiegesprekken te beginnen?

Ervaring vooraf is niet nodig. Voorbereiding op SQL-sollicitatiegesprekken op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 2 van 4.

Hoe lang duurt de les “De truc met het verschil van rijnummers”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over Voorbereiding op SQL-sollicitatiegesprekken?

Ja. Elke les over Voorbereiding op SQL-sollicitatiegesprekken bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

Een probleem met hiaten en eilanden herkennen
De truc met het verschil van rijnummers
Hiaten in een reeks vinden
Eilanden met datum- en statuswijzigingen

← Terug naar Voorbereiding op SQL-sollicitatiegesprekken