PERCENTILE_CONT en PostgreSQL: mediana y percentiles con WITHIN GROUP

PERCENTILE_CONT es una agregacion de conjunto ordenado que devuelve el valor en una fraccion dada de un conjunto ordenado, interpolando entre puntos vecinos cuando hace falta. Es la via directa a la mediana, al p95 de latencia y a cualquier percentil, todo dentro de un solo SELECT.

La sintaxis WITHIN GROUP

PERCENTILE_CONT es una agregacion de conjunto ordenado, asi que el orden no va dentro de los parentesis sino en una clausula aparte WITHIN GROUP (ORDER BY ...). El argumento entre parentesis es una fraccion entre 0 y 1.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Detalles clave:

• El argumento 0.5 es el percentil como fraccion: 0.5 es la mediana, 0.95 es el p95, 0.99 es el p99.
• El ORDER BY es obligatorio y decide sobre que columna se construye la distribucion.
• La columna debe ser numerica o un intervalo; para texto usa PERCENTILE_DISC.
• Los valores NULL se ignoran, igual que en las agregaciones normales.

Interpolacion: en que se diferencia CONT de DISC

La palabra "continuous" significa que cuando la posicion del percentil cae entre dos filas, la funcion devuelve su interpolacion lineal en lugar de uno de los valores reales. Con un numero par de filas, la mediana de PERCENTILE_CONT es el promedio de los dos valores centrales.

-- amounts: 10, 20, 30, 40
-- CONT median interpolates between 20 and 30 -> 25
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS cont_median
FROM orders;

-- DISC returns an actual row value -> 20
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS disc_median
FROM orders;

Cuando elegir cada una:

• PERCENTILE_CONT para sumas, duraciones y latencia: la interpolacion da un valor suave.
• PERCENTILE_DISC cuando necesitas un valor que existe de verdad en el conjunto (un amount real de un pedido o una categoria de texto).

Varios percentiles con un array

Pasa un array de fracciones y la funcion devuelve un array de valores en una sola pasada, mas barato que tres llamadas separadas e ideal para un panel de latencia.

SELECT
    PERCENTILE_CONT(ARRAY[0.5, 0.95, 0.99])
        WITHIN GROUP (ORDER BY amount) AS p50_p95_p99
FROM orders
WHERE status = 'paid';

El resultado es un numeric[]; desempaquetalo por indice:

SELECT
    pct[1] AS p50,
    pct[2] AS p95,
    pct[3] AS p99
FROM (
    SELECT PERCENTILE_CONT(ARRAY[0.5, 0.95, 0.99])
        WITHIN GROUP (ORDER BY amount) AS pct
    FROM orders
    WHERE status = 'paid'
) s;

p95 por grupo y por que supera al AVG

PERCENTILE_CONT combina de forma natural con GROUP BY: calcula la mediana salarial por departamento o el p95 del importe por pais en una sola consulta.

SELECT
    dept,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    AVG(salary)                                         AS avg_salary
FROM employees
GROUP BY dept
ORDER BY median_salary DESC;

La media es sensible a los valores atipicos: un contrato gigante o un timeout de 30 segundos arrastra el AVG hacia arriba, y la metrica deja de describir al usuario "tipico". La mediana y el p95 son mucho mas robustos.

-- p95 latency by country: the SLO-relevant number, not the average
SELECT
    u.country,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY o.amount) AS p95_amount
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.country
ORDER BY p95_amount DESC;

Trampa: PERCENTILE_CONT no se puede escribir como una funcion de ventana normal con OVER (...). El orden vive en WITHIN GROUP y OVER no se le acopla. Si necesitas un percentil como atributo de ventana, envuelve la agregacion en una subconsulta con GROUP BY y unela de vuelta, o usa percent_rank().

Diferencias en otras bases de datos

• MySQL no tiene PERCENTILE_CONT. Las medianas y percentiles se emulan con la funcion de ventana PERCENT_RANK() o ROW_NUMBER() filtrada por posicion, lo cual es verboso y carece de interpolacion honesta de serie.
• ClickHouse ofrece quantile(0.95)(amount) (aproximado, rapido) y quantileExact(0.95)(amount) (exacto). Para la mediana existe el alias median(amount). La sintaxis prescinde de WITHIN GROUP: la fraccion va en los parametros de la funcion. Un matiz importante: quantile y quantileTDigest interpolan entre valores vecinos, pero quantileExact no interpola. Devuelve un elemento real de los datos, igual que PERCENTILE_DISC, asi que no lo uses cuando quieras precisamente un p95 interpolado.

-- ClickHouse: quantileExact returns an actual element (no interpolation)
SELECT quantileExact(0.95)(amount) AS p95_amount
FROM orders
WHERE status = 'paid';

Si lo que quieres es el valor de cola interpolado, usa en su lugar quantile(0.95)(amount) o quantileTDigest(0.95)(amount).

PERCENTILE_CONT es la forma estandar, legible y precisa de obtener la mediana y los percentiles de cola en PostgreSQL. Usala en metricas de latencia y distribuciones monetarias, y reserva AVG para los casos en que los atipicos realmente no importan.