게시일: Dec 12, 2011 11:0:18 PM
- 계산 대상 데이터(로우)들을 특정한 기준으로 분할하여 그 결과값을 반환하는 분석 함수이다.
[구분 형식]
NTILE( expr ) OVER ( PARTITION BY expr1 ORDER BY expr2 [ASC | DESC] )
- 정렬된 PARTITION 별로 expr에 지정된 숫자만큼 각 로우를 분할
예를들어, 지정된 그룹이 총 100개의 결과 셋을 반환하는데 NTILE(4)를 사용하면, 각 로우를 순서대로 25%(100/4)씩 나누어 1부터 4까지 반환한다.
다시 말해 25%씩 4등급으로 나누어 순번을 매긴다. 즉, 상위 25%는 1, 그 다음 25%는 2, 다음은 3, 마지막은 4를 반환한다.
만약 결과 셋이 103개일 경우에는 정확시 25%씩 나누면 나머지가 생기게 되는데, 이 경우에는 최초 값부터 1개씩 추가로 분배하게 된다.
따라서 이런 경우에는 상위 26개의 로우가 1, 그 다음 26개의 로우가 2, 그 다음 26개가 3, 나머지 25개의 로우는 4를 반환한다.
select id
, NTILE(5) OVER (ORDER BY SUM(salary) DESC) rank -- 20%(100/5)씩 나눈다
, sum(salary)
from (
select 10 dept, 100 id, 39000 salary from dual union all
select 20 dept, 101 id, 30000 salary from dual union all
select 20 dept, 102 id, 9000 salary from dual union all
select 30 dept, 103 id, 17000 salary from dual union all
select 40 dept, 104 id, 60000 salary from dual union all
select 20 dept, 105 id, 9500 salary from dual union all
select 30 dept, 106 id, 17000 salary from dual union all
select 40 dept, 107 id, 37500 salary from dual union all
select 40 dept, 108 id, 25000 salary from dual union all
select 30 dept, 109 id, 5000 salary from dual union all
select 40 dept, 110 id, 17000 salary from dual
)
group by id;
---------------------------
ID RANK SUM(SALARY)
---------------------------
104 1 60000 -- 상위 20% 급여자
100 1 39000 -- 상위 20% 급여자
107 1 37500 -- 상위 20% 급여자
---------------------------
101 2 30000
108 2 25000
---------------------------
103 3 17000
110 3 17000
---------------------------
106 4 17000
105 4 9500
---------------------------
102 5 9000
109 5 5000
---------------------------