Al establecer límites de valores atípicos estadísticos para métricas de control de calidad, ¿cómo se calcula el **rango intercuartílico (IQR)** dentro de conjuntos de datos agrupados utilizando estrictamente funciones de ventana y agregados de **ANSI SQL**, y luego se utiliza esta medida para filtrar observaciones que caen más allá de 1.5×IQR de los cuartiles?

Respuesta a la pregunta

Historia de la pregunta

El concepto de IQR se origina de la metodología de análisis exploratorio de datos de John Tukey desarrollada en la década de 1970, proporcionando una estadística robusta para la detección de valores atípicos que sigue siendo resistente a valores extremos. A medida que el almacenamiento de datos evolucionó, los analistas pasaron de paquetes estadísticos procedimentales a consultas SQL basadas en conjuntos, requiriendo implementaciones nativas de base de datos de estos cálculos. ANSI SQL:2003 introdujo funciones de distribución inversa, posteriormente perfeccionadas en SQL:2011, que permiten cálculos percentiles directamente dentro del motor de base de datos sin procesamiento externo.

El problema

El desafío requiere calcular el primer cuartil (Q1, percentil 25) y el tercer cuartil (Q3, percentil 75) para cada subgrupo dentro de un conjunto de datos para derivar el IQR (Q3 menos Q1). Una vez establecido, se definen los límites de valores atípicos estadísticos en Q1 − 1.5×IQR y Q3 + 1.5×IQR. La complejidad radica en realizar estos cálculos estadísticos a través de particiones en una sola operación basada en conjuntos mientras se mantiene exactitud, luego filtrar el conjunto de datos original contra estos límites calculados dinámicamente sin recurrir a bucles procedimentales o procesamiento en la capa de aplicación.

La solución

Utilice PERCENTILE_CONT(0.25) y PERCENTILE_CONT(0.75) como funciones de ventana de conjunto ordenado particionadas por la columna de agrupamiento, que realizan interpolación lineal para determinar valores cuartílicos exactos. Calcule el IQR y las condiciones de límite dentro de una Expresión de Tabla Común (CTE), luego únase a este resultado o filtre directamente usando cláusulas WHERE que comparen las mediciones contra los límites calculados.

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;

Situación de la vida real

Una empresa farmacéutica monitorea congeladores de ultra baja temperatura que almacenan inventario de vacunas en 200 zonas de instalaciones. Cada zona genera 10,000 lecturas de temperatura diariamente. La detección de valores atípicos mediante desviación estándar simple falló debido a que fluctuaciones ocasionales de la red eléctrica causaron picos extremos que distorsionaron la media, resultando en falsos negativos para sutiles malfunciones del equipo. El equipo de calidad requería un método estadístico robusto para señalar solo aquellas lecturas que se desviaban significativamente del rango operativo típico de la zona, implementado directamente dentro de su almacén de datos PostgreSQL para alimentar tableros en tiempo real de Tableau.

Solución 1: Procesamiento en la capa de aplicación usando Python y Pandas

Extraiga todos los datos históricos a través de ODBC en un servicio de Python, calcule los cuartiles usando groupby().quantile(), luego filtre y escriba los resultados de vuelta. Pros: bibliotecas estadísticas extremadamente flexibles, fácil depuración con ejecución paso a paso y sintaxis familiar para los científicos de datos. Contras: gran sobrecarga de red al transferir millones de filas, limitaciones de memoria que causan errores de desbordamiento en servidores de aplicación y datos obsoletos debido a ventanas de procesamiento de 45 minutos que hacen que los resultados estén desactualizados al finalizar.

Solución 2: ANSI SQL nativo usando funciones de ventana PERCENTILE_CONT

Implemente la consulta utilizando PERCENTILE_CONT como agregados de conjunto ordenado con cláusulas OVER particionadas por facility_zone. Pros: sin transferencia de datos, aprovecha los índices B-tree existentes en los identificadores de zona, reduce el tiempo de procesamiento a menos de 15 segundos y proporciona resultados en tiempo real directamente consumibles por herramientas de BI. Contras: requiere base de datos compatible con SQL:2003/2011 (no disponible en versiones antiguas de MySQL), crea operaciones de orden temporal que pican CPU durante la ejecución, y presenta una sintaxis compleja poco familiar para muchos desarrolladores de aplicaciones.

Solución 3: Aproximación usando NTILE(4)

Divida las lecturas de cada zona en cuatro cubos iguales utilizando la función de ventana NTILE, luego use MIN() y MAX() en los cubos 1 y 4 para aproximar los límites de Q1 y Q3. Pros: compatible con versiones de bases de datos más antiguas que carecen de funciones de distribución inversa, se ejecuta más rápido debido a cálculos aproximados. Contras: produce solo límites aproximados no aptos para el cumplimiento regulatorio, falla catastróficamente con tamaños de muestra pequeños o valores atados pesados, e introduce un comportamiento no determinista cuando los límites caen entre lecturas de sensores discretos.

Solución elegida y resultado

El equipo seleccionó la Solución 2 (enfoque PERCENTILE_CONT) porque las regulaciones farmacéuticas exigen cálculos estadísticos exactos en lugar de aproximaciones. El administrador de la base de datos creó índices compuestos en (facility_zone, temperature), eliminando las operaciones de orden. La consulta resultante identificó el 0.03% de las lecturas como verdaderos valores atípicos estadísticos, lo que desencadenó inspecciones automáticas de congeladores que evitaron pérdidas de inventario valoradas en aproximadamente $2M anuales, mientras que redujo los costos de infraestructura al eliminar la capa ETL de Python.

Lo que a menudo omiten los candidatos

¿Por qué PERCENTILE_CONT produce resultados diferentes que PERCENTILE_DISC al calcular cuartiles, y cuál debe usarse para IQR?

PERCENTILE_CONT (continuo) realiza una interpolación lineal entre los dos valores más cercanos que rodean la posición percentil solicitada, devolviendo un valor calculado que puede no existir en el conjunto de datos original. PERCENTILE_DISC (discreto) devuelve el menor valor de distribución acumulada mayor o igual al percentil, seleccionando efectivamente una medición observada real. Para cálculos de IQR en detección de valores atípicos, PERCENTILE_CONT es generalmente preferido porque proporciona una escala continua menos sensible a artefactos de muestreo discreto, aunque PERCENTILE_DISC se vuelve necesario cuando los límites de los valores atípicos deben corresponder a valores observados físicamente en lugar de interpolaciones matemáticas.

¿Cómo manejas grupos que contienen menos de cuatro valores distintos donde IQR colapsa matemáticamente a cero o se vuelve indefinido?

Cuando una partición contiene valores idénticos o menos de cuatro puntos de datos, PERCENTILE_CONT devuelve valores idénticos para Q1 y Q3, resultando en un IQR de cero. Esto hace que los límites de los valores atípicos colapsen en el valor mediano, potencialmente marcando cada observación distinta como un valor atípico. Los candidatos deben implementar verificaciones NULLIF o expresiones CASE para detectar escenarios de IQR cero, ya sea devolviendo NULL para el estado de valores atípicos, regresando a métodos de desviación estándar para grupos pequeños o excluyendo explícitamente grupos con COUNT(DISTINCT value) < 4 del análisis de valores atípicos según las reglas comerciales.

¿Qué estrategia de índice optimiza el rendimiento de las funciones de distribución inversa al procesar miles de millones de filas particionadas por categorías de alta cardinalidad?

Dado que PERCENTILE_CONT requiere ordenar cada partición para determinar posiciones percentiles, los candidatos a menudo pasan por alto la necesidad de índices compuestos en (categoría, medición). Dichos índices permiten que el motor de base de datos escanee páginas de índice ya ordenadas, eliminando operaciones de orden externas costosas en disco. Sin estos índices, la base de datos realiza ordenamientos separados para cada partición, causando una severa necesidad de I/O y agotamiento temporal del espacio en disco. Además, los candidatos no reconocen que empujar cláusulas WHERE selectivas en CTEs tempranas reduce el conjunto de trabajo antes de los costosos cálculos percentiles, ya que las funciones de distribución inversa no pueden aprovechar índices una vez que comienza la fase de agregación.