[DBMS/Oracle] ROLLUP과 CUBE함수

[DBMS/Oracle] ROLLUP과 CUBE함수

ROLLUP과 CUBE함수

- GROUP BY 절 안에서 사용되어 다양한 집계를 반환함

 

1. ROLLUP(col1,col2,...)

  - ROLLUP에 사용된 컬럼명들로 레벨을 구성하고 각 레벨별 합계(중간 합계)를 반환
  - 레벨은 ROLLUP절에 사용된 모든 컬럼이 적용된 경우가 최하위레벨이고 이후에 오른쪽 컬럼부터 1개씩 제거한 값으로      레벨이 구성됨, 마지막에는 모든 컬럼이 제거된(전체 테이블) 전체 합계가 반환됨
  - 사용된 컬럼의 수가 n개 이면 n+1 가지의 집계가 반환됨
  - 일부의 컬럼만 ROLLUP절안에 기술되고 나머지 그룹기준 컬럼이 ROLLUP밖에 기술된 경우를 부분 ROLLUP이라함

사용예) 장바구니 테이블에서 2020년 월별,회원별,제품별,매출수량집계를 구하시오.
(GROUP BY 절만 사용)
    SELECT SUBSTR(CART_NO,5,2)||'월' AS 월, 
           CART_MEMBER AS 회원번호,
           CART_PROD AS 제품코드,
           SUM(CART_QTY) AS 매출수량집계
      FROM CART
     WHERE SUBSTR(CART_NO,1,4)='2020'
     GROUP BY SUBSTR(CART_NO,5,2),CART_MEMBER,CART_PROD
     ORDER BY 1;

(ROLLUP 절만 사용)
    SELECT SUBSTR(CART_NO,5,2)||'월' AS 월, 
           CART_MEMBER AS 회원번호,
           CART_PROD AS 제품코드,
           SUM(CART_QTY) AS 매출수량집계
      FROM CART
     WHERE SUBSTR(CART_NO,1,4)='2020'
     GROUP BY ROLLUP(SUBSTR(CART_NO,5,2),CART_MEMBER,CART_PROD)
     ORDER BY 1;

(부분 ROLLUP)     
    SELECT SUBSTR(CART_NO,5,2)||'월' AS 월, 
           CART_MEMBER AS 회원번호,
           CART_PROD AS 제품코드,
           SUM(CART_QTY) AS 매출수량집계
      FROM CART
     WHERE SUBSTR(CART_NO,1,4)='2020'
     GROUP BY SUBSTR(CART_NO,5,2),ROLLUP(CART_MEMBER,CART_PROD)
     ORDER BY 1;

:

 

2. CUBE(col1,col2,...)

  - 사용된 컬럼명을 조합사용하여 나올 수 있는 모든 경우의 수만큼의 집계를 반환
  - 레벨을 사용하지 않음
  - 사용된 컬럼이 n개일 때 Q반환되는 집계의 종류는 2^n가지임

사용예) 장바구니 테이블에서 2020년 월별,회원별,제품별,매출수량집계를 구하시오.
(CUBE 함수 사용)
    SELECT SUBSTR(CART_NO,5,2)||'월' AS 월, 
           CART_MEMBER AS 회원번호,
           CART_PROD AS 제품코드,
           SUM(CART_QTY) AS 매출수량집계
      FROM CART
     WHERE SUBSTR(CART_NO,1,4)='2020'
     GROUP BY CUBE(SUBSTR(CART_NO,5,2),CART_MEMBER,CART_PROD)
     ORDER BY 1;

(부분 CUBE)     
    SELECT SUBSTR(CART_NO,5,2)||'월' AS 월, 
           CART_MEMBER AS 회원번호,
           CART_PROD AS 제품코드,
           SUM(CART_QTY) AS 매출수량집계
      FROM CART
     WHERE SUBSTR(CART_NO,1,4)='2020'
     GROUP BY CUBE(CART_MEMBER,CART_PROD),SUBSTR(CART_NO,5,2)
     ORDER BY 1;