Geschiedenis van de vraag
De median absolute deviation (MAD) werd in 1816 door Gauss geïntroduceerd als een robuuste maat voor statistische spreiding, later in de jaren '70 geformaliseerd door Hampel voor uitschieter-resistente analytics. In tegenstelling tot de standaarddeviatie, die afwijkingen kwadrateert en daardoor hypersensitief is voor extreme waarden, tolereert MAD tot 50% vervuilde data zonder vervorming. In ANSI SQL werd het berekenen van MAD praktisch met de SQL:2003 standaard, die ordered-set aggregate functions introduceerde zoals PERCENTILE_CONT, waarmee declaratieve medianen berekend kunnen worden zonder procedurele lussen.
Het probleem
Het berekenen van MAD vereist een geneste mediaanbewerking: eerst de mediaan van de dataset bepalen, en vervolgens de mediaan van de absolute verschillen tussen elke waarneming en die mediaan. In ANSI SQL is dit uitdagend omdat het verwijzen naar een aggregate resultaat binnen dezelfde SELECT clausule om individuele afwijkingen te berekenen een self-join of gecorreleerde subquery vereist, die beide de prestaties op grote tijdreeks datasets verlagen. Bovendien produceren standaard STDDEV functies opgeblazen drempels wanneer sensor data transmissiepieken of calibratiefouten bevat, waardoor de robuuste MAD essentieel is voor nauwkeurige anomaly detectie.
De oplossing
Gebruik een Common Table Expression (CTE) pipeline om de berekening in logische fasen te scheiden. Gebruik eerst PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) om de mediaan per groep te berekenen. Ten tweede, bereken de absolute afwijking voor elke rij ten opzichte van zijn groepsmediaan. Tot slot, pas opnieuw PERCENTILE_CONT toe op deze afwijkingen om de MAD af te leiden. Deze methode is puur set-gebaseerd, maakt gebruik van de optimizer van de database engine voor venster functies, en vermijdt rij-voor-rij verwerking.
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;
Een productiebedrijf heeft duizenden trillingssensoren op transportbanden geïnstalleerd om lagerschade te voorspellen. Statische alarmdrempels faalden omdat wintertemperaturen van nature lagere baselines produceerden dan in de zomer, wat leidde tot valse positieven in koude maanden en gemiste meldingen in warme maanden. Het engineeringteam had een statistische methode nodig die zich aanpaste aan de unieke historische verdeling van elke sensor zonder verstoord te worden door incidentele transmissieglitches.
Het team overwoog drie architecturale benaderingen.
Statistische verwerking aan de klantzijde hield in dat dagelijkse CSV-dumps naar Python werden geëxporteerd met behulp van Pandas en SciPy bibliotheken. Dit bood rijke statistische functies en snelle prototyping, maar introduceerde een 24-uurs datavertraging en creëerde beveiligingsrisico's door gevoelige operationele data buiten de SQL database firewall te verplaatsen.
Procedurele SQL-oplossingen maakten gebruik van cursors en tijdelijke tabellen om door de geschiedenis van elke sensor te itereren, waarden te sorteren om de middelste rij te identificeren. Deze aanpak werkte op legacy-systemen zonder moderne venster functies, maar leed onder ernstige prestatieafname door O(n²) complexiteit en overmatige locking-contentie, waarbij het meer dan 45 minuten kostte om een miljoen rijen te verwerken.
ANSI SQL venster functies geïmplementeerd via CTE's berekenden medianen set-gebaseerd met behulp van PERCENTILE_CONT. Deze oplossing werd volledig binnen de database engine uitgevoerd in minder dan 800 milliseconden tegen 50 miljoen records, minimaliseerde netwerkbelasting, en maakte gebruik van optimizer parallelisme, hoewel het SQL:2003 of nieuwere naleving vereiste.
Het team koos de ANSI SQL venster functie benadering omdat het een balans vond tussen realtime prestaties en strikte gegevens governance-eisen die gegevensexport verboden. De resulterende MAD-waarden stelden dynamische drempels vast waarbij elke meting die mediaan ± 3 * MAD overschreed, onmiddellijke onderhoudswaarschuwingen veroorzaakte. Dit verminderde valse positieven met 94% en detecteerde drie dreigende lagerschade twee dagen eerder dan het vorige statische systeem.
Waarom is MAD de voorkeur boven standaarddeviatie voor anomaly detectie in SQL-gebaseerde telemetriesystemen?
De standaarddeviatie berekent de vierkantswortel van de gemiddelde gekwadrateerde afwijking van het gemiddelde, een maat die explodeert wanneer uitschieters aanwezig zijn omdat kwadrateren grote afstanden versterkt. In tegenstelling tot dat maakt MAD gebruik van de mediaan, die een breakdown-point-resistente schatter is die de grootte van extreme uitschieters tot 50% van het databestand negeert. Voor ANSI SQL implementaties betekent dit dat een enkele sensorstoring die een waarde van 9999 verzendt, STDDEV aanzienlijk zal verhogen maar MAD vrijwel onveranderd zal laten, wat een valse drempelstijging voorkomt die toekomstige subtiele anomalieën verbergt.
Hoe verschillen PERCENTILE_CONT en PERCENTILE_DISC bij het berekenen van medianen voor discrete sensor metingen, en welke moet je gebruiken voor MAD?
PERCENTILE_CONT(0.5) voert lineaire interpolatie uit tussen de twee centrale waarden wanneer het rijnummer even is, en retourneert een hypothetische waarde die mogelijk niet in je tabel bestaat (bijv. het gemiddelde van 20 en 30 om 25 terug te geven). PERCENTILE_DISC(0.5) retourneert de kleinste daadwerkelijke waarde uit de dataset waarvan de cumulatieve distributie groter of gelijk is aan 0.5. Voor MAD-berekeningen op discrete gehele sensor metingen is PERCENTILE_DISC vaak veiliger omdat het garandeert dat de drempel overeenkomt met een echte waargenomen meting, waardoor fractionele afwijkingen die de interpretatie compliceren worden vermeden.
Kan MAD zonder CTE's worden berekend met een enkele self-join, en wat zijn de prestatieverhoudingen?
Ja, maar het is inefficiënt. Je kunt de tabel self-joinen op sensor_id om elke rij te vergelijken met elke andere rij om de mediaan te vinden, maar dit resulteert in O(n²) complexiteit. Alternatief, het gebruik van een afgeleide subquery om de mediaan eerst te berekenen, en dan terug te joinen om afwijkingen te berekenen, dwingt de database om tussenresultaten te materialiseren of de tabel meerdere keren opnieuw te scannen. CTE's stellen de optimizer in staat om de mediaanberekening als een spool of werktafel te behandelen die eenmaal wordt berekend en hergebruikt, wat doorgaans resulteert in een enkele sorteeroperatie en lineaire O(n log n) complexiteit. kandidaten vergeten vaak dat ANSI SQL optimizers CTE's kunnen omzetten in interne werktafels, waardoor ze efficiënter zijn dan gecorreleerde subquery's in de SELECT lijst.