Agrupación en intervalos con NTILE y Cume_Dist

NTILE: grupos de igual tamaño

SELECT id, total,
       NTILE(4) OVER (ORDER BY total) AS quartile
FROM orders;
-- 1 = bottom 25%, 2 = next, 3, 4 = top 25%

Grupos por percentiles

SELECT id, score,
       NTILE(100) OVER (ORDER BY score DESC) AS percentile
FROM students;
-- percentile 1 = top 1%

PERCENT_RANK

SELECT id, score,
       PERCENT_RANK() OVER (ORDER BY score) AS pct_rank
FROM students;
-- 0 for the lowest, 1 for the highest.

CUME_DIST

SELECT id, score,
       CUME_DIST() OVER (ORDER BY score) AS cume_dist
FROM students;
-- "What share of students scored ≤ this one?"

Estadísticas de percentiles con PERCENTILE_CONT

SELECT
  PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY total) AS median,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total) AS p99
FROM orders;

PERCENTILE_DISC

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total)

NTILE en particiones

SELECT user_id, total,
       NTILE(4) OVER (PARTITION BY user_id ORDER BY total) AS user_quartile
FROM orders;

Histograma mediante NTILE

SELECT bucket, MIN(total) AS bucket_min, MAX(total) AS bucket_max, COUNT(*)
FROM (
  SELECT NTILE(10) OVER (ORDER BY total) AS bucket, total
  FROM orders
) sub
GROUP BY bucket
ORDER BY bucket;

Agregación por grupo

SELECT user_quartile, COUNT(*) AS users, AVG(total) AS avg_total
FROM (
  SELECT user_id, NTILE(4) OVER (ORDER BY total) AS user_quartile, total
  FROM orders
) sub
GROUP BY user_quartile;

Combinar funciones de ventana

SELECT id, total,
       NTILE(4) OVER w     AS quartile,
       PERCENT_RANK() OVER w AS pct_rank,
       AVG(total) OVER w     AS overall_avg
FROM orders
WINDOW w AS (ORDER BY total);

Cuándo NTILE puede inducir a error

Resumen

Comprobación rápida