그룹 내 비율 함수

그룹 내 비율 함수

 

* RATIO_TO_REPORT 함수

   

 - 파티션 내 전체 SUM 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있습니다.

   결과 값은 > 0 & <= 1 의 범위 값을 가집니다. 그리고 개별 RATIO의 합을 구하면 1이 됩니다.

   SQL Server에서는 지원하지 않는 함수입니다.

 

 EX)

 

SELECT ENAME
     , SAL
     , ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';

ENAME	SAL	R_R
ALLEN	1600	0.29	(1600 / 5600)
WARD	1250	0.22	(1250 / 5600)
MARTIN	1250	0.22	(1250 / 5600)
TURNER	1500	0.27	(1500 / 5600)

 

실행 결과에서 전체 값은 1650 + 1250 + 1250 + 1500 = 5600이 되고, RATIO_TO_REPORT 함수 연산의 분모로 사용됩니다.

그리고 개별 RATIO의 전체 합을 구하면 1이 되는 것을 확인할 수 있습니다.

 

0.29 + 0.22 + 0.22 + 0.27 = 1

 

 

* PERCENT_RANK 함수

   

 - 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구합니다.

   결과 값은 >= 0 & <= 1의 범위를 가집니다.

   SQL Server에서는 지원하지 않는 함수입니다.

 

 

 EX)

 

SELECT DEPTNO
     , ENAME
     , SAL
     , PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
FROM EMP;

DEPTNO	ENAME	SAL	P_R
10	KING	5000	0
10	CLARK	2450	0.5
10	MILLER	1300	1
20	SCOTT	3000	0
20	FORD	3000	0
20	JONES	2975	0.5
20	ADAMS	1100	0.75
20	SMITH	800	1

 

 

DEPTNO 10의 경우 3건이므로 구간은 2개가 됩니다.

0과 1 사이를 2개의 구간으로 나누면 0, 0.5, 1이 됩니다.

 

DEPTNO 20의 경우 5건이므로 구간은 4개가 됩니다.

0과 1 사이를 4개의 구간으로 나누면 0, 0.25, 0.5, 0.75, 1이 됩니다.

 

SCOTT, FORD의 경우 ORDER BY SAL DESC 구문에 의해 급여가 같으므로 같은 ORDER로 취급합니다.

 

 

* CUME_DIST 함수

   

 - 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구합니다.

   결과 값은 > 0 & <=1 의 범위를 가집니다.

   SQL Server에서는 지원하지 않는 함수입니다.

 

 

 EX)

 

SELECT DEPTNO
     , ENAME
     , SAL
     , CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST
FROM EMP;

DEPTNO	ENAME	SAL	CUME_DIST
10	KING	5000	0.3333
10	CLARK	2450	0.6667
10	MILLER	1300	1.0000
20	SCOTT *	3000	0.4000
20	FORD	3000	0.4000
20	JONES	2975	0.6000
20	ADAMS	1100	0.8000
20	SMITH	800	1.0000

 

DEPTNO가 10인 경우 왼도우가 전체 3건이므로 0.3333 단위의 간격을 가집니다.

즉, 0.3333, 0.6667, 1의 값이 됩니다.

 

DEPTNO가 20인 경우 왼두가 전체 5건이므로 0.2000 단위의 간격을 가집니다.

즉, 0.2000, 0.4000, 0.6000, 0.8000, 1의 값이 됩니다.

 

* 표시가 있는 SCOTT, FORD의 경우 ORDER BY SAL에 의해 SAL이 같으므로 같은 ORDER로 취급합니다.

  다른 WINDOW 함수의 경우 동일 순서면 앞 행의 함수 결과 값을 따르는데, CUME_DIST의 경우는 동일 순서면 뒤 행의 함수 결과값을 기준으로 합니다.

 

 

* NTILE 함수

   

 - NTILE 함수를 이용해 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있습니다.

 

 

 EX)

 

SELECT DEPTNO
     , ENAME
     , SAL
     , NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;

DEPTNO	ENAME	SAL	QUAR_TILE
10	KING	5000	1
20	FORD	3000	1
20	SCOTT	3000	1
20	JONES	2975	1
30	BLAKE	2850	2
10	CLARK	2450	2
30	ALLEN	1600	2
30	TURNER	1500	2
10	MILLER	1300	3
30	WARD	1250	3
30	MARTIN	1250	3
20	ADAMS	1100	4
30	JAMES	950	4
20	SMITH	800	4

 

NTILE(4)의 의미는 14명의 팀원을 4ㄱ개 조로 나눈다는 의미입니다.

전체 14명을 4개의 집합으로 나누면 몫이 3명, 나머지가 2명이 됩니다. 나머지 두 명은 앞의 조부터 할당합니다.

즉, 4명 + 4명 + 3명 + 3명으로 조를 나누게 됩니다.