Storia della domanda
Le medie pesate nel tempo sono emerse come una metrica critica nell'IoT industriale e nell'analisi delle serie temporali finanziarie. Le semplici medie aritmetiche distorcono la realtà poiché i valori dei sensori persistono fino alla successiva misurazione. Prima dello standard ANSI SQL:2003, calcolare queste medie richiedeva cursori procedurali o costosi self-join. Questi metodi si eseguivano in complessità temporale O(n²).
L'introduzione delle funzioni di finestra LEAD e LAG ha rivoluzionato questo dominio. Hanno abilitato calcoli di intervallo basati su set in un solo passaggio che si eseguono in O(n) tempo. Questo rende fattibili le analisi in tempo reale su miliardi di righe all'interno del livello del database.
Il problema
Data una tabella readings con colonne device_id, ts (timestamp) e value, l'obiettivo è calcolare una media pesata. Ogni riga deve contribuire proporzionalmente al delta temporale fino alla lettura successiva. Matematicamente, questo è $\frac{\sum (value_i \times (ts_{i+1} - ts_i))}{\sum (ts_{i+1} - ts_i)}$.
L'ultima riga presenta una condizione limite. Non ha timestamp successivo, quindi il suo intervallo deve essere definito come zero, estraibile a un tempo corrente, o limitato a un tempo di fine noto. La soluzione deve evitare cursori, funzioni definite dall'utente o self-join per rimanere puramente dichiarativa.
La soluzione
Utilizzare la funzione di finestra LEAD per proiettare il prossimo timestamp nella riga corrente. Calcolare la differenza di epoca per derivare il peso. Quindi applicare le formule standard per la media pesata.
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;
Questo approccio utilizza PARTITION BY per garantire che la finestra si ripristini per ogni dispositivo. Ciò impedisce l'interleaving dei timestamp di sensori diversi. Il COALESCE gestisce la riga terminale assegnando zero come peso, escludendola di fatto dal denominatore.
Una linea di produzione farmaceutica monitora 200 bioreattori. Ciascuno emette dati di temperatura a intervalli irregolari—ogni 10 secondi durante le fasi di riscaldamento, ma ogni 30 minuti durante le soste inattive. Il team di qualità richiedeva una media pesata nel tempo giornaliera per garantire la conformità. Una semplice media sovrastimerebbe i campioni rapid-fire di riscaldamento e sottovaluterebbe le soste stabili, potenzialmente mascherando pericolosi escursioni di temperatura.
Una proposta di soluzione prevedeva di estrarre tutti i dati in un DataFrame pandas Python. Gli ingegneri avrebbero calcolato diff() sui timestamp e computato la media pesata. Sebbene flessibile, questo approccio trasferiva gigabyte di dati attraverso la rete. Ha inoltre bloccato la workstation di analisi durante l'elaborazione di report di fine trimestre che coprivano 90 giorni di dati ad alta frequenza.
Un'altra alternativa utilizzava una subquery correlata per trovare il MIN(ts) maggiore della riga corrente per ciascun dispositivo. Questo eseguiva correttamente sui set di test di 1.000 righe. Tuttavia, mostrava una degradazione quadratica, impiegando 45 minuti per la cronologia completa di un singolo reattore.
Il team ha selezionato l'approccio delle funzioni di finestra ANSI SQL. Mantenendo il calcolo all'interno del cluster PostgreSQL, la query ha sfruttato scansioni sequenziali parallele ed evitato costi di rete. L'implementazione finale ha elaborato 50 milioni di righe attraverso tutti i reattori in meno di 12 secondi. Ciò ha permesso aggiornamenti in tempo reale della dashboard che hanno consentito agli operatori di individuare deriva termica in pochi minuti anziché ore.
Come gestire l'osservazione finale in ciascuna partizione in cui non esiste un timestamp successivo per definire il peso dell'intervallo?
I candidati spesso omettono la condizione limite. Questo fa sì che l'intervallo della riga terminale venga valutato come NULL, che le aggregazioni SQL ignorano. Di conseguenza, il contributo dell'ultima lettura viene eliminato, distorcendo la media. L'approccio corretto utilizza COALESCE per sostituire sia zero che EXTRACT(EPOCH FROM (boundary_time - ts)) se la media deve estendersi a un tempo di fine noto come CURRENT_TIMESTAMP.
Perché la formula SUM(value * duration) / SUM(duration) rappresenta matematicamente la media pesata nel tempo e cosa succede se si utilizza AVG(value) invece?
Questo calcola la media aritmetica pesata in cui la durata funge da peso $w_i$. I candidati spesso confondono questo con una media geometrica o tentano di utilizzare AVG(value * duration), che produce una somma di prodotti senza normalizzazione. L'uso di AVG(value) tratta ogni riga in modo uniforme, assumendo intervalli di tempo uniformi, il che viola il requisito che i valori di lunga durata esercitino una maggiore influenza.
Come influenzano i lag temporali o i timestamp duplicati all'interno della stessa partizione il comportamento della funzione LEAD e perché è essenziale PARTITION BY device_id?
I candidati a volte dimenticano che LEAD opera sull'ordine fisico delle righe all'interno della specifica della finestra. Senza PARTITION BY device_id, la funzione calcola intervalli tra sensori diversi, creando durate negative o massive prive di senso. Inoltre, se esistono timestamp duplicati, LEAD restituisce la riga successiva distinta indipendentemente, creando potenzialmente intervalli di zero secondi. I candidati devono decidere se deduplicare prima utilizzando DISTINCT o il filtraggio tramite ROW_NUMBER() per evitare errori di divisione per zero.