Как посчитать перцентили в SQL

Зачем перцентили

Перцентили показывают distribution. p50 = median, p95 = «худшие 5%», p99 = tail. SLO для latency обычно p95/p99. Avg latency хорошее, p99 ужасное → real UX broken.

PERCENTILE_CONT

Один перцентиль:

SELECT
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95_latency
FROM api_logs
WHERE DATE = CURRENT_DATE - 1;

Несколько перцентилей сразу

SELECT
    PERCENTILE_CONT(ARRAY[0.25, 0.5, 0.75, 0.90, 0.95, 0.99])
    WITHIN GROUP (ORDER BY response_time_ms) AS percentiles
FROM api_logs
WHERE DATE = CURRENT_DATE - 1;

Returns array: [p25, p50, p75, p90, p95, p99].

Альтернатива:

SELECT
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY response_time_ms) AS p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY response_time_ms) AS p50,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY response_time_ms) AS p75,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time_ms) AS p99
FROM api_logs
WHERE DATE = CURRENT_DATE - 1;

По группам

SELECT
    endpoint,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY response_time_ms) AS p50,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time_ms) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY response_time_ms) AS p99,
    COUNT(*) AS requests
FROM api_logs
WHERE DATE >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY endpoint
ORDER BY p95 DESC;

Endpoints с highest p95 — first для optimization.

NTILE

Bucket users into N percentile buckets:

SELECT
    user_id,
    revenue_30d,
    NTILE(10) OVER (ORDER BY revenue_30d) AS decile,
    -- Decile 10 = top 10%
    CASE NTILE(10) OVER (ORDER BY revenue_30d)
        WHEN 10 THEN 'whale (top 10%)'
        WHEN 9 THEN 'high-value (top 20%)'
        ELSE 'regular'
    END AS segment
FROM user_revenue
WHERE revenue_30d > 0;

NTILE — для labeling, не для exact percentile values.

Latency dashboard pattern

SELECT
    DATE_TRUNC('hour', TIMESTAMP) AS hour,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY latency_ms) AS p50,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99,
    MAX(latency_ms) AS p100_max,
    COUNT(*) AS requests
FROM api_logs
WHERE TIMESTAMP >= NOW() - INTERVAL '24 hours'
GROUP BY 1
ORDER BY 1;

Частые ошибки

Ошибка 1. Avg latency vs p99. Avg 50ms, p99 5000ms — average misses tail.

Ошибка 2. PERCENTILE_CONT slow. Big tables → use approximate (CH quantileTDigest, BQ APPROX_QUANTILES).

Ошибка 3. Wrong percentile direction. 0.95 для top 5% от distribution. Не «95%» от sample.

Ошибка 4. Bucketing неправильно. NTILE(10) даёт ranks 1-10. Top decile = NTILE = 10, не 1.

Ошибка 5. Empty bins. Если sparse, перцентиль может быть NULL. NULL handling.

Связанные темы

• Как посчитать медиану в SQL
• Как посчитать standard deviation в SQL
• Как посчитать moving average в SQL
• Window functions advanced

FAQ

Какие перцентили track?

Latency: p50, p95, p99. Revenue: p25, p50, p75. Users: deciles.

PERCENTILE_CONT array syntax?

PostgreSQL поддерживает: PERCENTILE_CONT(ARRAY[...]). Other dialects могут отличаться.

Approximate quantiles?

ClickHouse: quantileTDigest. BigQuery: APPROX_QUANTILES. Snowflake: APPROX_PERCENTILE. Trade accuracy for speed.

NTILE vs PERCENT_RANK?

NTILE — buckets (1..N). PERCENT_RANK — fractional rank (0..1).

p99 vs max?

Max — worst single. p99 — 99th percentile, robust to outliers.