[SQL] NTILE 사용법 및 예제

안녕하세요.

오늘은 SQL 대표적인 그룹 분석함수 NTILE에 대해 간단한 예제를 통해 알아보겠습니다.

 

 SQL NTILE 개념 및 예제

▣ NTILE 함수란?

행 데이터를 그룹별로 나누어 차례대로 행 번호를 부여하는 SQL의 분석함수

- 전체 행 데이터 수(N)를 그룹(G)으로 나누었을 때 나머지가 존재하면 첫 번째 그룹부터 나머지가 안남을 때까지 1씩 부여함.

(Ex. N=20, G= 3이면 3번 그룹까지 각 6개의 행을 부여하고 1,2번 그룹에 1행씩 추가 부여)

 

: Syntax

SELECT NTILE([나눌 그룹의 정수]) OVER (PARTITION BY [col1] ORDER BY [col2])

- PARTITION BY를 생략하면 전체 행(N)에 대해서 그룹화가 수행되고 PARTITION BY를 지정하면 해당 파티션(컬럼) 내에서 그룹화를 진행하여 행 번호를 부여함.

 

▣ NTILE 예제

: 예시 데이터 emp

empno	ename	job	sal
7902	FORD	ANALYST	3000
7788	SCOTT	ANALYST	3000
7369	SMITH	CLERK	800
7900	JAMES	CLERK	950
7876	ADAMS	CLERK	1100
7934	MILLER	CLERK	1300
7782	CLARK	MANAGER	2450
7698	BLAKE	MANAGER	2850
7566	JONES	MANAGER	2975
7839	KING	PRESIDENT	5000
7654	MARTIN	SALESMAN	1250
7521	WARD	SALESMAN	1250
7844	TURNER	SALESMAN	1500
7499	ALLEN	SALESMAN	1600

1) 전체 데이터에 대해서 4개의 그룹(NG)으로 행 번호 부여하기

- PARTITION BY를 생략하여 전체 데이터에 대한 그룹화를 진행하고 1~2번 그룹에 하나의 번호 추가 부여

select empno, ename, sal, NTILE(4) over (order by sal desc) as NG
from emp;

: 결과 (NG 포함)

empno	ename	sal	NG
7839	KING	5000	1
7902	FORD	3000	1
7788	SCOTT	3000	1
7566	JONES	2975	1
7698	BLAKE	2850	2
7782	CLARK	2450	2
7499	ALLEN	1600	2
7844	TURNER	1500	2
7934	MILLER	1300	3
7654	MARTIN	1250	3
7521	WARD	1250	3
7876	ADAMS	1100	4
7900	JAMES	950	4
7369	SMITH	800	4

 

2) 전체 행 그룹화를 수행하고 job 별로 그룹화하여 번호 부여하기

- PARTITION BY job을 통하여 job별로 2개로 그룹화 진행

select empno, ename, job, sal, NTILE(2) over (partition by job order by sal desc) as JNG
from emp;

: 결과

empno	ename	job	sal	JNG
7902	FORD	ANALYST	3000	1
7788	SCOTT	ANALYST	3000	2
7934	MILLER	CLERK	1300	1
7876	ADAMS	CLERK	1100	1
7900	JAMES	CLERK	950	2
7369	SMITH	CLERK	800	2
7566	JONES	MANAGER	2975	1
7698	BLAKE	MANAGER	2850	1
7782	CLARK	MANAGER	2450	2
7839	KING	PRESIDENT	5000	1
7499	ALLEN	SALESMAN	1600	1
7844	TURNER	SALESMAN	1500	1
7654	MARTIN	SALESMAN	1250	2
7521	WARD	SALESMAN	1250	2