[PostgreSQL] ntile(4) / percent_rank() / cume_dist() — 분위수 버킷으로 나누기

설치·접속: PostgreSQL 설치와 접속

부제: 학생 성적(또는 고객 결제액)을 사분위(4등분)로 나눠 "상위 25%"를 라벨링할 때

SELECT student_id, score,
       ntile(4)      OVER w                       AS quartile,
       round(percent_rank() OVER w::numeric, 3)   AS pct_rank,
       round(cume_dist()    OVER w::numeric, 3)   AS cume_dist
FROM scores
WINDOW w AS (ORDER BY score)
ORDER BY score;

 student_id | score | quartile | pct_rank | cume_dist 
------------+-------+----------+----------+-----------
          2 |    33 |        1 |    0.000 |     0.050
          4 |    36 |        1 |    0.053 |     0.100
          6 |    39 |        1 |    0.105 |     0.150
          8 |    42 |        1 |    0.158 |     0.200
         10 |    45 |        1 |    0.211 |     0.250
         12 |    48 |        2 |    0.263 |     0.300
         14 |    51 |        2 |    0.316 |     0.350
         16 |    54 |        2 |    0.368 |     0.400
         18 |    57 |        2 |    0.421 |     0.450
         20 |    60 |        2 |    0.474 |     0.500
          1 |    67 |        3 |    0.526 |     0.550
          3 |    70 |        3 |    0.579 |     0.600
          5 |    73 |        3 |    0.632 |     0.650
          7 |    76 |        3 |    0.684 |     0.700
          9 |    79 |        3 |    0.737 |     0.750
         11 |    82 |        4 |    0.789 |     0.800
         13 |    85 |        4 |    0.842 |     0.850
         15 |    88 |        4 |    0.895 |     0.900
         17 |    91 |        4 |    0.947 |     0.950
         19 |    94 |        4 |    1.000 |     1.000
(20 rows)

ntile(4)는 정렬된 행을 최대한 균등하게 4개 버킷으로 쪼개 각 행에 버킷 번호(14)를 붙인다. 20행이면 5행씩 깔끔하게 나뉜다. quartile=4가 상위 25%다. percent_rank()는 "나보다 낮은 행의 비율"(01, 최솟값은 0), cume_dist()는 "나 이하인 행의 누적 비율"(0 초과~1)로, 백분위(percentile) 라벨을 붙일 때 쓴다. 셋 다 core 윈도우 함수라 OVER (ORDER BY ...)만 있으면 된다. 여기선 세 함수가 같은 정렬을 공유하므로 WINDOW w AS (...) 절로 한 번만 정의해 재사용했다.

주의할 점은 행 수가 버킷 수로 딱 나눠떨어지지 않을 때다. ntile은 앞쪽 버킷부터 한 행씩 더 채운다. 예컨대 22행을 4로 나누면 앞의 두 버킷은 6행, 뒤의 두 버킷은 5행이 된다. 또 ntile은 값이 아니라 위치로 자르기 때문에, 경계에서 같은 점수가 서로 다른 버킷에 들어갈 수 있다. "값 기준"으로 정확히 자르고 싶으면 ntile 대신 percentile_cont로 임계값을 구해 CASE로 라벨링해야 한다.

이렇게도 쓴다

버킷별 요약(인원수·최저·최고 점수)으로 사분위 경계를 확인한다. (조합: GROUP BY)

SELECT quartile, count(*) n, min(score) lo, max(score) hi
FROM (SELECT score, ntile(4) OVER (ORDER BY score) quartile FROM scores) s
GROUP BY quartile ORDER BY quartile;

 quartile | n | lo | hi 
----------+---+----+----
        1 | 5 | 33 | 45
        2 | 5 | 48 | 60
        3 | 5 | 67 | 79
        4 | 5 | 82 | 94
(4 rows)

 

10등분(십분위)이 필요하면 인자만 바꾼다. ntile(10), ntile(100)(백분위).

SELECT student_id, score, ntile(10) OVER (ORDER BY score) AS decile FROM scores;

 

높은 점수가 1등이 되게 하려면 정렬을 뒤집는다. ORDER BY score DESC → quartile 1이 최상위.

SELECT student_id, score, ntile(4) OVER (ORDER BY score DESC) AS tier FROM scores;

 

값 기준으로 정확히 자르려면 percentile_cont로 임계값을 먼저 구한다. (대비: 위치 자르기 vs 값 자르기)

SELECT percentile_cont(ARRAY[0.25,0.5,0.75]) WITHIN GROUP (ORDER BY score) FROM scores;

 

그룹별(예: 반별)로 각각 사분위를 매기려면 PARTITION BY를 더한다. (조합: 파티션)

SELECT student_id, score, ntile(4) OVER (PARTITION BY class_id ORDER BY score) AS q
FROM scores;   -- class_id 컬럼이 있을 때