[SQL]분석함수(analytic function)_순위함수(NTILE, CUME_DIST, PERCENT_RANK, RATIO_TO_REPORT)

순위 함수

 

| NTILE 함수

NTILE 함수는 order_by_clause에 따라 행을 정렬하고, expr의 개수만큼 버킷을 생성한 후, 행에 해당하는 버킷 번호를 할당한다.

NTILE(expr) OVER([query_partition_clause] order_by_clause)

 

ex 1 )

SELECT sal
    , NTILE(1) OVER(ORDER BY sal) AS c1, NTILE(2) OVER(ORDER BY sal) AS c2
    , NTILE(3) OVER(ORDER BY sal) AS c3, NTILE(4) OVER(ORDER BY sal) AS c4
    , NTILE(5) OVER(ORDER BY sal) AS c5, NTILE(6) OVER(ORDER BY sal) AS c6
    , NTILE(7) OVER(ORDER BY sal) AS c7
 FROM emp
WHERE deptno = 30
ORDER BY 1;

• expr값이 버킷값이고, expr / 버킷값 에서 나머지가 나오면 앞쪽 행부터 1개씩 더 할당한다. c4 열은 나머지가 2(6/4)이므로 앞 쪽 2개의 버킷에 행이 더 할당되고, c5 열은 나머지가 1이므로 앞쪽 1개의 버킷에 행이 더 할당된다. c6, c7 열은 expr이 행의 개수 이상이므로 순번이 차례대로 부여된다.

ex 2 )

SELECT c1, COUNT(*) AS c2, SUM(sal) AS c3
 FROM (SELECT sal, NTILE(4) OVER(ORDER BY sal) AS c1 FROM emp WHERE deptno = 30)
GROUP BY c1
ORDER BY c1;

| CUME_DIST 함수

CUME_DIST 함수는 누적 분포 값을 반환한다. 누적분포 값은 0 < y <= 1의 범위를 가진다.

CUME_DIST() OVER([query_partition_clause] order_by_clause)

 

ex )

SELECT sal
    , COUNT(*) OVER(ORDER BY sal) c1
    , COUNT(*) OVER() AS c2
    , COUNT(*) OVER(ORDER BY sal) / COUNT(*) OVER() AS c3
    , CUME_DIST() OVER(ORDER BY sal) AS c4
 FROM emp
WHERE deptno = 30
ORDER BY sal;

| PERCENT_RANK 함수

PERCENT_RANK 함수는 백분위 순위 값을 반환한다. 백분위 순위는 순위의 대상을 100건으로 가정했을 때의 상대 순위다. 백분위 순위 값은 0 <= y <= 1 의 범위를 가진다.

PERCENT_RANK() OVER([query_partition_clause] order_by_clause)

ex )

SELECT sal
    , RANK() OVER(ORDER BY sal) - 1 AS c1
    , COUNT(*) OVER() - 1 AS c2
    , (RANK() OVER(ORDER BY sal) - 1) / (COUNT(*) OVER() - 1) AS c3
    , PERCENT_RANK() OVER(ORDER BY sal) AS c4
 FROM emp
WHERE deptno = 30
ORDER BY 1;

| RATIO_TO_REPORT 함수

RATIO_TO_REPORT 함수는 expr의 합계에 대한 현재 expr의 비율을 반환한다. expr이 널이면 널을 반환한다.

RATIO_TO_REPORT(expr) OVER([query_partition_clause])

 

ex )

SELECT sal, SUM(sal) over() AS c1
    , sal / SUM(sal) OVER() AS c2
    , RATIO_TO_REPORT(sal) OVER() AS c3
 FROM emp
WHERE deptno = 30
ORDER BY sal;