Quando si stabiliscono i confini degli outlier statistici per le metriche di controllo della qualità, come si calcola l'**intervallo interquartile (IQR)** all'interno di set di dati raggruppati utilizzando esclusivamente le **funzioni di finestra e aggregate ANSI SQL**, e come si utilizza questa misura per filtrare le osservazioni che si trovano oltre 1.5×IQR dai quartili?

Risposta alla domanda

Storia della domanda

Il concetto di IQR ha origine dalla metodologia di analisi esplorativa dei dati di John Tukey sviluppata negli anni '70, fornendo una statistica robusta per la rilevazione degli outlier che rimane resistente ai valori estremi. Con l'evoluzione del data warehousing, gli analisti sono passati da pacchetti statistici procedurali a query SQL basate su set, richiedendo implementazioni native di tali calcoli nel database. ANSI SQL:2003 ha introdotto funzioni di distribuzione inversa, successivamente perfezionate in SQL:2011, che consentono calcoli percentuali direttamente all'interno del motore del database senza elaborazione esterna.

Il problema

La sfida richiede il calcolo del primo quartile (Q1, 25esimo percentile) e del terzo quartile (Q3, 75esimo percentile) per ciascun sottogruppo all'interno di un set di dati per derivare l'IQR (Q3 meno Q1). Una volta stabiliti, i confini statistici per gli outlier sono definiti a Q1 − 1.5×IQR e Q3 + 1.5×IQR. La complessità risiede nell'eseguire questi calcoli statistici su partizioni in un'unica operazione basata su set mantenendo l'esattezza, quindi filtrare il set di dati originale rispetto a questi limiti calcolati dinamicamente senza ricorrere a cicli procedurali o elaborazioni a livello di applicazione.

La soluzione

Utilizzare PERCENTILE_CONT(0.25) e PERCENTILE_CONT(0.75) come funzioni di finestra per set ordinati partizionate dalla colonna di raggruppamento, che eseguono l'interpolazione lineare per determinare i valori esatti dei quartili. Calcolare l'IQR e le condizioni di confine all'interno di un Espressione di Tabella Comune (CTE), quindi unire o filtrare direttamente utilizzando clausole WHERE che confrontano le misurazioni con i confini calcolati.

WITH quartiles AS (
  SELECT 
    facility_zone,
    temperature,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q3
  FROM sensor_readings
),
bounds AS (
  SELECT 
    facility_zone,
    temperature,
    (q3 - q1) AS iqr,
    q1 - 1.5 * (q3 - q1) AS lower_fence,
    q3 + 1.5 * (q3 - q1) AS upper_fence
  FROM quartiles
)
SELECT facility_zone, temperature, iqr
FROM bounds
WHERE temperature < lower_fence 
   OR temperature > upper_fence;

Situazione reale

Una società farmaceutica monitora congelatori a temperatura ultra-bassa che immagazzinano inventario di vaccini in 200 zone. Ogni zona genera 10.000 letture di temperatura al giorno. La semplice rilevazione degli outlier tramite deviazione standard ha fallito poiché occasionali fluttuazioni della rete elettrica hanno causato picchi estremi che hanno distorto la media, risultando in falsi negativi per lievi malfunzionamenti delle attrezzature. Il team di qualità richiedeva un metodo statistico robusto per contrassegnare solo quelle letture che si discostavano significativamente dall'intervallo operativo tipico della zona, implementato direttamente all'interno del loro data warehouse PostgreSQL per alimentare dashboard in tempo reale Tableau.

Soluzione 1: Elaborazione a livello di applicazione utilizzando Python e Pandas

Estrarre tutti i dati storici tramite ODBC in un servizio Python, calcolare i quartili utilizzando groupby().quantile(), quindi filtrare e scrivere i risultati di nuovo. Pro: Librerie statistiche estremamente flessibili, facile debug con esecuzione passo-passo e sintassi familiare per i data scientist. Contro: Ingente sovraccarico di rete per il trasferimento di milioni di righe, vincoli di memoria che causano errori di out-of-memory sui server applicativi, e obsolescenza dei dati a causa di finestre di elaborazione di 45 minuti che rendono i risultati obsoleti al termine.

Soluzione 2: SQL ANSI nativo utilizzando le funzioni di finestra PERCENTILE_CONT

Implementare la query utilizzando PERCENTILE_CONT come aggregate per set ordinati con clausole OVER partizionate da facility_zone. Pro: Nessun trasferimento di dati, sfrutta gli esistenti indici B-tree sugli identificatori di zona, riduce il tempo di elaborazione a meno di 15 secondi e fornisce risultati in tempo reale direttamente utilizzabili da strumenti BI. Contro: Richiede un database conforme a SQL:2003/2011 (non disponibile in versioni legacy di MySQL), crea operazioni di ordinamento temporanee che aumentano l'uso della CPU durante l'esecuzione e coinvolge una sintassi complessa poco familiare a molti sviluppatori di applicazioni.

Soluzione 3: Approssimazione utilizzando NTILE(4)

Dividere le letture di ciascuna zona in quattro bucket uguali utilizzando la funzione di finestra NTILE, quindi usare MIN() e MAX() sui bucket 1 e 4 per approssimare i confini Q1 e Q3. Pro: Compatibile con versioni di database più vecchie prive di funzioni di distribuzione inversa, esegue più veloce a causa dei calcoli approssimativi. Contro: Produce solo confini approssimativi non adatti per la conformità normativa, fallisce in modo catastrofico con campioni piccoli o forti valori legati, e introduce comportamento non deterministico quando i confini cadono tra letture di sensori discrete.

Soluzione scelta e risultato

Il team ha scelto Soluzione 2 (approccio PERCENTILE_CONT) poiché le normative farmaceutiche richiedono calcoli statistici esatti piuttosto che approssimazioni. L'amministratore del database ha creato indici compositi su (facility_zone, temperature), eliminando le operazioni di ordinamento. La query risultante ha identificato lo 0,03% delle letture come veri outlier statistici, innescando ispezioni automatiche dei congelatori che hanno prevenuto perdite di inventario valutate a circa $2M all'anno, riducendo nel contempo i costi infrastrutturali rimuovendo il layer ETL in Python.

Cosa spesso i candidati trascurano

Perché PERCENTILE_CONT produce risultati diversi da PERCENTILE_DISC quando calcola i quartili, e quale dovrebbe essere utilizzato per IQR?

PERCENTILE_CONT (continuo) esegue un'interpolazione lineare tra i due valori più vicini che circondano la posizione percentuale richiesta, restituendo un valore calcolato che potrebbe non esistere nel set di dati originale. PERCENTILE_DISC (discreto) restituisce il valore di distribuzione cumulativa più piccolo maggiore o uguale al percentile, selezionando effettivamente una misurazione osservata reale. Per i calcoli di IQR nella rilevazione degli outlier, PERCENTILE_CONT è generalmente preferito poiché fornisce una scala continua meno sensibile agli artefatti di campionamento discreto, anche se PERCENTILE_DISC diventa necessario quando i confini degli outlier devono corrispondere a valori osservati fisicamente piuttosto che a interpolazioni matematiche.

Come gestisci i gruppi contenenti meno di quattro valori distinti in cui l'IQR collassa matematicamente a zero o diventa indefinito?

Quando una partizione contiene valori identici o meno di quattro punti dati, PERCENTILE_CONT restituisce valori identici per Q1 e Q3, risultando in un IQR di zero. Questo causa il collasso dei confini degli outlier al valore mediano, potenzialmente contrassegnando ogni osservazione distinta come un outlier. I candidati devono implementare controlli NULLIF o espressioni CASE per rilevare scenari di zero IQR, restituendo NULL per lo stato di outlier, tornando a metodi di deviazione standard per piccoli gruppi, o escludendo esplicitamente gruppi con COUNT(DISTINCT value) < 4 dall'analisi degli outlier secondo le regole aziendali.

Quale strategia di indicizzazione ottimizza le prestazioni delle funzioni di distribuzione inversa quando si elaborano miliardi di righe partizionate da categorie ad alta cardinalità?

Poiché PERCENTILE_CONT richiede di ordinare ogni partizione per determinare le posizioni percentuali, i candidati trascurano frequentemente la necessità di indici compositi su (categoria, misurazione). Tali indici consentono al motore del database di scansionare le pagine foglia dell'indice pre-ordinate, eliminando le costose operazioni di ordinamento esterne su disco. Senza questi indici, il database esegue ordinamenti separati per ciascuna partizione, causando severa frustrazione di I/O e esaurimento temporaneo dello spazio su disco. Inoltre, i candidati trascurano che spingere clausole WHERE selettive nelle prime CTE riduce il set di lavoro prima dei costosi calcoli percentuali, poiché le funzioni di distribuzione inversa non possono sfruttare gli indici una volta iniziata la fase di aggregazione.