Historia pytania
Ważone średnie czasowe pojawiły się jako kluczowy wskaźnik w przemysłowym IoT oraz analizie czasowych szeregów finansowych. Proste średnie arytmetyczne zniekształcają rzeczywistość, ponieważ wartości czujników utrzymują się do następnego pomiaru. Przed standardem ANSI SQL:2003 obliczanie tych średnich wymagało proceduralnych kursorów lub drogich samo-łączeń. Metody te miały złożoność czasową O(n²).
Wprowadzenie funkcji okiennych LEAD i LAG zrewolucjonizowało tę dziedzinę. Umożliwiły one jednoprzebiegowe, oparte na zbiorach obliczenia interwałowe, które działają w czasie O(n). Umożliwia to realistyczną analizę danych w czasie rzeczywistym na miliardach wierszy w warstwie bazy danych.
Problem
Dana jest tabela readings z kolumnami device_id, ts (znacznik czasu) i value, celem jest obliczenie ważonej średniej. Każdy wiersz musi proporcjonalnie przyczyniać się do różnicy czasowej do następnego odczytu. Matematycznie jest to $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.
Ostatni wiersz przedstawia warunek brzegowy. Nie ma kolejnego znacznika czasu, więc jego interwał musi być zdefiniowany jako zero, ekstrapolowany do bieżącego czasu lub ograniczony do znanego czasu końcowego. Rozwiązanie musi unikać kursorów, funkcji zdefiniowanych przez użytkownika lub samo-łączeń, aby pozostać czysto deklaratywne.
Rozwiązanie
Użyj funkcji okiennej LEAD, aby przewidzieć następny znacznik czasu w bieżącym wierszu. Oblicz różnicę epok, aby wywnioskować wagę. Następnie zastosuj standardowe formuły średniej ważonej.
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;
Podejście to wykorzystuje PARTITION BY, aby zapewnić reset okna dla każdego urządzenia. Zapobiega to przeplatającym się znacznikom czasu z różnych czujników. COALESCE obsługuje końcowy wiersz, przypisując zero wagi, skutecznie wykluczając go z mianownika.
Linia produkcyjna w przemyśle farmaceutycznym monitoruje 200 bioreaktorów. Każdy z nich emituje dane o temperaturze w nieregularnych interwałach — co 10 sekund podczas faz podgrzewania, ale co 30 minut podczas utrzymania w bezruchu. Zespół jakości wymagał codziennej ważonej średniej czasowej dla zapewnienia zgodności. Prosta średnia przeważyłaby szybkie odczyty przyspieszania i zaniżyłaby stabilne utrzymania, co mogłoby zatuszować niebezpieczne odchylenia temperatury.
Jedno z proponowanych rozwiązań polegało na wyodrębnieniu wszystkich danych do ramki danych Python pandas. Inżynierowie obliczyliby diff() na znacznikach czasu i wyliczyli średnią ważoną. Mimo że elastyczne, to podejście przesyłało gigabajty danych przez sieć. Spowodowało również awarię stacji roboczej analitycznej podczas przetwarzania raportów na koniec kwartału obejmujących 90 dni danych o wysokiej częstotliwości.
Inna alternatywa używała skorelowanego podzapytania do znajdowania MIN(ts) większego od bieżącego wiersza dla każdego urządzenia. Działało to poprawnie na zestawach testowych z 1,000 wierszy. Jednak wykazywało degradację kwadratową, zajmując 45 minut dla pełnej historii jednego reaktora.
Zespół wybrał podejście funkcji okiennej ANSI SQL. Utrzymując obliczenia w obrębie klastra PostgreSQL, zapytanie wykorzystało równoległe skanowanie sekwencyjne i uniknęło obciążeń sieciowych. Ostateczna implementacja przetworzyła 50 milionów wierszy dla wszystkich reaktorów w mniej niż 12 sekund. Umożliwiło to aktualizacje pulpitu w czasie rzeczywistym, co pozwoliło operatorom na dostrzeganie dryfu termicznego w minutach, a nie godzinach.
Jak radzisz sobie z ostatnią obserwacją w każdej partycji, gdzie nie ma kolejnego znaczika czasu, aby zdefiniować wagę interwału?
Kandydaci często pomijają warunek brzegowy. Powoduje to, że interwał ostatniego wiersza ocenia się jako NULL, co jest ignorowane przez agregaty SQL. W konsekwencji wkład ostatniego odczytu zostaje pominięty, co wypacza średnią. Poprawne podejście wykorzystuje COALESCE, aby zastąpić albo zero, albo EXTRACT(EPOCH FROM (boundary_time - ts)), jeśli średnia musi extendować do znanego czasu końcowego, takiego jak CURRENT_TIMESTAMP.
Dlaczego wzór SUM(value * duration) / SUM(duration) matematycznie reprezentuje ważoną średnią czasową, a co się stanie, jeśli użyjesz AVG(value) zamiast tego?
Oblicza to ważoną średnią arytmetyczną, gdzie czas trwałości служит jako waga $w_i$. Kandydaci często mylą to z średnią geometryczną lub próbują użyć AVG(value * duration), co produkuje sumę iloczynów bez normalizacji. Używanie AVG(value) traktuje każdy wiersz równo, zakładając jednorodne kroki czasowe, co narusza wymaganie, że wartości trwające dłużej mają większy wpływ.
Jak luki czasowe lub duplikujące się znaczniki czasu w tej samej partycji wpływają na działanie funkcji LEAD, i dlaczego PARTITION BY device_id jest istotne?
Kandydaci czasami zapominają, że LEAD działa na fizycznym porządku wierszy w ramach specyfikacji okna. Bez PARTITION BY device_id funkcja oblicza interwały pomiędzy różnymi czujnikami, co prowadzi do nielogicznych negatywnych lub ogromnych długości. Dodatkowo, jeśli istnieją duplikaty znaczników czasu, LEAD zwraca następny wyróżniający się wiersz, co potencjalnie tworzy interwały zerowe. Kandydaci muszą zdecydować, czy najpierw usunąć duplikaty, używając DISTINCT lub filtrowania ROW_NUMBER(), aby uniknąć błędów dzielenia przez zero.