Storia della domanda
La deviazione assoluta mediana (MAD) è stata introdotta da Gauss nel 1816 come una misura robusta della dispersione statistica, successivamente formalizzata da Hampel negli anni '70 per analisi resistenti agli outlier. A differenza della deviazione standard, che eleva al quadrato le deviazioni ed è quindi ipersensibile ai valori estremi, il MAD tollera fino al 50% di dati contaminati senza distorsione. In ANSI SQL, calcolare il MAD è diventato pratico con lo standard SQL:2003, che ha introdotto funzioni aggregate di set ordinate come PERCENTILE_CONT, consentendo calcoli di mediana dichiarativi senza cicli procedurali.
Il problema
Calcolare il MAD richiede un'operazione di mediana annidata: prima determinare la mediana del dataset, poi trovare la mediana delle differenze assolute tra ogni osservazione e quella mediana. In ANSI SQL, questo è complesso perché fare riferimento a un risultato aggregato all'interno della stessa clausola SELECT per calcolare le deviazioni individuali richiede un auto-join o una subquery correlata, entrambi i quali degradano le performance su grandi dataset di serie temporali. Inoltre, le funzioni STDDEV standard producono soglie gonfiate quando i dati dei sensori contengono picchi di trasmissione o errori di calibrazione, rendendo il robusto MAD essenziale per una rilevazione accurata delle anomalie.
La soluzione
Utilizza una Common Table Expression (CTE) pipeline per separare il calcolo in fasi logiche. Prima, usa PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) per calcolare la mediana per gruppo. Secondo, calcola la deviazione assoluta per ogni riga rispetto alla sua mediana di gruppo. Infine, applica nuovamente PERCENTILE_CONT a queste deviazioni per derivare il MAD. Questo metodo è puramente basato su set, sfrutta l'ottimizzatore del motore del database per le funzioni di finestra e evita l'elaborazione riga per riga.
WITH group_medians AS ( SELECT sensor_id, reading, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY reading) OVER (PARTITION BY sensor_id) AS median_val FROM telemetry ), deviations AS ( SELECT sensor_id, ABS(reading - median_val) AS abs_dev FROM group_medians ) SELECT DISTINCT sensor_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY abs_dev) OVER (PARTITION BY sensor_id) AS mad FROM deviations;
Un impianto di produzione ha distribuito migliaia di sensori di vibrazione su nastri trasportatori per prevedere i guasti dei cuscinetti. Le soglie di allerta statiche falliscono perché le temperature invernali producevano baselines naturalmente più basse rispetto all'estate, causando falsi positivi nei mesi freddi e avvisi mancati nei mesi caldi. Il team di ingegneria aveva bisogno di un metodo statistico che si adattasse alla distribuzione storica unica di ciascun sensore senza essere distorto da occasionali glitch di trasmissione.
Il team ha considerato tre approcci architettonici.
Elaborazione statistica lato client ha coinvolto l'esportazione di dump CSV giornalieri in Python utilizzando le librerie Pandas e SciPy. Ciò ha offerto funzioni statistiche ricche e prototipazione rapida, ma ha introdotto una latenza di dati di 24 ore e ha creato rischi di sicurezza spostando dati operativi sensibili al di fuori del firewall del database SQL.
Soluzioni SQL procedurali hanno utilizzato cursori e tabelle temporanee per iterare attraverso la storia di ciascun sensore, ordinando i valori per identificare la riga centrale. Questo approccio ha funzionato su sistemi legacy privi di moderne funzioni di finestra, eppure ha sofferto di una grave degradazione delle performance a causa della complessità O(n²) e di un'eccessiva contesa di blocchi, impiegando oltre 45 minuti per elaborare un milione di righe.
Funzioni di finestra ANSI SQL implementate tramite CTE hanno calcolato le mediane basate su set usando PERCENTILE_CONT. Questa soluzione è stata eseguita interamente all'interno del motore del database in meno di 800 millisecondi su 50 milioni di record, ha minimizzato l'overhead di rete e ha sfruttato il parallelismo dell'ottimizzatore, sebbene richiedesse conformità a SQL:2003 o successivi.
Il team ha selezionato l'approccio funzione di finestra ANSI SQL perché bilanciava le performance in tempo reale con severi requisiti di governance dei dati che proibivano l'esportazione dei dati. I valori MAD risultanti hanno stabilito soglie dinamiche dove qualsiasi lettura superiore a mediana ± 3 * MAD ha attivato immediati avvisi di manutenzione. Questo ha ridotto i falsi positivi del 94% e ha rilevato tre fermi imminenti dei cuscinetti due giorni prima rispetto al sistema statico precedente.
Perché si preferisce il MAD rispetto alla deviazione standard per la rilevazione delle anomalie nei sistemi di telemetria basati su SQL?
La deviazione standard calcola la radice quadrata della deviazione quadratica media dalla media, una metrica che esplode quando ci sono outlier perché il quadrato amplifica grandi distanze. Al contrario, il MAD utilizza la mediana, che è un estimatore resistente ai punti di rottura che ignora l'entità degli outlier estremi fino al 50% del volume dei dati. Per le implementazioni ANSI SQL, ciò significa che un singolo malfunzionamento di un sensore che invia un valore di 9999 gonfierà notevolmente STDDEV ma lascerà il MAD quasi invariato, prevenendo gonfiare le soglie false che mascherano future anomalie sottili.
Quali sono le differenze tra PERCENTILE_CONT e PERCENTILE_DISC quando si calcolano le mediane per letture discrete dei sensori, e quale dovresti usare per il MAD?
PERCENTILE_CONT(0.5) esegue un'interpolazione lineare tra i due valori centrali quando il conteggio delle righe è pari, restituendo un valore ipotetico che potrebbe non esistere nella tua tabella (ad esempio, mediando 20 e 30 per restituire 25). PERCENTILE_DISC(0.5) restituisce il valore effettivo più piccolo dal dataset la cui distribuzione cumulativa è maggiore o uguale a 0.5. Per il calcolo del MAD su letture intere discrete dei sensori, PERCENTILE_DISC è spesso più sicuro perché garantisce che la soglia corrisponda a una misurazione osservata reale, evitando deviazioni frazionarie che complicano l'interpretazione.
Può il MAD essere calcolato senza CTE utilizzando un singolo auto-join, e quali sono i compromessi in termini di performance?
Sì, ma è inefficiente. Puoi auto-unire la tabella su sensor_id per confrontare ogni riga con ogni altra riga per trovare la mediana, ma questo risulta in complessità O(n²). In alternativa, utilizzare una subquery derivata per calcolare la mediana prima, quindi unirsi nuovamente per calcolare le deviazioni, costringe il database a materializzare risultati intermedi o a riesaminare la tabella più volte. Le CTE consentono all'ottimizzatore di trattare il calcolo della mediana come un spool o una tabella di lavoro che viene calcolata una volta e riutilizzata, risultando tipicamente in un'unica operazione di ordinamento e complessità lineare O(n log n). I candidati dimenticano spesso che gli ottimizzatori ANSI SQL possono trasformare le CTE in tabelle di lavoro interne, rendendole più efficienti rispetto alle subquery correlate nella lista SELECT.