행위

ROLLUP

DB CAFE

thumb_up 추천메뉴 바로가기

1 ROLLUP 합계, 소계 구하기 (GROUP BY)[편집]

  • 오라클 GROUP BY 쿼리에서 ROLLUP 함수를 사용하여 손쉽게 합계와 소계를 구할 수 있다.

1.1 GROUP BY 컬럼이 하나인 경우[편집]

GROUP BY 절에 ROLLUP 함수를 추가한 후 그룹핑 컴럼을 인자로 넣는다.
-- 가상 테이블
WITH TEST_TABLE AS (
   SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
SELECT JOB
     , SUM(SAL)
  FROM TEST_TABLE
 GROUP BY ROLLUP(JOB)



1.2 GROUP BY 컬럼이 두 개 이상인 경우[편집]

합계 및 소계 까지 계산되어 표시된다.
-- 가상 테이블
WITH TEST_TABLE AS (
   SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
SELECT JOB
     , DEPT
     , SUM(SAL)
  FROM TEST_TABLE
 GROUP BY ROLLUP(JOB, DEPT)

1.3 GROUPING , GROUPING_ID 함수[편집]

  1. GROUPING, GROUPING_ID 함수는 소계와 합계를 집계할 때 사용하는 ROLLUP, CUBE, GROUPING SETS 함수와 함께 사용
  2. 소계와 합계로 집계되어 출력된 행을 구분할 때 사용

1.3.1 GROUPING 함수[편집]

  1. GROUPING 함수는 소계, 합계로 집계된 행의 컬럼 NULL을 구분할 수있다.
  2. NULL인 경우 1을 반환하고 아닌경우 0을 반환
SELECT job
     , deptno
     , SUM(sal) 
     , GROUPING(job)
     , GROUPING(deptno)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)
SELECT job
     , mgr
     , SUM(sal) 
     , GROUPING(mgr)
  FROM emp
 WHERE job = 'PRESIDENT'
 GROUP BY ROLLUP(job, mgr)
  • NULL은 0을 반환
  • 소계 산출로 생성된 행의 NULL만 1을 반환

1.3.2 GROUPING_ID 함수[편집]

  1. GROUPING_ID 함수는 여러 컬럼을 매개변수로 사용할 수 있다.
  2. 매개변수의 컬럼 순서에 맞게 해당 컬럼이 NULL인 경우 1을 반한하고 한 행을 2진수라고 생각하면 된다.
    예시) 01 → 0001 (2진수) → 1 (10진수)
    예시) 11 → 0011 (2진수) → 3 (10진수)
  3. 2진수를 10진수로 변환하면 GROUPING_ID 함수 결과가 된다.
SELECT job
     , deptno
     , SUM(sal) 
     , GROUPING_ID(job, deptno)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)
SELECT job
     , deptno
     , mgr
     , SUM(sal) 
     , GROUPING_ID(job, deptno, mgr)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno, mgr)
  • 예시) 001 → 0001 (2진수) → 1 (10진수)
  • 예시) 011 → 0011 (2진수) → 3 (10진수)
  • 예시) 111 → 0111 (2진수) → 7 (10진수)
  • GROUPING, GROUPING_ID 함수는 SELECT 절과 HAVING 절에서 사용


  • 합계 행의 JOB 컬럼에 NULL 대신 합계를 표시
SELECT DECODE(GROUPING(job), 1, '합계', job)
     , SUM(sal) 
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job)
  • 소계, 합계 행의 JOB, DEPTNO 컬럼에 NULL 대신 소계, 합계를 표시
SELECT DECODE(GROUPING_ID(job, deptno), 3, '합계', job)    AS job
     , DECODE(GROUPING_ID(job, deptno), 1, '소계', deptno) AS deptno
     , SUM(sal) 
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)


  • 소계 행을 제거하고, 데이터 행(0)과 합계 행(3)만 표시한다.
SELECT job
     , deptno
     , SUM(sal) 
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)
HAVING GROUPING_ID(job, deptno) IN (0, 3)


1.4 GROUP BY 컬럼이 두 개 이상인 경우 합계만 표시하고 싶을 때[편집]

HAVING 절에 GROUPING_ID 함수를 이용하여 소계 값을 제외할 수 있다.
-- 가상 테이블
WITH TEST_TABLE AS (
   SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
 
SELECT JOB
           , DEPT
           , SUM(SAL)
   FROM TEST_TABLE
 GROUP BY ROLLUP(JOB, DEPT) HAVING GROUPING_ID(JOB, DEPT) IN (0, 3)

1.5 ROLLUP 컬럼에 합계 표시하기[편집]

DECODE 함수를 이용하여 합계 컬럼 값을 NULL 대신 합계로 표시할 수 있다.
-- 가상 테이블
WITH TEST_TABLE AS (
   SELECT 'SMITH' NM, 'CLERK' JOB, 800 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'ALLEN' NM, 'SALESMAN' JOB, 1600 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'WARD' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'JONES' NM, 'MANAGER' JOB, 2975 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MARTIN' NM, 'SALESMAN' JOB, 1250 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'BLAKE' NM, 'MANAGER' JOB, 2850 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'CLARK' NM, 'MANAGER' JOB, 2450 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'SCOTT' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'KING' NM, 'PRESIDENT' JOB, 5000 SAL, 'ACCOUNTING' DEPT FROM DUAL UNION ALL
   SELECT 'TURNER' NM, 'SALESMAN' JOB, 1500 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'ADAMS' NM, 'CLERK' JOB, 1100 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'JAMES' NM, 'CLERK' JOB, 950 SAL, 'SALES' DEPT FROM DUAL UNION ALL
   SELECT 'FORD' NM, 'ANALYST' JOB, 3000 SAL, 'RESEARCH' DEPT FROM DUAL UNION ALL
   SELECT 'MILLER' NM, 'CLERK' JOB, 1300 SAL, 'ACCOUNTING' DEPT FROM DUAL
)
 
SELECT DECODE(JOB, NULL, '합계', JOB) JOB
     , DEPT
     , SUM(SAL)
  FROM TEST_TABLE
 GROUP BY ROLLUP(JOB, DEPT) HAVING GROUPING_ID(JOB, DEPT) IN (0, 3)


1.6 ROLLUP, CUBE[편집]

  • ROLLUP(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE)
SELECT TO_CHAR(ORDER_DATE, 'YYYYMM') ORDER_MM
     , EMPLOYEE_ID
     , ORDER_MODE
     , SUM(ORDER_TOTAL) ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDDHH24')
   AND A.ORDER_DATE < TO_DATE('20130101', 'YYYYMMDDHH24') 
 GROUP BY ROLLUP(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE);


  • ROLLUP (TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE)
    HAVING ((GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) = 0 AND
    GROUPING(EMPLOYEE_ID) = 0 AND
    GROUPING(ORDER_MODE) = 0) OR
    (GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) = 1 AND
    GROUPING(EMPLOYEE_ID) = 1 AND
    GROUPING(ORDER_MODE) = 1));
SELECT TO_CHAR(ORDER_DATE, 'YYYYMM') ORDER_MM
     , EMPLOYEE_ID
     , ORDER_MODE
     , GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) GROUPING_ORDER_MM
     , GROUPING(EMPLOYEE_ID) EMPLOYEE_ID
     , GROUPING(ORDER_MODE) ORDER_MODE
     , SUM(ORDER_TOTAL) ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDDHH24')
   AND A.ORDER_DATE < TO_DATE('20130101', 'YYYYMMDDHH24') 
 GROUP BY ROLLUP(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE)
 HAVING ((GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) = 0 AND
          GROUPING(EMPLOYEE_ID) = 0 AND 
          GROUPING(ORDER_MODE) = 0) OR
         (GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) = 1 AND
          GROUPING(EMPLOYEE_ID) = 1 AND 
          GROUPING(ORDER_MODE) = 1));
  • GROUP BY ROLLUP(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE)
    HAVING GROUPING_ID(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE) IN (0, 7)
SELECT TO_CHAR(ORDER_DATE, 'YYYYMM') ORDER_MM
     , EMPLOYEE_ID
     , ORDER_MODE
     , GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) GROUPING_ORDER_MM
     , GROUPING(EMPLOYEE_ID) EMPLOYEE_ID
     , GROUPING(ORDER_MODE) ORDER_MODE
     , SUM(ORDER_TOTAL) ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDDHH24')
   AND A.ORDER_DATE < TO_DATE('20130101', 'YYYYMMDDHH24') 
 GROUP BY ROLLUP(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE)
 HAVING GROUPING_ID(TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE) IN (0, 7)

1.7 GROUPING SETS[편집]

  • ((TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE),())
SELECT TO_CHAR(ORDER_DATE, 'YYYYMM') ORDER_MM
     , EMPLOYEE_ID
     , ORDER_MODE
     , GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) GROUPING_ORDER_MM
     , GROUPING(EMPLOYEE_ID) EMPLOYEE_ID
     , GROUPING(ORDER_MODE) ORDER_MODE
     , SUM(ORDER_TOTAL) ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDDHH24')
   AND A.ORDER_DATE < TO_DATE('20130101', 'YYYYMMDDHH24') 
 GROUP BY GROUPING SETS ((TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE), 
                         ());


  • ((TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE), TO_CHAR(ORDER_DATE, 'YYYYMM'),EMPLOYEE_ID,ORDER_MODE)
SELECT TO_CHAR(ORDER_DATE, 'YYYYMM') ORDER_MM
     , EMPLOYEE_ID
     , ORDER_MODE
     , GROUPING(TO_CHAR(ORDER_DATE, 'YYYYMM')) GROUPING_ORDER_MM
     , GROUPING(EMPLOYEE_ID) EMPLOYEE_ID
     , GROUPING(ORDER_MODE) ORDER_MODE
     , SUM(ORDER_TOTAL) ORDER_TOTAL
  FROM ORDERS A
 WHERE A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDDHH24')
   AND A.ORDER_DATE < TO_DATE('20130101', 'YYYYMMDDHH24') 
 GROUP BY GROUPING SETS ((TO_CHAR(ORDER_DATE, 'YYYYMM'), EMPLOYEE_ID, ORDER_MODE), 
                         TO_CHAR(ORDER_DATE, 'YYYYMM'),
                         EMPLOYEE_ID,
                         ORDER_MODE);
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=ROLLUP&oldid=9028"