오라클 cube 예제

오라클 그룹함수 cube는 rollup과 유사하나 약간 더 상세한 결과를 제공한다. rollup과 같이 각 항목별 전체 합, 부분합을 쿼리 결과로 제공한다. 샘플 데이터는 아래와 같다

oracle cube sample data
WITH BASE AS (
    SELECT '마포구' GU, 1 GU_ODR, '연남동' DONG, 1 DONG_ODR, 12 QTY FROM DUAL UNION ALL
    SELECT '마포구' GU, 1 GU_ODR, '공덕동' DONG, 4 DONG_ODR, 31 QTY FROM DUAL UNION ALL
    SELECT '마포구' GU, 1 GU_ODR, '상암동' DONG, 3 DONG_ODR, 78 QTY FROM DUAL UNION ALL
    SELECT '마포구' GU, 1 GU_ODR, '동교동' DONG, 2 DONG_ODR, 34 QTY FROM DUAL UNION ALL
    SELECT '강남구' GU, 2 GU_ODR, '도곡동' DONG, 3 DONG_ODR, 25 QTY FROM DUAL UNION ALL
    SELECT '강남구' GU, 2 GU_ODR, '논현동' DONG, 2 DONG_ODR, 33 QTY FROM DUAL UNION ALL
    SELECT '강남구' GU, 2 GU_ODR, '삼성동' DONG, 1 DONG_ODR, 39 QTY FROM DUAL 
)
SELECT CASE WHEN B.GU IS NULL THEN 'Total'
       ELSE B.GU END AS GU
     , B.GU_ODR
     , CASE WHEN B.GU IS NOT NULL AND B.DONG IS NULL THEN 'Total'
       ELSE B.DONG END AS DONG
     , B.DONG_ODR
     , SUM(QTY) AS QTY
  FROM BASE         B
 WHERE 1=1
 GROUP BY CUBE (
    (B.GU, B.GU_ODR), (B.DONG, B.DONG_ODR)
 )
 ORDER BY
       B.GU_ODR NULLS FIRST, B.GU NULLS FIRST
     , B.DONG_ODR NULLS FIRST, B.DONG NULLS FIRST
oracle cube함수 결과