오라클(SQL)집계함수(ROLLUP, CUBE)

집계함수(ROLLUP, CUBE)

1.ROLLUP
    -GROUP BY절과 같이 사용되어 레벨별 합계와 전체합계를 반환
(사용형식)
GRUOP   BY ROLLUP(컬럼명1, 컬럼명2,.....)
        . 집계의 종류는 사용된 컬럼명의 수+1가지임
        . 컬럼명이 기술된 순서 오른쪽에서 왼쪽 순의 레벨별로 집계
사용예)대출잔액테이블(KOR_LOAN_STATUS)에서 2011~2013년 까지
    년도별, 지역별 대출잔액을 구하시오
    ALIAS 년도, 지역, 대출잔액합계

(ROLLUP 미사용)

SELECT  SUBSTR(PERIOD,1,4) AS 년도, 
        REGION AS 지역, 
        SUM(LOAN_JAN_AMT) AS 대출잔액합계
FROM    KOR_LOAN_STATUS
WHERE   SUBSTR(PERIOD,1,4) BETWEEN '2011' AND '2013'
--GROUP   BY SUBSTR(PERIOD,1,4),REGION
GROUP   BY ROLLUP(SUBSTR(PERIOD,1,4),REGION)
ORDER   BY 1;

사용예)대출잔액테이블(KOR_LOAN_STATUS)에서 2013년 까지
    년도별, 구분별, 월별 대출잔액을 구하시오
    ALIAS 지역,대출구분, 월, 대출잔액합계

(ROLLUP 미적용)

SELECT  REGION AS 지역,
        GUBUN AS 대축구분,
        SUBSTR(PERIOD,5) AS 월,
        SUM(LOAN_JAN_AMT) AS 대출잔액합계
FROM    KOR_LOAN_STATUS
WHERE   SUBSTR(PERIOD,1,4)='2013'
GROUP   BY REGION, GUBUN, SUBSTR(PERIOD,5)
ORDER   BY 1;

(ROLLUP 적용)

SELECT  REGION AS 지역,
        GUBUN AS 대축구분,
        SUBSTR(PERIOD,5) AS 월,
        SUM(LOAN_JAN_AMT) AS 대출잔액합계
FROM    KOR_LOAN_STATUS
WHERE   SUBSTR(PERIOD,1,4)='2013'
GROUP   BY ROLLUP(REGION, GUBUN, SUBSTR(PERIOD,5))
ORDER   BY 1;

**부분 ROLLUP

-GROUP BY 절에서 컬럼명이 ROLLUP 밖에서 기술된 경우

SELECT  REGION AS 지역,
        GUBUN AS 대축구분,
        SUBSTR(PERIOD,5) AS 월,
        SUM(LOAN_JAN_AMT) AS 대출잔액합계
FROM    KOR_LOAN_STATUS
WHERE   SUBSTR(PERIOD,1,4)='2013'
GROUP   BY ROLLUP(GUBUN, SUBSTR(PERIOD,5)),REGION
ORDER   BY 1; 

2.CUBE
    -ROLLUP 과 사용형식은 동일
    -CUBE 다음 '()'안에 기술된 컬럼명을 조합한 모든 경우의 수만큼의
     종류의 합계를 반환, N개의 컬럼이 사용된 경우 2의 N승 가지의 
     합계 반환
     
사용예)대출잔액테이블(KOR_LOAN_STATUS)에서 2011~2013년 까지
    년도별, 지역별 대출잔액을 구하시오
    ALIAS 년도, 지역, 대출잔액합계
(ROLLUP 미사용)

SELECT  SUBSTR(PERIOD,1,4) AS 년도,  
        REGION AS 지역,  
        SUM(LOAN_JAN_AMT) AS 대출잔액합계 
FROM    KOR_LOAN_STATUS 
WHERE   SUBSTR(PERIOD,1,4) BETWEEN '2011' AND '2013' 
--GROUP   BY SUBSTR(PERIOD,1,4),REGION 
GROUP   BY CUBE(SUBSTR(PERIOD,1,4),REGION) 
ORDER   BY 1; 

사용예)대출잔액테이블(KOR_LOAN_STATUS)에서 2013년 까지
    년도별, 구분별, 월별 대출잔액을 구하시오
    ALIAS 지역,대출구분, 월, 대출잔액합계
(ROLLUP 미적용)

SELECT  REGION AS 지역, 
        GUBUN AS 대축구분, 
        SUBSTR(PERIOD,5) AS 월, 
        SUM(LOAN_JAN_AMT) AS 대출잔액합계 
FROM    KOR_LOAN_STATUS 
WHERE   SUBSTR(PERIOD,1,4)='2013' 
GROUP   BY REGION, GUBUN, SUBSTR(PERIOD,5) 
ORDER   BY 1; 

(CUBE 적용)

SELECT  REGION AS 지역, 
        GUBUN AS 대축구분, 
        SUBSTR(PERIOD,5) AS 월, 
        SUM(LOAN_JAN_AMT) AS 대출잔액합계 
FROM    KOR_LOAN_STATUS 
WHERE   SUBSTR(PERIOD,1,4)='2013' 
GROUP   BY CUBE(REGION, GUBUN, SUBSTR(PERIOD,5)) 
ORDER   BY 1;