Historique de la question
La déviation absolue médiane (MAD) a été introduite par Gauss en 1816 comme une mesure robuste de dispersion statistique, formalisée plus tard par Hampel dans les années 1970 pour des analyses résistantes aux valeurs aberrantes. Contrairement à l'écart-type, qui élève au carré les écarts et est donc hypersensible aux valeurs extrêmes, la MAD tolère jusqu'à 50 % de données contaminées sans distorsion. En ANSI SQL, le calcul de la MAD est devenu pratique avec la norme SQL:2003, qui a introduit des fonctions d'agrégation par ensembles ordonnés telles que PERCENTILE_CONT, permettant des calculs de médiane déclaratifs sans boucles procédurales.
Le problème
Le calcul de la MAD nécessite une opération médiane imbriquée : d'abord, déterminer la médiane de l'ensemble de données, puis trouver la médiane des écarts absolus entre chaque observation et cette médiane. En ANSI SQL, cela est difficile car référencer un résultat agrégé dans la même clause SELECT pour calculer des écarts individuels nécessite une auto-jointure ou une sous-requête corrélée, toutes deux dégradant la performance sur de grands ensembles de données de séries temporelles. De plus, les fonctions STDDEV standard produisent des seuils gonflés lorsque les données des capteurs contiennent des pics de transmission ou des erreurs d'étalonnage, rendant essentielle la MAD robuste pour une détection d'anomalies précise.
La solution
Utiliser un Common Table Expression (CTE) pour séparer le calcul en étapes logiques. D'abord, utilisez PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) pour calculer la médiane par groupe. Ensuite, calculez l'écart absolu pour chaque ligne par rapport à sa médiane de groupe. Enfin, appliquez à nouveau PERCENTILE_CONT à ces écarts pour dériver la MAD. Cette méthode est purement basée sur les ensembles, tire parti de l'optimiseur du moteur de base de données pour les fonctions de fenêtre, et évite le traitement ligne par ligne.
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;
Une usine de fabrication a déployé des milliers de capteurs de vibration sur des bandes transporteuses pour prédire les pannes de roulements. Les seuils d'alerte statiques ont échoué car les températures hivernales ont produit des bases naturellement plus basses que celles de l'été, causant des faux positifs en hiver et des alertes manquées en été. L'équipe d'ingénierie avait besoin d'une méthode statistique qui s'adapte à la distribution historique unique de chaque capteur sans être biaisée par des glitchs de transmission occasionnels.
L'équipe a envisagé trois approches architecturales.
Le traitement statistique côté client a impliqué l'exportation de dumps CSV quotidiens vers Python en utilisant les bibliothèques Pandas et SciPy. Cela offrait de riches fonctions statistiques et un prototypage rapide, mais cela introduisait une latence de données de 24 heures et créait des risques de sécurité en déplaçant des données opérationnelles sensibles en dehors du pare-feu de la base de données SQL.
Les solutions SQL procédurales utilisaient des curseurs et des tables temporaires pour itérer à travers l'historique de chaque capteur, triant les valeurs pour identifier la ligne du milieu. Cette approche fonctionnait sur des systèmes hérités manquant de fonctions de fenêtre modernes, mais elle souffrait d'une dégradation de performance sévère due à la complexité O(n²) et à une contention de verrouillage excessive, prenant plus de 45 minutes pour traiter un million de lignes.
Les fonctions de fenêtre ANSI SQL mises en œuvre via des CTE ont calculé les médianes par ensembles en utilisant PERCENTILE_CONT. Cette solution s'exécutait entièrement au sein du moteur de base de données en moins de 800 millisecondes contre 50 millions d'enregistrements, minimisant la surcharge réseau et tirant parti du parallélisme de l'optimiseur, bien qu'elle ait nécessité une conformité à SQL:2003 ou plus récent.
L'équipe a choisi l'approche ANSI SQL window function car elle équilibré les performances en temps réel avec des exigences strictes de gouvernance des données qui interdisaient l'exportation des données. Les valeurs MAD résultantes ont établi des seuils dynamiques où toute lecture dépassant median ± 3 * MAD déclenchait des alertes de maintenance immédiates. Cela a réduit les faux positifs de 94 % et détecté trois pannes de roulement imminentes deux jours plus tôt que le système statique précédent.
Pourquoi la MAD est-elle préférable à l'écart type pour la détection d'anomalies dans les systèmes de télémétrie basés sur SQL ?
L'écart type calcule la racine carrée de la moyenne des écarts au carré par rapport à la moyenne, une métrique qui explose lorsqu'il y a des valeurs aberrantes parce que l'élévation au carré amplifie les grandes distances. En revanche, la MAD utilise la médiane, qui est un estimateur résistant aux points de rupture qui ignore l'ampleur des valeurs aberrantes extrêmes jusqu'à 50 % du volume de données. Pour les implémentations en ANSI SQL, cela signifie qu'une seule défaillance de capteur envoyant une valeur de 9999 gonflera considérablement STDDEV mais laissera la MAD presque inchangée, empêchant un gonflement faux des seuils qui masque de futures anomalies subtiles.
Comment PERCENTILE_CONT et PERCENTILE_DISC diffèrent-ils lors du calcul des médianes pour des lectures de capteur discrètes, et lequel devez-vous utiliser pour la MAD ?
PERCENTILE_CONT(0.5) effectue une interpolation linéaire entre les deux valeurs centrales lorsque le nombre de lignes est pair, retournant une valeur hypothétique qui pourrait ne pas exister dans votre table (par exemple, en faisant la moyenne entre 20 et 30 pour retourner 25). PERCENTILE_DISC(0.5) retourne la plus petite valeur réelle de l'ensemble de données dont la distribution cumulée est supérieure ou égale à 0,5. Pour le calcul de la MAD sur des lectures de capteur entières discrètes, PERCENTILE_DISC est souvent plus sûr car il garantit que le seuil correspond à une mesure observée réelle, évitant les écarts fractionnaires qui compliquent l'interprétation.
La MAD peut-elle être calculée sans CTE en utilisant une seule auto-jointure, et quels sont les compromis de performance ?
Oui, mais ce n'est pas efficace. Vous pouvez faire une auto-jointure de la table sur sensor_id pour comparer chaque ligne avec chaque autre ligne afin de trouver la médiane, mais cela entraîne une complexité O(n²). Alternativement, utiliser une sous-requête dérivée pour calculer la médiane d'abord, puis revenir en arrière pour calculer les écarts, oblige la base de données à matérialiser des résultats intermédiaires ou à rescanner plusieurs fois la table. Les CTE permettent à l'optimiseur de traiter le calcul de la médiane comme un spool ou une table de travail qui est calculée une fois et réutilisée, entraînant généralement une seule opération de tri et une complexité linéaire O(n log n). Les candidats oublient souvent que les optimisateurs ANSI SQL peuvent transformer les CTE en tables de travail internes, les rendant plus efficaces que les sous-requêtes corrélées dans la liste SELECT.