Construe een query die een tijdsgewogen gemiddelde berekent van onregelmatige sensorwaarden, waarbij elke waarde wordt gewogen op basis van de duur dat deze geldig blijft tot de volgende sample, uitsluitend gebruikmakend van ANSI SQL vensterfuncties zonder procedurele logica.

Antwoord op de vraag

Geschiedenis van de vraag

Tijdsgewogen gemiddelden zijn ontstaan als een cruciale metriek in industriële IoT en financiële tijdreeksanalyse. Eenvoudige rekenkundige gemiddelden vervormen de werkelijkheid omdat sensorwaarden aanhouden tot de volgende meting. Voor de ANSI SQL:2003 standaard vereisen het berekenen van deze gemiddelden procedurele cursors of dure zelfverbindingen. Deze methoden presteerden met een tijdcomplexiteit van O(n²).

De introductie van LEAD en LAG vensterfuncties heeft dit domein gerevolutioneerd. Ze maakten berekeningen van intervallen in één keer, set-gebaseerde berekeningen mogelijk die draaien in O(n) tijd. Dit maakt realtime-analyse van miljarden rijen haalbaar binnen de database-laag.

Het probleem

Gegeven een tabel readings met de kolommen device_id, ts (timestamp) en value, is het doel een gewogen gemiddelde te berekenen. Elke rij moet proportioneel bijdragen aan de tijdsdelta tot de volgende meting. Wiskundig gezien is dit $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.

De laatste rij presenteert een grensvoorwaarde. Het heeft geen volgende timestamp, dus zijn interval moet worden gedefinieerd als nul, geëxtrapoleerd naar een actuele tijd, of begrensd op een bekende eindtijd. De oplossing moet cursors, gebruikersgedefinieerde functies of zelfverbindingen vermijden om puur declaratief te blijven.

De oplossing

Gebruik de LEAD vensterfunctie om de volgende timestamp in de huidige rij te projecteren. Bereken het tijdsverschil om het gewicht af te leiden. Pas vervolgens standaard gewogen gemiddelde formules toe.

WITH weighted AS (
  SELECT
    device_id,
    value,
    ts,
    COALESCE(
      EXTRACT(EPOCH FROM (LEAD(ts) OVER (PARTITION BY device_id ORDER BY ts) - ts)),
      0
    ) AS duration_seconds
  FROM readings
)
SELECT
  device_id,
  SUM(value * duration_seconds) / NULLIF(SUM(duration_seconds), 0) AS time_weighted_avg
FROM weighted
GROUP BY device_id;

Deze aanpak gebruikt PARTITION BY om ervoor te zorgen dat het venster per apparaat opnieuw instelt. Dit voorkomt het door elkaar heen lopen van timestamps van verschillende sensoren. De COALESCE handelt de terminal rij af door nul gewicht toe te wijzen, waardoor deze effectief wordt uitgesloten van de noemer.

Situatie uit het leven

Een farmaceutische productie lijn monitort 200 bioreactors. Elke reactor geeft temperatuurgegevens vrij op onregelmatige intervallen—elke 10 seconden tijdens verwarmingsfasen, maar elke 30 minuten tijdens stilstand. Het kwaliteitsteam vereist een dagelijks tijdsgewogen gemiddelde om naleving te waarborgen. Een eenvoudig gemiddelde zou de snelle verwarmingssamples te veel wegen en de stabiele onderhoudstijden te weinig, wat gevaarlijke temperatuuruitstapjes zou kunnen verbergen.

Een voorgestelde oplossing hield in dat alle gegevens in een Python pandas DataFrame werden geëxtraheerd. Ingenieurs zouden diff() op timestamps berekenen en het gewogen gemiddelde berekenen. Hoewel flexibel, was deze aanpak belastend voor het netwerk door het overdragen van gigabytes aan gegevens. Het liet ook de analytische werkplek crashen tijdens het verwerken van kwartaalrapporten die 90 dagen aan gegevens met hoge frequentie besloegen.

Een andere alternatieve oplossing gebruikte een gecorreleerde subquery om de MIN(ts) groter dan de huidige rij voor elk apparaat te vinden. Dit werkte correct op testsets van 1.000 rijen. Echter, het vertoonde kwadratische degradatie, waarbij het 45 minuten in beslag nam voor de volledige geschiedenis van één reactor.

Het team koos voor de ANSI SQL vensterfunctie benadering. Door de berekening binnen de PostgreSQL cluster te houden, maakte de query gebruik van parallelle volgordescans en vermijdde netwerkoverhead. De uiteindelijke implementatie verwerkte 50 miljoen rijen over alle reactors in minder dan 12 seconden. Dit maakte realtime dashboardupdates mogelijk waarmee operators thermische drift binnen enkele minuten in plaats van uren konden opmerken.

Wat kandidaten vaak missen

Hoe ga je om met de laatste observatie in elke partition waar geen volgende timestamp bestaat om het intervalgewicht te definiëren?

Kandidaten vergeten vaak de grensvoorwaarde. Dit zorgt ervoor dat het interval van de terminal rij als NULL wordt geëvalueerd, wat SQL-aggregaten negeert. Als gevolg hiervan wordt de bijdrage van de laatste meting weggegooid, wat het gemiddelde vertekent. De correcte aanpak gebruikt COALESCE om ofwel nul of EXTRACT(EPOCH FROM (boundary_time - ts)) te substitueren als het gemiddelde moet worden verlengd naar een bekende eindtijd zoals CURRENT_TIMESTAMP.

Waarom stelt de formule SUM(value * duration) / SUM(duration) wiskundig het tijdsgewogen gemiddelde voor, en wat gebeurt er als je in plaats daarvan AVG(value) gebruikt?

Dit berekent het gewogen rekenkundige gemiddelde waar duur als het gewicht $w_i$ fungeert. Kandidaten verwarren dit vaak met een geometrisch gemiddelde of proberen AVG(value * duration) te gebruiken, wat een som van producten zonder normalisatie oplevert. Het gebruik van AVG(value) behandelt elke rij gelijk, ervan uitgaande dat de tijdstappen uniform zijn, wat in tegenspraak is met de eis dat langdurige waarden een grotere invloed hebben.