[Oracle] ROLLUP 고급 사용법 GROUPING 함수 :: 마이자몽

GROUP BY

SELECT
    DEPTNO
    ,JOB
    ,SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB;

ORACLE SCOTT 계정의 EMP 테이블을 사용해서  실습을 진행했습니다.

일반적으로 사용하는 GROUP BY 문입니다. SUM 함수를 이용해서 각 부서별, 직업별 급여의 합을 구했습니다.

 

 

ROLLUP

SELECT
    DEPTNO
    ,JOB
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

위의 GROUP BY절에 ROLLUP을 추가했습니다. 결과를  부서별, 직업별 뿐만아니라 전체 급여의 합과 부서별 급여의 합을 함께 출력합니다. 그런데 여기서 의문이 드는게 있습니다. 전체 급여로도 합을 계산해주고 부서별로도 합을 구해주는건 좋은데... 전체 급여의 합만 보여주던가, 부서별로 합만 보여주던가 둘 중 하나만 추가하고 싶은데... 그게 가능할까요?

 

전체 급여 합만 출력

SELECT
    DEPTNO
    ,JOB
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP((DEPTNO, JOB));

가능하네요.

 

 

부서별 급여 합만 출력

SELECT
    DEPTNO
    ,JOB
    ,SUM(SAL)
FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);

부서별 급여 합만 출력하는 것도 가능하네요. 그럼 도데체 ROLLUP이 어떻게 무슨 원리로 이렇게 가능한걸까요?

 

 

ROLLUP의 원리

위 이미지가 ROLLUP의 원리를 전부 설명해줍니다. 각 번호는 해당 칼럼으로 GROUP BY를 한 결과를 준다는 뜻입니다. 첫번째 GROUP BY절 같은 경우는 총 4개의 GROUP을 만든다는 뜻 입니다. ROLLUP은 아래와 같은 원리로 실행됩니다.

1. ROLLUP의 인자로 들어온 칼럼을 오른쪽부터 하나씩 빼면서 GROUP을 만듭니다.
2. "()"의 의미는 GROUP이 없는 즉, 전체에 대한 결과를 출력한다는 뜻 입니다. EX(SUM 함수 사용하면 전체 SUM 구한다는 뜻)
3. 괄호로 묶여져 있는 컬럼은 하나로 본다는 뜻 입니다.
4. ROLLUP 이전에 일반 컬럼과 GROUP BY 한다면, 일반 컬럼은 끝까지 남습니다.

 

 

GROUP BY ROLLUP(A, B, C) 예시

SELECT
    DEPTNO
    ,JOB
    ,ENAME
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB,ENAME);

 

 

 

GROUP BY ROLLUP(A, (B, C)) 예시

SELECT
    DEPTNO
    ,JOB
    ,ENAME
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,(JOB,ENAME));

 

 

 

GROUP BY A, ROLLUP((B, C)) 예시

SELECT
    DEPTNO
    ,JOB
    ,ENAME
    ,SUM(SAL)
FROM EMP
GROUP BY DEPTNO,ROLLUP((JOB,ENAME));

 

 

 

ROLLUP 특이 예제

여태까지는 기본적으로 ROLLUP이 어떤 원리로 실행되는지 알아봤습니다. 그럼 위에 예제 처럼 결과를 출력할 수 있을까요? 일단, 결과를 보니, 한 칼럼에서 컬럼 이외의 값이 나오기도하고, 급여 컬럼에서는 평균값과 합계값이 같이 나오는 것을 볼 수 있습니다. 어떻게 결과를 이렇게 출력할 수 있을까요? 우선 GROUPING, GROUPING_ID 그리고 숫자 GROUP 추가에 대한 내용을 알아야합니다.

 

 

GROUPING

SELECT
    DEPTNO
    ,GROUPING(DEPTNO) AS DG
    ,JOB
    ,GROUPING(JOB) AS JG
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

GROUPING 함수는 ROLLUP이랑 같이 사용합니다. 해당 칼럼이 ROLLUP 되었을 때 그룹에서 빠져있다면 1을 반환합니다. 즉, GROUP BY ROLLUP 결과로 NULL이 나왔을 때 1을 반환합나다.

 

 

GROUPING_ID

SELECT
    DEPTNO
    ,JOB
    ,GROUPING(DEPTNO) AS DG
    ,GROUPING(JOB) AS JG
    ,GROUPING_ID(DEPTNO, JOB) GI
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

GROUPING_ID 함수는 인자로 들어온 각각 칼럼의 GROUPING 함수 값을 2진수로 합쳐 해당 2진수 값을 10진수로 변환한 값을 반환해줍니다.

 

 

숫자 GROUP

ELECT
    DEPTNO
    ,JOB
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(1,DEPTNO, JOB);

숫자가 들어간 ROLLUP은 뭘까요? 결과를 보니 마지막 2개의 줄이 같은 값이 나왔습니다. 헷갈릴 수 있지만 ROLLUP의 원리를 생각해보면 별 다를것 없습니다. JOB과 DEPTNO가 ROLLUP에서 빠지고 1만 남았을때 SUM(SAL)을 구하려고 합니다. 상수에 대한 GROUP은 없습니다. 즉, 전체 행에 대한 결과를 출력하라는 의미와 같습니다.

 

 

GROUPING, GROUPING_ID, 숫자 GROUP 종합

SELECT
    DEPTNO
    ,JOB
    ,GROUPING(1) AS "1"
    ,GROUPING(DEPTNO) AS "D"
    ,GROUPING(2) AS "2"
    ,GROUPING(JOB) AS "J"
    ,GROUPING_ID(1, DEPTNO, 2, JOB) AS ID
    ,SUM(SAL)
FROM EMP
GROUP BY ROLLUP(1,DEPTNO, 2, JOB);

GROUPING, GROUPING_ID, 숫자 GROUP 모두 같이 사용했습니다. 한 테이블에 놓고 보니까 이해가 가시나요? 숫자가 ROLLUP에 온다고 해도 ROLLUP의 원리를 안다면 큰 차이가 없습니다. 그냥 남아있는 칼럼들로 GROUP BY한 결과를 출력해주고 원리대로 진행합니다.

 

 

최종 풀이

SELECT
    DECODE(
        GROUPING_ID(1, DEPTNO, 2, JOB)
        ,7, '전체'
        ,15, '전체'
        ,DEPTNO
    ) AS DEPTNO
    ,DECODE(
        GROUPING_ID(1, DEPTNO, 2, JOB)
        ,1, '합계'
        ,3, '평균'
        ,7, '합계'
        ,15, '평균'
        ,JOB
    ) AS JOB
    ,DECODE(
        GROUPING_ID(1, DEPTNO, 2, JOB)
        ,3, ROUND(AVG(SAL))
        ,15, ROUND(AVG(SAL))
        ,SUM( SAL)
    ) AS SAL
FROM EMP
GROUP BY ROLLUP(1,DEPTNO, 2, JOB);