【오라클(Oracle) SQL 함수】 NTILE 함수

o NTILE

 

---

문 법

 

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가 배치된다.

[그림]는 "제품별 판매량을 구하고, 이를 4등급으로 나누어, 다시 등급 내에서 순위를 매겨라"는 질의를 NTILE() 함수를 이용하여 구현한 것이다.

Oracle Program

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

[그림] NTILE() 활용

 

---

패키지 함수 비교 - Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table

• NTILE Oracle Function 프로그래밍 비교

 

---

관련 함수

함수명	함수 내용
CUME_DIST	그룹에 존재하는 관측치의 누적 분포 값을 계산한다.
DENSE_RANK	Rank()와 유사한 함수로 각 그룹별로 ORDER BY절에 사용된 칼럼이나 표현식을 기준으로 정렬하고, 그룹 내의 각 열에 대한 순위를 부여한다.
LAG	이 함수는 Self 조인 작업 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. LAG 함수는 현재 위치를 기준으로 이전에 지정한 행에 접근하여 작업을 수행한다.
LEAD	이 함수는 Self 조인 작업을 수행하지 않고, 하나의 테이블을 접근하면서 여러 개의 행에 동시에 접근(Access)하여 작업한다. 쿼리에서 수행되는 행의 Series나 커서의 위치를 지정하면, LEAD 함수는 해당 위치 이후에 존재하는 물리적 Offset의 행에 ACCESS 한다.
NTILE	그룹 내에서 정렬된 데이터를 expr에 의해 지정된 bucket의 수로 분할하여, 각 행을 적절한 bucket의 번호를 부여하여 할당한다.
PERCENT_RANK	인수로 지정한 값의 그룹 내의 위치를 나타내는 백분위 순위(순위 퍼센트, Percent rank)를 반환한다.
RANK	값의 그룹에서 값의 순위를 계산한다. 반환되는 데이터형은 NUMBER이다.
RATIO_TO_REPORT	개별 그룹에서 관측치 합에 대한 개별 관측치 값의 상대적 비율(구성비, 점유율, Share)을 계산한다.
ROW_NUMBER	이 함수는 파티션 그룹별 또는 전체 데이터에 대하여 order_by_clause에서 지정된 행의 정렬 순위대로 1로 시작하는 중복을 허용하지 않는 유일한(unique) 순서 값을 할당한다. (파티션에서 각 행 또는 쿼리에 의해서 반환되는 각 행에 대하여)
WIDTH_BUCKET	동일한 넓이를 갖는 히스토그램을 생성한다.

 

---

예 제

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

Oracle Program

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

 

Results

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

 

 

---

오라클 SQL 함수(Oracle SQL Function) 목록 리스트 링크

 

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 처리 방법 리스트 링크