PERCENTILE_CONT no PostgreSQL: mediana e percentis com WITHIN GROUP

PERCENTILE_CONT e uma agregacao de conjunto ordenado que retorna o valor em uma fracao dada de um conjunto ordenado, interpolando entre pontos vizinhos quando preciso. E o caminho direto para a mediana, para o p95 de latencia e para qualquer percentil, tudo dentro de um unico SELECT.

A sintaxe WITHIN GROUP

PERCENTILE_CONT e uma agregacao de conjunto ordenado, entao a ordenacao nao fica dentro dos parenteses mas em uma clausula separada WITHIN GROUP (ORDER BY ...). O argumento entre parenteses e uma fracao entre 0 e 1.

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

Detalhes importantes:

• O argumento 0.5 e o percentil como fracao: 0.5 e a mediana, 0.95 e o p95, 0.99 e o p99.
• O ORDER BY e obrigatorio e decide sobre qual coluna a distribuicao e construida.
• A coluna deve ser numerica ou um intervalo; para texto use PERCENTILE_DISC.
• Valores NULL sao ignorados, assim como nas agregacoes comuns.

Interpolacao: como CONT difere de DISC

A palavra "continuous" significa que, quando a posicao do percentil cai entre duas linhas, a funcao retorna a interpolacao linear delas em vez de um dos valores reais. Para um numero par de linhas, a mediana do PERCENTILE_CONT e a media dos dois valores centrais.

-- 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;

Quando escolher cada uma:

• PERCENTILE_CONT para somas, duracoes e latencia: a interpolacao da um valor suave.
• PERCENTILE_DISC quando voce precisa de um valor que existe de fato no conjunto (um amount real de um pedido ou uma categoria de texto).

Varios percentis com um array

Passe um array de fracoes e a funcao retorna um array de valores em uma unica passada, mais barato que tres chamadas separadas e ideal para um painel 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';

O resultado e um numeric[]; desempacote-o 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 e por que supera o AVG

PERCENTILE_CONT combina naturalmente com GROUP BY: calcule a mediana salarial por departamento ou o p95 do valor do pedido por pais em uma so 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;

A media e sensivel a valores extremos: um contrato gigante ou um timeout de 30 segundos puxa o AVG para cima, e a metrica deixa de descrever o usuario "tipico". A mediana e o p95 sao bem mais 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;

Pegadinha: PERCENTILE_CONT nao pode ser escrita como uma funcao de janela comum com OVER (...). A ordenacao vive em WITHIN GROUP e o OVER nao se acopla a ela. Se precisar de um percentil como atributo de janela, envolva a agregacao em uma subconsulta com GROUP BY e faca o join de volta, ou recorra a percent_rank().

Diferencas em outros bancos

• MySQL nao tem PERCENTILE_CONT. Medianas e percentis sao emulados com a funcao de janela PERCENT_RANK() ou ROW_NUMBER() filtrada por posicao, o que e verboso e sem interpolacao honesta de fabrica.
• ClickHouse oferece quantile(0.95)(amount) (aproximado, rapido) e quantileExact(0.95)(amount) (exato). Para a mediana ha o alias median(amount). A sintaxe dispensa o WITHIN GROUP: a fracao vai nos parametros da funcao. Uma ressalva importante: quantile e quantileTDigest interpolam entre valores vizinhos, mas quantileExact nao interpola. Ele retorna um elemento real dos dados, assim como o PERCENTILE_DISC, entao nao recorra a ele quando voce quer justamente um p95 interpolado.

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

Se o que voce quer e o valor de cauda interpolado, use no lugar quantile(0.95)(amount) ou quantileTDigest(0.95)(amount).

PERCENTILE_CONT e a forma padrao, legivel e precisa de obter a mediana e os percentis de cauda no PostgreSQL. Use-a em metricas de latencia e distribuicoes monetarias, e deixe o AVG para os casos em que os valores extremos realmente nao importam.