Geschichte der Frage
Zeitgewichtete Durchschnitte sind als kritische Kennzahl in der industriellen IoT und der Analyse von Finanzzeitreihen aufgetaucht. Einfache arithmetische Mittel verzerren die Realität, da Sensorwerte bis zur nächsten Messung bestehen bleiben. Vor dem ANSI SQL:2003 Standard erforderte die Berechnung dieser Durchschnitte prozedurale Cursors oder teure Selbst-Joins. Diese Methoden hatten eine Zeitkomplexität von O(n²).
Die Einführung der Fensterfunktionen LEAD und LAG hat dieses Gebiet revolutioniert. Sie ermöglichten einstufige, mengenbasierte Intervallberechnungen, die in O(n) Zeit laufen. Dadurch sind Echtzeitanalysen von Milliarden von Zeilen innerhalb der Datenbankebene möglich.
Das Problem
Gegeben ist eine Tabelle readings mit den Spalten device_id, ts (Zeitstempel) und value, das Ziel ist es, einen gewichteten Durchschnitt zu berechnen. Jede Zeile muss proportional zum Zeitdelta bis zur nächsten Messung beitragen. Mathematisch ist dies $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.
Die letzte Zeile stellt eine Grenzbedingung dar. Sie hat keinen nachfolgenden Zeitstempel, sodass ihr Intervall entweder als null, extrapoliert auf eine aktuelle Zeit oder auf eine bekannte Endzeit begrenzt definiert werden muss. Die Lösung muss Cursors, benutzerdefinierte Funktionen oder Selbst-Joins vermeiden, um rein deklarativ zu bleiben.
Die Lösung
Verwenden Sie die Fensterfunktion LEAD, um den nächsten Zeitstempel in die aktuelle Zeile zu projizieren. Berechnen Sie die Epochendifferenz, um das Gewicht abzuleiten. Wenden Sie dann die standardmäßigen gewichteten Durchschnittsformeln an.
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;
Dieser Ansatz verwendet PARTITION BY, um sicherzustellen, dass sich das Fenster pro Gerät zurücksetzt. Dies verhindert, dass Zeitstempel von unterschiedlichen Sensoren sich überlagern. Der COALESCE-Befehl behandelt die Terminalzeile, indem er das Gewicht auf null setzt, wodurch sie effektiv aus dem Nenner ausgeschlossen wird.
Eine pharmazeutische Produktionslinie überwacht 200 Bioreaktoren. Jeder gibt Temperaturdaten in unregelmäßigen Abständen aus—alle 10 Sekunden während Heizphasen, aber alle 30 Minuten während der Leerlaufzeiten. Das Qualitätsteam benötigte einen täglichen zeitgewichteten Durchschnitt, um die Einhaltung sicherzustellen. Ein einfacher Durchschnitt würde die schnellen Heizproben übergewichten und die stabilen Haltezeiten untergewichten, was potenziell gefährliche Temperaturschwankungen maskieren könnte.
Eine vorgeschlagene Lösung bestand darin, alle Daten in einen Python pandas DataFrame zu extrahieren. Ingenieure würden diff() auf Zeitstempel berechnen und den gewichteten Durchschnitt ermitteln. Obwohl flexibel, übertrug dieser Ansatz Gigabytes an Daten über das Netzwerk. Außerdem stürzte der Analyse-Computer ab, als Berichte für den Quartalsabschluss, die sich über 90 Tage mit hochfrequenten Daten erstreckten, verarbeitet wurden.
Eine weitere Alternative verwendete eine korrelierte Unterabfrage, um den MIN(ts) zu finden, der größer als die aktuelle Zeile für jedes Gerät ist. Diese führte korrekt bei Testmengen von 1.000 Zeilen aus. Sie zeigte jedoch quadratisches Abfallen und benötigte 45 Minuten für die vollständige Historie eines einzelnen Reaktors.
Das Team wählte den Ansatz mit den ANSI SQL-Fensterfunktionen. Durch die Berechnung innerhalb des PostgreSQL-Clusters nutzte die Abfrage parallele sequentielle Scans und vermied Netzwerküberlastung. Die endgültige Implementierung verarbeitete 50 Millionen Zeilen über alle Reaktoren in weniger als 12 Sekunden. Dadurch wurden Echtzeit-Dashboard-Updates ermöglicht, die es den Betreibern ermöglichten, thermische Abweichungen innerhalb von Minuten anstelle von Stunden zu erkennen.
Wie gehen Sie mit der letzten Beobachtung in jeder Partition um, in der kein nachfolgender Zeitstempel vorhanden ist, um das Intervallgewicht zu definieren?
Kandidaten ignorieren häufig die Grenzbedingung. Dies führt dazu, dass das Intervall der Terminalzeile als NULL ausgewertet wird, was die SQL-Aggregationen ignorieren. Folglich wird der Beitrag der letzten Messung gestrichen, was den Durchschnitt verzerrt. Der richtige Ansatz verwendet COALESCE, um entweder null oder EXTRACT(EPOCH FROM (boundary_time - ts)) zu ersetzen, wenn der Durchschnitt auf eine bekannte Endzeit wie CURRENT_TIMESTAMP ausgeweitet werden muss.
Warum repräsentiert die Formel SUM(value * duration) / SUM(duration) mathematisch den zeitgewichteten Durchschnitt, und was passiert, wenn Sie stattdessen AVG(value) verwenden?
Dies berechnet den gewichteten arithmetischen Mittelwert, wobei die Dauer als Gewicht $w_i$ dient. Kandidaten verwechseln dies häufig mit einem geometrischen Mittel oder versuchen, AVG(value * duration) zu verwenden, was eine Summe von Produkten ohne Normalisierung ergibt. Die Verwendung von AVG(value) behandelt jede Zeile gleich, unter der Annahme, dass die Zeitintervalle einheitlich sind, was die Anforderung verletzt, dass länger andauernde Werte größeren Einfluss ausüben.
Wie beeinflussen zeitliche Lücken oder doppelte Zeitstempel innerhalb derselben Partition das Verhalten der LEAD-Funktion, und warum ist PARTITION BY device_id unerlässlich?
Kandidaten vergessen manchmal, dass LEAD auf der physischen Reihenfolge der Zeilen innerhalb der Fensterspezifikation arbeitet. Ohne PARTITION BY device_id berechnet die Funktion die Intervalle über verschiedene Sensoren, was unsinnige negative oder massive Dauern erzeugt. Darüber hinaus gibt LEAD, wenn doppelte Zeitstempel vorhanden sind, die nächste getrennte Zeile unabhängig von der Zeit zurück, was potenziell Null-Sekunden-Intervalle erzeugt. Kandidaten müssen entscheiden, ob sie zuerst mit DISTINCT oder ROW_NUMBER() filtern, um Division-durch-Null-Fehler zu vermeiden.