오라클 분석함수

오라클 분석함수

RANK() OVER(), DENSE_RANK() OVER(), ROW_NUMBER() OVER()

WITH TMP AS (

    SELECT 'AAA' AS DEPTNO, '111' AS EMPNO, 300 AS SAL FROM DUAL UNION ALL

    SELECT 'AAA' AS DEPTNO, '222' AS EMPNO, 200 AS SAL FROM DUAL UNION ALL

    SELECT 'BBB' AS DEPTNO, '333' AS EMPNO, 100 AS SAL FROM DUAL UNION ALL

    SELECT 'BBB' AS DEPTNO, '444' AS EMPNO, 150 AS SAL FROM DUAL UNION ALL

    SELECT 'CCC' AS DEPTNO, '555' AS EMPNO, 250 AS SAL FROM DUAL UNION ALL

    SELECT 'CCC' AS DEPTNO, '555' AS EMPNO, 270 AS SAL FROM DUAL UNION ALL

    SELECT 'CCC' AS DEPTNO, '666' AS EMPNO, 350 AS SAL FROM DUAL

)

SELECT

           A.DEPTNO

         , A.EMPNO

         , RANK()           OVER(ORDER BY DEPTNO)                                AS R_ORD1

         , RANK()           OVER(PARTITION BY DEPTNO ORDER BY EMPNO)  AS R_ORD2

         , DENSE_RANK() OVER(ORDER BY DEPTNO)                                AS D_ORD1

         , DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY EMPNO)  AS D_ORD2

         , ROW_NUMBER() OVER(ORDER BY DEPTNO)                                AS RN_ORD1

         , ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY EMPNO)  AS RN_ORD2

         , A.SAL

         , SUM(A.SAL)       OVER(ORDER BY DEPTNO)                                AS S_ORD1

         , SUM(A.SAL)       OVER(PARTITION BY DEPTNO ORDER BY EMPNO)  AS S_ORD2

         , COUNT(A.EMPNO) OVER(ORDER BY DEPTNO)                                AS C_ORD1

         , COUNT(A.EMPNO) OVER(PARTITION BY DEPTNO ORDER BY EMPNO)  AS C_ORD2

FROM TMP A

=> 결과

DEPTNO EMPNO R_ORD1 R_ORD2 D_ORD1 D_ORD2 RN_ORD1 RN_ORD2 SAL S_ORD1 S_ORD2 C_ORD1 C_ORD2

--------------------------------------------------------------------------------------------------------------------------------

AAA       111           1         1         1           1           1           1         300    500     300        2          1

AAA       222           1         2         1           2           2           2         200    500     500        2          2

BBB       333            3         1         2           1           3           1         100    750     100        4          1

BBB       444            3         2         2           2           4           2         150    750     250        4          2

CCC       555            5         1         3           1           5           1         250  1620     520        7          2

CCC       555            5         1         3           1           6           2         270  1620     520        7          2

CCC       666            5         3         3           2           7           3         350  1620     870        7          3

RANK(), DENSE_RANK(), ROW_NUMBER()는 순위를 리턴하는 순위함수로

OVER()와 같이 사용하여 PARTITION BY로 그룹별 순위를 매길수 있고

ORDER BY로 순위의 기준컬럼을 설정할 수 있다.

RANK()

값이 같으면 같은 갯수만큼 건너뛴다.

1,2,2,3 2등이 2명이면

-> 1,2,2,4

DENSE_RANK()

RANK()와 다르게 값이 같으면 동일한 순위가 된다.

1,2,2,3 2등이 2명이어도

-> 1,2,2,3

ROW_NUMBER()

순차적으로 순위가 정해진다.

값이 동일해도 무조건 순서대로 순위가 정해진다.

SUM(), COUNT(), MIN(), MAX(), AVG()는 집계함수로 합,건수,최소,최대,평균값을 구할수 있다.

SUM()

누적합계를 구한다.

COUNT()

누적건수를 구한다.