Percentiles y medianas en T-SQL: PERCENTILE_CONT vs PERCENTILE_DISC

En proyectos de Business Intelligence, la media aritmética suele ser el primer indicador que solicitan los usuarios de negocio. Sin embargo, en sectores como el retail o la industria, la media es un valor profundamente engañoso. Un solo pedido atípico de gran volumen o un error en la lectura de un sensor pueden distorsionar el promedio, ofreciendo una visión irreal de la operativa. Aquí es donde entran en juego la mediana y los percentiles.

Como consultores de datos, nuestra responsabilidad es decidir dónde realizar estos cálculos. Aunque Power BI permite calcular medianas de forma nativa, delegar esta lógica a SQL Server suele ser la decisión correcta cuando trabajamos con grandes volúmenes de datos o cuando necesitamos que el dato esté precalculado en nuestra capa de transformación para garantizar la consistencia en múltiples informes. En este artículo analizaremos cómo implementar estas métricas utilizando T-SQL, evaluando el coste de rendimiento y las diferencias críticas entre funciones discretas y continuas.

El problema principal de los percentiles en SQL es que no son funciones de agregado estándar como SUM() o AVG(). Son funciones analíticas que requieren una ordenación previa de los datos, lo que introduce un coste computacional que debemos monitorizar antes de desplegar en producción.

La diferencia fundamental: PERCENTILE_CONT vs PERCENTILE_DISC

SQL Server nos ofrece dos funciones principales para calcular percentiles: PERCENTILE_CONT (Continuo) y PERCENTILE_DISC (Discreto). Entender la diferencia entre ambas es vital para no entregar resultados matemáticamente incorrectos al negocio.

PERCENTILE_CONT calcula un percentil basado en una distribución continua de los datos. Si el percentil buscado cae entre dos valores existentes, la función realiza una interpolación lineal para encontrar el resultado. El valor devuelto suele ser un tipo de dato decimal o de coma flotante, incluso si los datos originales son enteros.

PERCENTILE_DISC, por el contrario, devuelve un valor que existe realmente en el conjunto de datos. Calcula la frecuencia acumulada y elige el primer valor cuya frecuencia sea mayor o igual al percentil especificado. Es ideal cuando el negocio necesita identificar un elemento real de la tabla (por ejemplo, el precio exacto de un producto que represente el corte del cuartil).

Si estamos calculando la mediana de un conjunto de datos como {10, 20}, PERCENTILE_CONT(0.5) nos devolverá 15.0, mientras que PERCENTILE_DISC(0.5) nos devolverá 10. Esta distinción puede parecer trivial, pero en auditorías de facturación o análisis de KPIs industriales, la diferencia es crítica.

Implementación de medianas y cuartiles por categoría

Para implementar estas funciones, necesitamos la cláusula WITHIN GROUP (ORDER BY columna) seguida de la partición analítica OVER(). A diferencia de otras funciones de ventana, aquí el orden es obligatorio dentro del grupo de la función, no en la cláusula OVER.

Imaginemos un escenario de retail donde queremos calcular la mediana de ventas y el primer y tercer cuartil para segmentar nuestros productos según su rendimiento. Para profundizar en cómo estructurar estas consultas complejas, recomiendo revisar nuestro artículo sobre Seleccionar sobre resultados de consulta: CTEs y Subconsultas en el mundo real.

-- Cálculo de mediana y cuartiles en una tabla de Ventas
SELECT 
 ProductoID,
 Importe,
 -- Mediana continua (Percentil 50)
 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Importe) 
 OVER (PARTITION BY CategoriaID) AS MedianaImporte,
 -- Primer Cuartil (25%)
 PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Importe) 
 OVER (PARTITION BY CategoriaID) AS Q1,
 -- Tercer Cuartil (75%)
 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Importe) 
 OVER (PARTITION BY CategoriaID) AS Q3
FROM Fact.Ventas
INNER JOIN Dim.Producto ON Ventas.ProductoKey = Producto.ProductoKey;

En este ejemplo, el uso de PARTITION BY CategoriaID permite que el cálculo se reinicie para cada categoría de producto. Esto es fundamental en modelos en estrella donde queremos enriquecer las dimensiones o las tablas de hechos con métricas de contexto estadístico.

El impacto en el rendimiento: ¿Por qué mi consulta va lenta?

Calcular percentiles es costoso. A diferencia de un SUM que puede resolverse con un simple escaneo de índice, un percentil requiere que el motor de SQL Server ordene los datos para cada partición definida. En tablas con millones de filas, esto se traduce en un operador de Sort masivo en el plan de ejecución.

Cuando el motor no tiene suficiente memoria para realizar esta ordenación (Memory Grant), utiliza tempdb para volcar los datos, lo que degrada drásticamente el rendimiento. Si estás trabajando en entornos modernos, como los descritos en Pruebas de rendimiento en Fabric Warehouse: metodología y métricas reales, verás que la concurrencia puede verse afectada si lanzamos estas métricas sobre tablas de hechos sin filtrar.

Para diagnosticar estos cuellos de botella, es imperativo leer planes de ejecución en SQL Server y buscar operadores de tipo Window Spool o advertencias de Spill to tempdb. Si el rendimiento es inasumible, una alternativa es el uso de OFFSET / FETCH para calcular la mediana de forma manual en una subconsulta, lo cual a veces permite aprovechar mejor los índices existentes.

Alternativa de alto rendimiento para tablas masivas

Si solo necesitas la mediana y la tabla es excesivamente grande, puedes optar por un enfoque basado en ROW_NUMBER(). Aunque requiere más líneas de código, suele ser más eficiente bajo ciertas condiciones de indexación.

-- Alternativa manual para la mediana en grandes volúmenes
WITH VentasOrdenadas AS (
 SELECT 
 Importe,
 ROW_NUMBER() OVER (ORDER BY Importe) AS Fila,
 COUNT(*) OVER () AS TotalFilas
 FROM Fact.Ventas
 WHERE FechaKey >= 20230101
)
SELECT AVG(Importe * 1.0) AS Mediana
FROM VentasOrdenadas
WHERE Fila IN ((TotalFilas + 1) / 2, (TotalFilas + 2) / 2);

Este método evita la sobrecarga de las funciones analíticas específicas de percentiles y permite un control más granular sobre los filtros aplicados en el WHERE, algo que a veces penaliza si se combina con OR en la cláusula principal, como explicamos en OR en el WHERE: por qué penaliza y cómo usar UNION ALL para ganar rendimiento.

Integración con la arquitectura de datos y Power BI

Desde la perspectiva de un arquitecto de BI, calcular estos valores en SQL tiene una ventaja estratégica: el linaje de datos. Si calculamos la mediana en DAX mediante MEDIANX, esa lógica solo vive en el modelo semántico. Si mañana necesitamos ese mismo valor en un Notebook de Python en Fabric o en un informe de Reporting Services, tendremos que duplicar la lógica.

Al mover el cálculo a la capa de transformación (vistas de SQL o procesos de ETL), garantizamos que el dato es «oficial». Esto es especialmente relevante en escenarios de columnas condicionales y personalizadas en Power Query, donde podemos importar el valor ya calculado y usarlo para etiquetar filas como «Por encima de la mediana» sin cargar el motor de Mashup con cálculos estadísticos pesados.

Recuerda: Si el conjunto de datos cambia con frecuencia y el usuario necesita interactividad total (filtros cruzados que afecten a la mediana), DAX es tu única opción. Si la mediana es estática por periodo o categoría, SQL es el lugar donde debe residir.

Errores comunes al calcular percentiles

• No manejar los NULLs: Las funciones de percentiles ignoran los valores nulos, pero si toda la partición es nula, el resultado será NULL. Esto puede romper cálculos posteriores si no se usa ISNULL o COALESCE.
• Confundir tipos de datos: PERCENTILE_CONT siempre devuelve un tipo de datos de alta precisión. Si intentas insertar esto en una tabla con tipos de datos rígidos sin hacer un CAST, podrías perder precisión o provocar errores de desbordamiento.
• Ignorar el filtrado previo: Lanzar un percentil sobre toda la tabla de hechos es un error de principiante. Filtra siempre por fechas o dimensiones relevantes antes de aplicar la función analítica.
• Uso excesivo de OVER(): Cada cláusula OVER con una partición distinta puede forzar una nueva ordenación de los datos. Intenta agrupar tus cálculos estadísticos bajo las mismas particiones siempre que sea posible.

Conclusiones para el consultor de BI

El uso de PERCENTILE_CONT y PERCENTILE_DISC eleva la calidad analítica de nuestros DWH. Nos permite pasar de simples promedios a análisis de dispersión y detección de outliers con un código limpio y mantenible. Sin embargo, la elegancia de la sintaxis no debe cegarnos ante el coste de computación. En entornos de producción, mide siempre el impacto en la memoria y asegúrate de que los índices de cobertura están alineados con tus cláusulas ORDER BY dentro del grupo.

Checklist de implementación

• Identificar si el negocio requiere un valor real (DISC) o un valor calculado (CONT).
• Verificar que las columnas utilizadas en ORDER BY dentro de WITHIN GROUP están indexadas si el volumen supera el millón de filas.
• Validar el impacto en el plan de ejecución buscando operadores de ordenación (Sort) que desborden a tempdb.
• Asegurarse de que el cálculo se realiza tras filtrar los datos (WHERE) para minimizar el conjunto de trabajo.
• Documentar en el diccionario de datos si la mediana se calcula a nivel de transacción o a nivel agregado por día/producto.

Preguntas frecuentes

¿Puedo usar PERCENTILE_CONT en Azure SQL y Microsoft Fabric?

Sí, estas funciones son totalmente compatibles con Azure SQL Database y con los Warehouses de Microsoft Fabric. En el caso de Fabric, la computación distribuida ayuda a manejar estos cálculos en tablas de gran tamaño, aunque se recomienda monitorizar las unidades de capacidad (CU) consumidas.

¿Qué ocurre si el conjunto de datos está vacío?

Si la consulta no devuelve filas o si todos los valores de la columna en el WITHIN GROUP son NULL, la función devolverá NULL. No generará un error de ejecución, pero debes prever este comportamiento en tu lógica de negocio.

¿Es mejor calcular percentiles en SQL o en Power BI con DAX?

Depende de la granularidad. Si el percentil debe recalcularse dinámicamente según los filtros que elija el usuario en el informe, usa DAX. Si el percentil es una métrica fija (ej. Mediana de ventas del mes anterior) que se usará en múltiples informes, calcúlalo en SQL para mejorar el rendimiento del informe y asegurar la consistencia.