NTILE

그림 설명

NTILE함수는 분석 함수 이다. 순서화된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket 번호를 할당한다. buckets은 1~expr의 번호를 붙일수 있다. expr의 값은 각 파티션에 대하여 양의 정수로 변활될 필요가 있다. 오라클 데이터 베이스는 정수로 간주하기 위해, 만약 expr이 정수가 아닌 상수라면, 오라클은 정수로 값을 절삭한다. 반환되는 값은 NUMBER이다.

Bucket에서 행의 수는 대부분 1과 다를수 있다. 나머지 값(bucket에 의해 나누어진 행 수의 나머지)은 bucket 1로 시작하여서, 각 bucket에 하나씩 분할된다.

만약 expr이 행의 수보다 큰 경우에는, buckets의 수는 행의 수와 동일한 채워지고, 나머지는 bucket은 비운다.

expr에 NTILE 또는 다른 분석 함수를 이용할수 없다. 분석함수는 중첩 될수 없고, expr에는 다른 built-in 함수 표현은 사용할수 있다.

NTILE()함수는 정렬된 PARTITION을BUCKET이라 불리는 그룹별로 나누고 PARTITION내의 각 ROW등을 BUCKET에 배치하는 함수로 각 BECKET에는 동일한 수의 ROW가 배치된다. 예를 들어 PARTITION내에 100개의 ROW를 가지고 있고 4개의 BUCKET으로 나누는 NTILE(4)를 사용하면 1개의 BUCKET당 25개의 ROW가 배정된다. 만일 각 PARTION의 수가 정확하게 분배되지 않을 경우 근사치로 배분한 후 남는 값에 대하여 최초 PARTITION부터 한 개씩 배분한다. 즉, 만일 103개의 ROW에 대하여 NTILE(5)를 적용하면 첫번째 BUCKET부터 세번째까지는 21개의 ROW가, 나머지는 20개의 ROW가 배치된다.

SELECT  p_productkey, sum(s_amount) AS sum_s_amount, 
    NTILE(4) over (ORDER BY sum(s_amount) DESC) as 4_tile,
    RANK() OVER (PARTITION BY NTILE(4) over 
    (ORDER BY sum(s_amount) DESC) ORDER BY sum_s_amount DESC) 
    AS rank_in_quartile
FROM product, sales
WHERE p_productkey = s_productkey
GROUP BY p_productkey);

다음 예제는 부서 100으로 부터 oe.employees 테이블의 급여 열에서 값을 4개의 buckets으로 분할한다. 이 부문에서 급여 칼럼은 6개 값이 존재하기 때문에, 2개 여분의 값은 (6/4의 나머지) buckets의 1과 2에 할당된다. 그러므로 bucket 1과 2는 3과 4보다 하나더 할당된다.

SELECT last_name, salary, NTILE(4) OVER (ORDER BY salary DESC) 
   AS quartile FROM employees
   WHERE department_id = 100;

LAST_NAME                     SALARY   QUARTILE
------------------------- ---------- ----------
Greenberg                      12000          1
Faviet                          9000          1
Chen                            8200          2
Urman                           7800          2
Sciarra                         7700          3
Popp                            6900          4