Historia de la pregunta
La mediana de la desviación absoluta (MAD) fue introducida por Gauss en 1816 como una medida robusta de la dispersión estadística, formalizada más tarde por Hampel en la década de 1970 para análisis resistentes a outliers. A diferencia de la desviación estándar, que eleva al cuadrado las desviaciones y, por lo tanto, es hipersensible a valores extremos, MAD tolera hasta un 50% de datos contaminados sin distorsión. En SQL ANSI, calcular MAD se volvió práctico con el estándar SQL:2003, que introdujo funciones de agregación de conjunto ordenado como PERCENTILE_CONT, lo que permite cálculos de mediana declarativa sin bucles procedimentales.
El problema
Calcular MAD requiere una operación de mediana anidada: primero determinar la mediana del conjunto de datos, luego encontrar la mediana de las diferencias absolutas entre cada observación y esa mediana. En SQL ANSI, esto es un desafío porque hacer referencia a un resultado agregado dentro de la misma cláusula SELECT para calcular desviaciones individuales requiere un auto-join o subconsulta correlacionada, ambos de los cuales degradan el rendimiento en grandes conjuntos de datos de series temporales. Además, las funciones STDDEV estándar producen umbrales inflados cuando los datos de los sensores contienen picos de transmisión o errores de calibración, haciendo que la robusta MAD sea esencial para una detección de anomalías precisa.
La solución
Emplea un Expresión de Tabla Común (CTE) como pipeline para separar el cálculo en etapas lógicas. Primero, usa PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER (PARTITION BY category) para calcular la mediana por grupo. En segundo lugar, calcula la desviación absoluta para cada fila en relación con su mediana de grupo. Finalmente, aplica PERCENTILE_CONT nuevamente a estas desviaciones para derivar la MAD. Este método es puramente basado en conjuntos, aprovecha el optimizador del motor de base de datos para funciones de ventana y evita el procesamiento fila por fila.
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;
Una planta de manufactura desplegó miles de sensores de vibración en cintas transportadoras para predecir fallos en los rodamientos. Los umbrales de alerta estáticos fallaron porque las temperaturas invernales produjeron baselines naturalmente más bajos que en verano, causando falsos positivos en los meses fríos y alertas perdidas en los meses cálidos. El equipo de ingeniería necesitaba un método estadístico que se adaptara a la distribución histórica única de cada sensor sin ser sesgado por fallos de transmisión ocasionales.
El equipo consideró tres enfoques arquitectónicos.
El procesamiento estadístico del lado del cliente implicaba exportar volcando diarios en CSV a Python utilizando las bibliotecas Pandas y SciPy. Esto ofreció ricas funciones estadísticas y una rápida creación de prototipos, pero introdujo una latencia de datos de 24 horas y creó riesgos de seguridad al mover datos operativos sensibles fuera del cortafuegos de la base de datos SQL.
Las soluciones SQL procedimentales utilizaron cursores y tablas temporales para iterar a través de la historia de cada sensor, ordenando valores para identificar la fila central. Este enfoque funcionó en sistemas heredados que carecían de funciones de ventana modernas, pero sufrió una severa degradación del rendimiento debido a la complejidad de O(n²) y excesiva contención de bloqueos, tardando más de 45 minutos en procesar un millón de filas.
Las funciones de ventana SQL ANSI implementadas a través de CTEs calcularon medianas basadas en conjuntos usando PERCENTILE_CONT. Esta solución se ejecutó completamente dentro del motor de base de datos en menos de 800 milisegundos contra 50 millones de registros, minimizó la sobrecarga de red y aprovechó el paralelismo del optimizador, aunque requirió conformidad con SQL:2003 o más reciente.
El equipo seleccionó el enfoque de funciones de ventana SQL ANSI porque equilibró el rendimiento en tiempo real con estrictos requisitos de gobernanza de datos que prohibieron la exportación de datos. Los valores MAD resultantes establecieron umbrales dinámicos donde cualquier lectura que excediera mediana ± 3 * MAD activaba alertas de mantenimiento inmediatas. Esto redujo los falsos positivos en un 94% y detectó tres fallas inminentes en los rodamientos dos días antes que el sistema estático anterior.
¿Por qué se prefiere MAD sobre la desviación estándar para la detección de anomalías en sistemas de telemetría basados en SQL?
La desviación estándar calcula la raíz cuadrada de la desviación cuadrática media del promedio, una métrica que explota cuando existen outliers porque elevar al cuadrado amplifica grandes distancias. En contraste, MAD utiliza la mediana, que es un estimador resistente al punto de interrupción que ignora la magnitud de los outliers extremos hasta el 50% del volumen de datos. Para implementaciones de SQL ANSI, esto significa que un solo mal funcionamiento de un sensor enviando un valor de 9999 inflará bruscamente STDDEV pero dejará MAD casi sin cambios, previniendo la inflación falsa de umbrales que enmascaran futuras anomalías sutiles.
¿Cómo difieren PERCENTILE_CONT y PERCENTILE_DISC al calcular medianas para lecturas de sensores discretos, y cuál deberías usar para MAD?
PERCENTILE_CONT(0.5) realiza una interpolación lineal entre los dos valores centrales cuando el conteo de filas es par, devolviendo un valor hipotético que podría no existir en tu tabla (por ejemplo, promediando 20 y 30 para devolver 25). PERCENTILE_DISC(0.5) devuelve el valor más pequeño actual del conjunto de datos cuya distribución acumulativa es mayor o igual a 0.5. Para el cálculo de MAD en lecturas discretas de sensores enteros, PERCENTILE_DISC es a menudo más seguro porque garantiza que el umbral corresponda a una medición real observada, evitando desviaciones fraccionales que complican la interpretación.
¿Se puede calcular MAD sin CTEs utilizando un solo auto-join, y cuáles son las compensaciones de rendimiento?
Sí, pero es ineficiente. Puedes auto-unir la tabla en sensor_id para comparar cada fila contra cada otra fila para encontrar la mediana, pero esto resulta en una complejidad de O(n²). Alternativamente, el uso de una subconsulta derivada para calcular la mediana primero, luego unirse de nuevo para calcular las desviaciones, obliga a la base de datos a materializar resultados intermedios o volver a escanear la tabla múltiples veces. Los CTE permiten al optimizador tratar el cálculo de la mediana como un spool o tabla de trabajo que se calcula una vez y se reutiliza, resultando típicamente en una sola operación de ordenación y complejidad lineal O(n log n). Los candidatos a menudo olvidan que los optimizadores de SQL ANSI pueden transformar los CTE en tablas de trabajo internas, haciéndolos más eficientes que las subconsultas correlacionadas en la lista SELECT.