Geschichte der Frage
Die median absolute deviation (MAD) wurde 1816 von Gauss als robustes Maß für statistische Streuung eingeführt und in den 1970er Jahren von Hampel für ausreißerresistente Analysen formalisiert. Im Gegensatz zur Standardabweichung, die Abweichungen quadriert und daher hypersensibel gegenüber extremen Werten ist, toleriert MAD bis zu 50 % kontaminierte Daten ohne Verzerrung. In ANSI SQL wurde die Berechnung von MAD mit dem SQL:2003-Standard praktikabel, der ordered-set aggregation functions wie PERCENTILE_CONT einführte, die deklarative Medianberechnungen ohne prozedurale Schleifen ermöglichen.
Das Problem
Die Berechnung von MAD erfordert eine geschachtelte Medianoperation: Zuerst wird der Median des Datensatzes bestimmt, dann der Median der absoluten Abweichungen zwischen jeder Beobachtung und diesem Median. In ANSI SQL ist dies herausfordernd, da das Verweisen auf ein aggregiertes Ergebnis innerhalb derselben SELECT-Klausel zur Berechnung individueller Abweichungen eine Selbstverknüpfung oder korrelierte Unterabfrage erfordert, was die Leistung bei großen Zeitreihendatensätzen beeinträchtigt. Darüber hinaus erzeugen standardmäßige STDDEV-Funktionen aufgeblähte Schwellenwerte, wenn Sensordaten Übertragungsspitzen oder Kalibrierungsfehler enthalten, was MAD für eine genaue Anomalieerkennung unerlässlich macht.
Die Lösung
Verwenden Sie eine Common Table Expression (CTE)-Pipeline, um die Berechnung in logische Stufen zu unterteilen. Zuerst verwenden Sie PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category), um den Median pro Gruppe zu berechnen. Zweitens berechnen Sie die absolute Abweichung für jede Zeile relativ zu ihrem Gruppenmedian. Schließlich wenden Sie erneut PERCENTILE_CONT auf diese Abweichungen an, um die MAD abzuleiten. Diese Methode ist rein set-basiert, nutzt den Optimierer der Datenbank-Engine für Fensterfunktionen und vermeidet eine zeilenweise Verarbeitung.
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;
Ein Fertigungswerk setzte Tausende von Vibrationssensoren auf Förderbändern ein, um Lagerfehler vorherzusagen. Statische Alarmgrenzen versagten, da die Wintertemperaturen von Natur aus niedrigere Baselines als im Sommer produzierten, was zu falsch positiven Ergebnissen in kalten Monaten und verpassten Alarmen in heißen Monaten führte. Das Ingenieurteam benötigte eine statistische Methode, die sich an die einzigartige historische Verteilung jedes Sensors anpasste, ohne von gelegentlichen Übertragungsfehlern beeinflusst zu werden.
Das Team erwog drei architektonische Ansätze.
Client-seitige statistische Verarbeitung beinhaltete den Export täglicher CSV-Dumps nach Python unter Verwendung der Bibliotheken Pandas und SciPy. Dies bot reichhaltige statistische Funktionen und schnelles Prototyping, führte jedoch zu einer 24-stündigen Dataverspätung und schuf Sicherheitsrisiken, da sensible Betriebsdaten außerhalb der SQL-Datenbank-Firewall verschoben wurden.
Prozedurale SQL-Lösungen nutzten Cursors und temporäre Tabellen, um die Geschichte jedes Sensors zu durchlaufen und Werte zu sortieren, um die mittlere Zeile zu identifizieren. Dieser Ansatz funktionierte in Legacy-Systemen, die keine modernen Fensterfunktionen hatten, litt jedoch unter schwerer Leistungseinbuße aufgrund der O(n²)-Komplexität und übermäßigen Sperrkonkurrenz, was mehr als 45 Minuten dauerte, um eine Million Zeilen zu verarbeiten.
ANSI SQL-Fensterfunktionen, die über CTEs implementiert wurden, berechneten Medianwerte set-basiert unter Verwendung von PERCENTILE_CONT. Diese Lösung wurde vollständig innerhalb der Datenbank-Engine in weniger als 800 Millisekunden gegen 50 Millionen Datensätze ausgeführt, minimierte Netzwerküberhead und nutzte die Parallelität des Optimierers, obwohl sie die Konformität mit SQL:2003 oder neuer erforderte.
Das Team wählte den Ansatz der ANSI SQL-Fensterfunktion, da er eine Balance zwischen Echtzeitleistung und strengen Datenverwaltungsvorschriften bot, die den Datenaustausch untersagten. Die resultierenden MAD-Werte etablierten dynamische Schwellenwerte, bei denen jeder Wert, der median ± 3 * MAD überschritt, sofortige Wartungsalarme auslöste. Dies reduzierte die falsch positiven Ergebnisse um 94 % und erkannte drei bevorstehende Lagerfehler zwei Tage früher als das vorherige statische System.
Warum wird MAD der Standardabweichung für die Anomalieerkennung in SQL-basierten Telemetrie-Systemen vorgezogen?
Die Standardabweichung berechnet die Quadratwurzel der durchschnittlichen quadratischen Abweichung vom Mittelwert, ein Maß, das explodiert, wenn Ausreißer vorhanden sind, da das Quadrieren große Abstände verstärkt. Im Gegensatz dazu verwendet MAD den Median, der ein Ausreißer-resistenter Schätzer ist, der die Größe extremer Ausreißer bis zu 50 % des Datenvolumens ignoriert. Für ANSI SQL-Implementierungen bedeutet dies, dass ein einzelner Sensorausfall, der einen Wert von 9999 sendet, die STDDEV stark aufblähen wird, während MAD nahezu unverändert bleibt, was eine falsche Schwellenwertaufblähung verhindert, die zukünftige subtile Anomalien verdeckt.
Wie unterscheiden sich PERCENTILE_CONT und PERCENTILE_DISC bei der Berechnung von Medianen für diskrete Sensorablesungen, und welchen sollten Sie für MAD verwenden?
PERCENTILE_CONT(0.5) führt eine lineare Interpolation zwischen den beiden zentralen Werten durch, wenn die Zeilenanzahl gerade ist, und gibt einen hypothetischen Wert zurück, der möglicherweise nicht in Ihrer Tabelle existiert (z. B. das Mittelung von 20 und 30, um 25 zurückzugeben). PERCENTILE_DISC(0.5) gibt den kleinsten tatsächlichen Wert aus dem Datensatz zurück, dessen kumulative Verteilung größer oder gleich 0,5 ist. Für die Berechnung von MAD bei diskreten Ganzzahlen von Sensorablesungen ist PERCENTILE_DISC oft sicherer, da es garantiert, dass der Schwellenwert einem tatsächlich beobachteten Messwert entspricht und fraktionale Abweichungen vermeidet, die die Interpretation komplizieren.
Kann MAD ohne CTEs mit einer einzigen Selbstverknüpfung berechnet werden, und welche Leistungsabstriche sind damit verbunden?
Ja, aber es ist ineffizient. Sie können die Tabelle auf sensor_id selbst verknüpfen, um jede Zeile mit jeder anderen Zeile zu vergleichen, um den Median zu finden, was jedoch zu einer O(n²)-Komplexität führt. Alternativ zwingt die Verwendung einer abgeleiteten Unterabfrage, um zunächst den Median zu berechnen und dann zurück zu verknüpfen, um Abweichungen zu berechnen, die Datenbank dazu, Zwischenresultate zu materialisieren oder die Tabelle mehrmals zu scannen. CTEs ermöglichen es dem Optimierer, die Medianberechnung als Spool- oder Arbeitsdatei zu behandeln, die einmal berechnet und wiederverwendet wird, was typischerweise zu einer einzigen Sortieroperation und linearer O(n log n)-Komplexität führt. Kandidaten vergessen oft, dass ANSI SQL-Optimierer CTEs in interne Arbeits-tabellen umwandeln können, was sie effizienter macht als korrelierte Unterabfragen in der SELECT-Liste.