NTILE과 Cume_Dist를 활용한 구간 나누기

NTILE: 동일한 크기의 버킷

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

백분위수 버킷

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?"

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 사용

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

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;

버킷별 집계

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;

윈도 함수 결합

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

NTILE이 오해를 일으키는 경우

요약

빠른 확인