Geschiedenis: Voordat ANSI SQL:2003 analytische functies introduceerde, was het invullen van spaarzame tijdreeksgegevens inefficiënt vanwege zelfverbindingen of procedurele cursors die rijen individueel verwerkten. Het LOCF-patroon is ontstaan in statistische pakketten zoals SAS en R, waar het doorgeven van de laatste bekende observatie een standaarddatacleaningstechniek is. Databaseleveranciers implementeerden later deze logica in SQL via venstervariabelen, met de IGNORE NULLS clausule die formeel werd vastgelegd in ANSI SQL:2011 specifiek om dergelijke hiaten declaratief aan te pakken.
Probleem: Sensornetwerken en financiële handelsystemen genereren vaak NULL-waarden als gevolg van transmissiefouten of niet-handelsuren. Eenvoudige LAG-functies falen omdat ze de onmiddellijke voorganger retourneren, die ook NULL kan zijn, wat hiaten in de berekende metrieken creëert. De uitdaging vereist het terugscannen door een geordende partitionering tot de meest recente niet-NULL-waarde te vinden, zonder gebruik te maken van zelfverbindingen die de prestaties kwadratisch verminderen.
Oplossing: Gebruik de LAST_VALUE venstervariabele met de optie IGNORE NULLS en een frame specificatie die zich uitstrekt van het begin van de partitionering tot de huidige rij. Deze configuratie instrueert de engine om een lopende buffer van niet-NULL-waarden bij te houden, waardoor effectief wordt teruggekeken door NULL's om de laatste geldige observatie op te halen. Voor systemen zonder IGNORE NULLS, wordt een workaround gebruikt waarbij COUNT van niet-NULL's wordt gebruikt om stabiele groepen te creëren, hoewel dit technisch gezien een subquery vereist.
SELECT device_id, reading_time, temperature, LAST_VALUE(temperature IGNORE NULLS) OVER ( PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS locf_temperature FROM sensor_readings;
Een gezondheidsanalyseplatform monitort continue glucoseniveaus voor diabetische patiënten met behulp van draagbare apparaten. Vanwege Bluetooth-interferentie arriveert ongeveer 12% van de metingen als NULL, maar clinici hebben volledige curves nodig voor insuline-doseringsberekeningen waar interpolatie medisch onveilig kan zijn. Nauwkeurige LOCF-logica is essentieel omdat ontbrekende waarden tijdens de slaap of maaltijden valse hypoglykemie waarschuwingen kunnen veroorzaken.
Oplossing A: Cursor-gebaseerde procedurele update. Een PL/SQL opgeslagen procedure doorloopt de patiëntrecords chronologisch, waarbij een sessievariabele wordt behouden om de laatste geldige glucosemeting op te slaan en NULL-rijen onmiddellijk bij te werken. Voordelen: compatibel met legacy Oracle versies voorafgaand aan ondersteuning voor venstervariabelen; gemakkelijk te begrijpen voor ontwikkelaars met imperatieve programmeerachtergronden. Nadelen: rij-voor-rij verwerking creëert overmatige I/O en tabelvergrendeling; het verwerken van 10 miljoen rijen duurt 45 minuten, waardoor real-time dashboards onmogelijk worden.
Oplossing B: Zelf-verbinding met gecorreleerde subquery. De query voert een left join uit om de maximale timestamp te vinden die kleiner is dan de huidige rij waar glucose IS NIET NULL, waardoor effectief de vorige waarde voor elke kloof wordt opgezocht. Voordelen: declaratieve SQL zonder procedurele code; functioneert op ANSI SQL-92 conforme systemen. Nadelen: O(n²) complexiteit veroorzaakt exponentiële vertraging; de query time-out na 6 uur op operationele datasets vanwege herhaalde volledige tabelscans.
Oplossing C: Venstervariabele met IGNORE NULLS. Implementeert LAST_VALUE(glucose IGNORE NULLS) gepartitioneerd op patiënt en geordend op tijd, met een enkele doorloop door de index. Voordelen: O(n log n) complexiteit wordt uitgevoerd in 28 seconden op dezelfde dataset van 10 miljoen rijen; minimale geheugenspiegel en geen vergrendelingsproblemen. Nadelen: vereist ondersteuning voor ANSI SQL:2011, wat een database-upgrade van de bestaande PostgreSQL 9.5 instantie vereist.
Het team koos Oplossing C nadat ze hadden vastgesteld dat de kosten voor de database-upgrade gerechtvaardigd waren door de 99% prestatieverbetering. De implementatie stelde real-time glucose-waarschuwingen in staat en verminderde het CPU-gebruik van de server met 94%. Dienovereenkomstig kon de kliniek met succes 50.000 gelijktijdige patiënten monitoren zonder vertraging of het missen van kritieke glucosepieken.
Vraag 1: Waarom retourneert LAST_VALUE zonder IGNORE NULLS NULL, zelfs wanneer vorige niet-NULL waarden in de partitionering bestaan?
Standaard evalueert LAST_VALUE het frame inclusief de huidige rij. Wanneer de huidige rij NULL bevat en het frame zich uitstrekt tot CURRENT ROW, ziet de functie deze NULL als de laatste waarde in het venster. Kandidaten veronderstellen ten onrechte dat de functie oneindig terugscant; echter, zonder IGNORE NULLS behandelt het NULL's als geldige waarden. Het vensterframe ROWS UNBOUNDED PRECEDING omvat de huidige rij, waardoor LAST_VALUE gelijk is aan de waarde van de huidige rij, tenzij expliciet is aangegeven dat NULL's genegeerd moeten worden.
Vraag 2: Hoe kun je LOCF implementeren in pre-2011 ANSI SQL zonder IGNORE NULLS, en wat is de logische fout in het gebruik van ROW_NUMBER verschillen in plaats van COUNT?
Je kunt COUNT(non_null_col) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) gebruiken om een groepsidentifier te maken die alleen incrementen wanneer het niet-NULL waarden tegenkomt. Alle daaropvolgende NULL's delen deze telling, waardoor een carry-groep ontstaat. Kandidaten proberen soms ROW_NUMBER() OVER (ORDER BY ...) af te trekken van ROW_NUMBER() OVER (PARTITION BY non_null_flag ORDER BY ...). Dit faalt omdat het nieuwe groepen creëert voor elke kloof tussen niet-NULL's in plaats van de vorige groep naar voren voort te zetten. De COUNT-methode werkt omdat het een stabiele identifier voor de hele laatste bekende waardeperiode produceert.
Vraag 3: Waarom kunnen de resultaten niet-deterministisch worden wanneer je RANGE in plaats van ROWS framing voor LOCF op tijdstempels met duplicaten gebruikt?
RANGE framing groepeert rijen met identieke ORDER BY waarden in groepsgenoten, waardoor ze als een enkele eenheid worden behandeld. Als meerdere sensorlezingen dezelfde milliseconde timestamp delen, kan RANGE UNBOUNDED PRECEDING hun fysieke volgorde niet onderscheiden. Wanneer sommige duplicaten NULL's bevatten en andere waarden, kan de venstervariabele willekeurig kiezen uit de groepsgenoten afhankelijk van het uitvoeringsplan. ROWS framing garandeert deterministische resultaten door de fysieke rijvolgorde te verwerken, waardoor de specifieke volgorde van invoegingen bepaalt welke waarde voorwaarts wordt doorgegeven. Deze onderscheiding is cruciaal voor gegevens van hoge frequentie handel waar microseconden ertoe doen.