ROWS versus RANGE bij framing

Als je eenmaal een lopend totaal kunt schrijven, is de natuurlijke vervolgvraag: "Wat is het verschil tussen ROWS en RANGE in een windowframe?" Dit is een scherpe graadmeter voor kennis op middenniveau. Veel kandidaten gebruiken frames dagelijks zonder ooit te merken dat deze twee sleutelwoorden zich anders gedragen.

Beide definiëren de verzameling rijen die het aggregaat ziet, maar ze tellen die verzameling op fundamenteel verschillende manieren. Als je dit goed beheerst, val je op.

ROWS is positioneel. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW betekent letterlijk: de huidige rij plus de twee fysieke rijen die er direct boven staan in de geordende reeks.

Het maakt niet uit of aangrenzende rijen dezelfde ORDER BY-waarde hebben. Drie rijen betekent drie rijen, zonder uitzonderingen. Dit is de framekeuze die je bijna altijd wilt gebruiken voor voortschrijdende gemiddelden en strikte lopende totalen.

RANGE is gebaseerd op waarden. De functie neemt elke rij op waarvan de ORDER BY-waarde binnen een logisch bereik van de waarde van de huidige rij valt, in plaats van een vast aantal rijen te tellen.

Het belangrijkste gevolg: met RANGE worden alle rijen met dezelfde ORDER BY-waarde behandeld als één groep van gelijkwaardige rijen. Ze krijgen allemaal hetzelfde frame en daardoor hetzelfde resultaat.

Stel dat twee verkopen plaatsvinden op 2024-03-02, met bedragen 30 en 40, voorafgegaan door 2024-03-01 met een bedrag van 100.

• Met RANGE tot en met de huidige rij: beide rijen van 03-02 zijn gelijkwaardig, dus beide tonen 100 + 30 + 40 = 170.
• Met ROWS tot en met de huidige rij: de eerste rij van 03-02 toont 130 en de tweede 170, omdat elke rij het frame één positie verder uitbreidt.

Dezelfde gegevens, andere getallen. Dat verschil bij gelijke waarden vormt de kern van de vraag.

Onthoud dat OVER (ORDER BY ...) zonder expliciet frame standaard RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gebruikt.

Dit betekent dat een eenvoudig lopend totaal stilzwijgend RANGE gebruikt. Bij unieke sorteersleutels is dat identiek aan ROWS, waardoor de fout verborgen blijft. Zodra er dubbele sorteerwaarden voorkomen, voegt je lopende totaal gelijke rijen ongemerkt samen. Daarom schrijven ervaren ontwikkelaars ROWS expliciet op.

RANGE kan een verschuiving op basis van een waarde gebruiken, niet alleen UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW over een datum- of numerieke kolom bevat elke rij waarvan de waarde maximaal 7 eenheden van de huidige waarde afwijkt.

Bij datums levert dit een echt venster van de "laatste 7 dagen" op, waarbij ontbrekende dagen correct worden overgeslagen. ROWS 7 PRECEDING zou daarentegen de vorige 7 rijen pakken, ongeacht hiaten. Als je deze nuance kent, geef je een sterk antwoord.

Het omgekeerde geldt ook: ROWS heeft geen begrip van hiaten in de gegevens. Als je verkooptabel geen weekenden bevat, beslaat ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 7 geregistreerde dagen, die in werkelijkheid twee kalenderweken kunnen omvatten.

De keuze hangt dus af van je bedoeling: de laatste N records betekent ROWS; de laatste N eenheden van een waarde (dagen, euro's) betekent RANGE met een verschuiving.

Een eerlijk antwoord in een sollicitatiegesprek benoemt verschillen in ondersteuning:

• PostgreSQL ondersteunt RANGE met verschuivingen (sinds versie 11) en ondersteunt ROWS volledig.
• SQL Server ondersteunt ROWS en RANGE, maar RANGE alleen met UNBOUNDED/CURRENT ROW, niet met numerieke verschuivingen.
• MySQL 8 ondersteunt beide, met beperkte typen voor verschuivingen bij RANGE.

Als een numerieke verschuiving bij RANGE niet beschikbaar is, boots je die na met een koppeling van een tabel met zichzelf of met een gegenereerde kalender. Als je dit noemt, laat je zien dat je ook aan productieomgevingen denkt.

Er is een minder bekende derde frame-modus: GROUPS. Deze telt groepen met gelijke sorteerwaarden in plaats van rijen of waarden. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW bevat de huidige groep met gelijke waarden en de groep ervoor.

Deze modus is zelden nodig, maar als je hem noemt wanneer iemand vraagt "zijn er nog andere framemodi?", toon je echte diepgang. PostgreSQL 11 en hoger en enkele andere systemen ondersteunen deze modus.

Vat het hele onderwerp samen in één zin die je in een sollicitatiegesprek kunt zeggen:

"Gebruik ROWS als ik een vast aantal fysieke rijen bedoel, gebruik RANGE als ik een logisch bereik van de sorte waarde bedoel, en onthoud dat gelijke waarden ze van elkaar laten afwijken, omdat het standaardbereik gelijke waarden samenneemt."

Met die ene zin beantwoord je de vraag volledig en zelfverzekerd.

Als je beide frame-indelingen in één query zet, wordt het verschil zichtbaar. Voer deze query uit op gegevens met dubbele datums en vergelijk de twee kolommen rij voor rij.

Bij unieke datums zijn de kolommen exact gelijk. Bij gelijke datums herhaalt de kolom met RANGE hetzelfde totaal voor alle gelijke waarden, terwijl de kolom met ROWS stap voor stap oploopt.

ROWS telt fysieke rijen; RANGE telt op basis van de logische waarde van de sorteerkolom en behandelt gelijke waarden als een groep. Het standaardframe van OVER (ORDER BY ...) is RANGE. Daarom kan een eenvoudig lopend totaal dubbele sorteerwaarden als één groep behandelen.

Kies ROWS voor "de laatste N records" en bewegende vensters die ongevoelig zijn voor hiaten. Kies RANGE met een verschuiving voor "de laatste N dagen/euro's" waarbij hiaten meetellen. Weten dat GROUPS bestaat, is een bonus. Hierna gebruiken we frames met ROWS om bewegende gemiddelden te bouwen.