Historisch gezien stonden data-engineers voor de uitdaging om ontbrekende sensorwaarnemingen aan te pakken door records te verwijderen of constante imputatie te gebruiken, wat beide de analytische resultaten vervormt. Lineaire interpolatie is ontstaan als de statistisch voorkeur methode voor continue fysieke processen, waarbij een rechte lijn tussen twee bekende datapunt wordt verondersteld.
Het probleem vereist het reconstrueren van NULL-waarden in een geordende reeks door een proportionele waarde te berekenen op basis van de temporele afstand van de omliggende geldige metingen. Dit moet worden bereikt zonder gebruik te maken van procedurele lussen of de tabel aan zichzelf te koppelen, waarbij een pure set-gebaseerde logica wordt gehandhaafd.
De oplossing maakt gebruik van voorwaardelijke windowfuncties om de grensankers vast te stellen. MAX met een bereik dat zich uitstrekt tot alle voorafgaande rijen vangt de meest recente niet-null waarde en tijdstempel vóór het huidige gat. Omgekeerd vangt MIN met een bereik dat zich uitstrekt tot alle volgende rijen de volgende niet-null waarde en tijdstempel na het gat. De interpolatieformule berekent vervolgens het gewogen gemiddelde op basis van de verhouding van verstreken tijd tussen deze grenzen.
WITH boundaries AS ( SELECT device_id, reading_time, reading, MAX(CASE WHEN reading IS NOT NULL THEN reading_time END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS UNBOUNDED PRECEDING) as prev_time, MAX(CASE WHEN reading IS NOT NULL THEN reading END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS UNBOUNDED PRECEDING) as prev_val, MIN(CASE WHEN reading IS NOT NULL THEN reading_time END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_time, MIN(CASE WHEN reading IS NOT NULL THEN reading END) OVER (PARTITION BY device_id ORDER BY reading_time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as next_val FROM sensor_readings ) SELECT device_id, reading_time, COALESCE( reading, prev_val + (next_val - prev_val) * (EXTRACT(EPOCH FROM (reading_time - prev_time)) / NULLIF(EXTRACT(EPOCH FROM (next_time - prev_time)), 0)) ) as interpolated_reading FROM boundaries;
Een farmaceutisch bedrijf bewaakte vaccinkoelopslagunits met behulp van IoT-temperatuursensoren die elke minuut rapporteren. Netwerkcongestie tijdens piekbedrijfsuren veroorzaakte sporadische NULL-waarnemingen die 3-5 minuten duurden. Regelgevende compliance volgens de FDA vereiste een complete temperatuurgeschiedenis zonder gaten, terwijl eenvoudige verwijdering de protocollen voor continue monitoring schond.
Verwijdering van NULL-records werd eerst overwogen. Deze benadering handhaafde de feitelijke integriteit door alleen waargenomen waarden op te slaan. Het creëerde echter temporele discontinuïteiten die duurafhankelijke aggregaties verstoorden, zoals het berekenen van de totale tijd boven kritieke temperatuurgrenzen, waardoor de dataset niet voldeed aan de auditnormen.
Last Observation Carried Forward (LOCF) werd geëvalueerd als een computationeel triviale alternatieve aanpak met behulp van LAST_VALUE windowfuncties. Hoewel deze methode performant was, ging ze ervan uit dat de temperatuur constant bleef tijdens de uitval, wat de thermodynamische principes van geleidelijke thermische drift in koelsystemen schond, waardoor aanzienlijke bias werd geïntroduceerd in variantie- en trendanalyse.
Lineaire interpolatie werd uiteindelijk geselecteerd. Deze benadering modeleerde realistische thermische inertie tussen metingen, behield eerste-orde trends en bood wiskundig onderbouwde schattingen voor korte duur gaten die typisch zijn voor netwerkblips. Hoewel het een monotone lineaire verandering veronderstelde, bleek dit nauwkeurig te zijn voor de korte, stabiele periodes tussen compressorcylces in vaccinkoeling.
De implementatie reconstructeerde met succes 99,2% van de verloren waarnemingen tijdens Q3-compliance-audits. De geïnterpoleerde dataset behield temperatuurvariantie binnen 0,1°C van fysiek geregistreerde waarden, wat voldeed aan de FDA-vereisten zonder dure hardware-upgrades of redundante sensornetwerken te vereisen.
Hoe gaat u om met NULL-waarden die aan het begin of het einde van een partitie optreden, waar slechts één grenswaarde bestaat voor interpolatie?
Kandidaten implementeren vaak de interpolatieformule zonder rekening te houden met randgevallen, waardoor de berekening NULL retourneert voor leidende of achterblijvende gaten waar ofwel prev_val of next_val niet gedefinieerd is. De oplossing vereist dat de logica in een CASE-uitdrukking wordt gewikkeld: wanneer prev_time IS NULL, gebruik dan next_val (achterwaartse extrapolatie); wanneer next_time IS NULL, gebruik dan prev_val (voorwaartse extrapolatie of LOCF); anders toepassen de volledige interpolatieformule. Dit zorgt ervoor dat de query resultaten voor de hele dataset retourneert in plaats van alleen het binnenste bereik.
Waarom kan de uitdrukking (next_val - prev_val) / (next_time - prev_time) mogelijk falen in strikte ANSI SQL, en welke wijziging zorgt voor numerieke correctheid?
ANSI SQL datetime-arithmetiek retourneert een INTERVAL-type, geen numerieke scalar. Pogingen tot aritmetische deling tussen intervallen of het mengen van intervallen met decimalen kan type-incompatibiliteitsfouten veroorzaken. Bovendien zou gehele deling fractionele seconden trunceren, wat precisie verwoest. Kandidaten moeten de epoch-seconden extraheren met behulp van EXTRACT(EPOCH FROM (next_time - prev_time)) om een numerieke weergave te verkrijgen. Ze moeten ook de noemer wikkelen in NULLIF(..., 0) om deling door nul-fouten te voorkomen wanneer opeenvolgende geldige waarnemingen identieke tijdstempels delen door batchinvoeringen.
Wat is het fundamentele verschil in computationele complexiteit tussen deze windowfunctie-aanpak en een zelf-joinmethode die gebruik maakt van correlerende subqueries, en waarom is dit belangrijk voor streamingdatasets met hoge frequentie?
De windowfunctieaanpak voert uit in O(n log n) tijd, gedomineerd door de sorteerversie, en handhaaft een lineair geheugengebruik in verhouding tot de partitiegrootte. Een zelf-joinbenadering met scalare subqueries (bijv. het vinden van de volgende niet-null via MIN(time) WHERE time > current) degradeert naar O(n²) omdat elke rij de tabel scant naar zijn buren, wat ernstige geneste lus-joins creëert. Voor telemetry met hoge frequentie die miljoenen rijen genereert, benut de windowfunctie methode geordende indexscans en enkele doorvoeren, terwijl zelf-joins cartesiaanse producten en geheugensplitsingen veroorzaken. Kandidaten over het hoofd zien vaak dat UNBOUNDED FOLLOWING-frames schijfruimte kunnen vereisen voor grote partitites, hoewel dit asymptotisch superieur blijft aan quadratische complexiteit.