Oracle Advanced SQL 강좌
Rollup 결과 행 구분 3 4 11,879

by 마농 GROUPING GROUPING_ID GROUP_ID ROLLUP [2014.01.15]


GROUPING과 GROUPING_ID

GROUPING (컬럼)
  • - GROUPING 함수는 ROLLUP, CUBE에 모두 사용할 수 있다.
  • - GROUPING 함수는 해당 컬럼의 Row가 GROUP BY에 의해서 산출된 Row인 경우에는 0을 반환하고, ROLLUP이나 CUBE에 의해서 산출된 Row인 경우에는 1을 반환하게 된다.
  • - 따라서 해당 Row가 결과집합에 의해 산출된 Data 인지, ROLLUP이나 CUBE에 의해서 산출된 Data 인지를 알 수 있도록 지원하는 함수이다.

GROUPING_ID(컬럼a, 컬럼b[, …])
  • - GROUPING(컬럼a)||GROUPING(컬럼b)의 값을 2진수에서 10진수로 변환한 값이다.

아래는 GROUPING과 GROUPING_ID의 예제이다.

SELECT deptno
     , empno
     , SUM(sal) s_sal
     , GROUPING(deptno) grp_dept
     , GROUPING(empno)  grp_emp
     , GROUPING_ID(deptno, empno) gid
  FROM emp
 GROUP BY ROLLUP(deptno, empno)
;


-- GRP_DEPT : deptno로 GROUP BY 되면 0, deptno의 값이 없으면 1
-- GRP_EMP : empno로 GROUP BY 되면 0, empno의 값이 없으면 1
-- GID : GROUPING(deptno) || GROUPING(empno)의 값을 2진수에서 10진수로 변환한 값
DEPTNO      EMPNO      S_SAL   GRP_DEPT    GRP_EMP        GID
------ ---------- ---------- ---------- ---------- ----------
    10       7782       2450          0          0          0
    10       7839       5000          0          0          0
    10       7934       1300          0          0          0
    10                  8750          0          1          1
    20       7369        800          0          0          0
    20       7566       2975          0          0          0
    20       7788       3000          0          0          0
    20       7876       1100          0          0          0
    20       7902       3000          0          0          0
    20                 10875          0          1          1
    30       7900        950          0          0          0
    30       7499       1600          0          0          0
    30       7521       1250          0          0          0
    30       7654       1250          0          0          0
    30       7698       2850          0          0          0
    30       7844       1500          0          0          0
    30                  9400          0          1          1
                       29025          1          1          3

소계만 표시, 총계 제거

아래는 부서별 소계만 조회하고, 전체 총계는 조회하지 않는 예제이다. 위 GROUPING과 GROUPING_ID 예제 실행결과 [GROUPING(deptno)=0] 조건으로 소계만 조회가 가능하다.

SELECT deptno
     , DECODE(GROUPING(empno),1,'소계',empno) empno
     , SUM(sal) s_sal
  FROM emp
 GROUP BY ROLLUP(deptno, empno)
HAVING GROUPING(deptno) = 0
;

 DEPTNO EMPN      S_SAL
------- ---- ----------
     10 7782       2450
     10 7839       5000
     10 7934       1300
     10 소계       8750
     20 7369        800
     20 7566       2975
     20 7788       3000
     20 7876       1100
     20 7902       3000
     20 소계      10875
     30 7900        950
     30 7499       1600
     30 7521       1250
     30 7654       1250
     30 7698       2850
     30 7844       1500
     30 소계       9400

아래와 같이 GROUP BY 절에서 empno만 ROLLUP 연산[GROUP BY deptno, ROLLUP(empno)]을 하여도 동일한 결과를 얻을 수 있다.

SELECT deptno
     , DECODE(GROUPING(empno),1,'소계',empno) empno
     , SUM(sal) s_sal
  FROM emp
 GROUP BY deptno, ROLLUP(empno)
;


 DEPTNO EMPN      S_SAL
------- ---- ----------
     10 7782       2450
     10 7839       5000
     10 7934       1300
     10 소계       8750
     20 7369        800
     20 7566       2975
     20 7788       3000
     20 7876       1100
     20 7902       3000
     20 소계      10875
     30 7900        950
     30 7499       1600
     30 7521       1250
     30 7654       1250
     30 7698       2850
     30 7844       1500
     30 소계       9400

총계만 표시, 소계 제거

아래는 전체 총계만 조회하고, 소계는 조회하지 않는 예제이다. 위 GROUPING과 GROUPING_ID 예제 실행결과 [GROUPING_ID(deptno, empno) != 1] 조건으로 소계만 조회가 가능하다.

SELECT deptno
     , DECODE(GROUPING(deptno),1,'합계',empno) empno
     , SUM(sal) s_sal
  FROM emp
 GROUP BY ROLLUP(deptno, empno)
HAVING GROUPING_ID(deptno, empno) != 1
;

 DEPTNO EMPN      S_SAL
------- ---- ----------
     10 7782       2450
     10 7839       5000
     10 7934       1300
     20 7369        800
     20 7566       2975
     20 7788       3000
     20 7876       1100
     20 7902       3000
     30 7900        950
     30 7499       1600
     30 7521       1250
     30 7654       1250
     30 7698       2850
     30 7844       1500
        합계      29025

아래와 같이 ROLLUP 연산을 괄호로 묶어[GROUP BY ROLLUP((deptno, empno))]서 처리가 가능하다. 괄호로 묶은 항목은 하나의 항목처럼 묶여 처리 된다. 이는 코드와 명칭을 함께 그룹바이 롤업할때 주로 사용된다.

SELECT deptno
     , DECODE(GROUPING(deptno),1,'합계',empno) empno
     , SUM(sal) s_sal
  FROM emp
 GROUP BY ROLLUP((deptno, empno))
;


 DEPTNO EMPN      S_SAL
------- ---- ----------
     10 7782       2450
     10 7839       5000
     10 7934       1300
     20 7369        800
     20 7566       2975
     20 7788       3000
     20 7876       1100
     20 7902       3000
     30 7900        950
     30 7499       1600
     30 7521       1250
     30 7654       1250
     30 7698       2850
     30 7844       1500
        합계      29025

- 강좌 URL : http://www.gurubee.net/lecture/2679

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 더블엑스 [2014.03.12 17:19:55]
전 이 링크 보고 이해했음..ㅠㅠ
http://blog.naver.com/minis24/80100555203

by 김용한 [2015.04.08 00:36:31]

감사합니다 이제이해했어요 ~~


by 므흣 [2016.05.02 22:12:18]

하~~~ 이해하기 힘듭니다... ㅜㅜ

좀더 노력해야겠네요.  


by 지현명 [2016.05.27 02:28:28]

마지막에 괄호써서 한거는 어떻게 알아 냈을까?..감사~

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입