ROLLUP 과 CUBE

게시일: Oct 24, 2011 1:9:52 AM

- ROLLUP : 그룹핑된 결과에 그룹별 합계 정보를 추가한다

- CUBE : 그룹핑된 컬럼의 모든 가능한 조합에 대한 합계 정보를 추가한다.

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sum(sales_volume) sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 100 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '1' product_id, 100 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '3' product_id, 300 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '1' product_id, 100 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '3' product_id, 200 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 500 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 100 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 200 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '1' product_id, 700 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '2' product_id, 500 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '3' product_id, 700 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '2' product_id, 600 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '3' product_id, 100 sales_volume from dual

)

group by rollup( to_char(sales_dt, 'yyyy-mm'), product_id )

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 1 400

2011-10 2 900

2011-10 3 600

2011-10 1900 --> 10월 소계

2011-11 1 1400

2011-11 2 1200

2011-11 3 1000

2011-11 3600 --> 11월 소계

5500 --> 합계

------------------------------------

select to_char(sales_dt, 'yyyy-mm') sales_month

, product_id product_id

, sum(sales_volume) sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 100 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '1' product_id, 100 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '3' product_id, 300 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '1' product_id, 100 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '3' product_id, 200 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 500 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 100 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 200 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '1' product_id, 700 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '2' product_id, 500 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '3' product_id, 700 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '2' product_id, 600 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '3' product_id, 100 sales_volume from dual

)

group by cube( to_char(sales_dt, 'yyyy-mm'), product_id )

order by sales_month, product_id

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 1 400

2011-10 2 900

2011-10 3 600

2011-10 1900 --> 10월 소계

2011-11 1 1400

2011-11 2 1200

2011-11 3 1000

2011-11 3600 --> 11월 소계

1 1800 --> 1번 물품 판매 소계

2 2100 --> 2번 물품 판매 소계

3 1600 --> 3번 물품 판매 소계

5500 --> 합계

------------------------------------

☞ grouping(컬럼) = 0 --> 원래(진짜) 데이터

grouping(컬럼) = 1 --> 가공(가짜, 소계) 데이터

select decode(grouping(to_char(sales_dt, 'yyyy-mm')), 1, '합계', to_char(sales_dt, 'yyyy-mm')) sales_month

, decode(grouping(to_char(sales_dt, 'yyyy-mm')), 1, '합계'

, decode(grouping(product_id), 1, '소계', product_id)) product_id

, sum(sales_volume) sales_volume

from (

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 100 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '1' product_id, 100 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-02', 'yyyy-mm-dd') sales_dt, '3' product_id, 300 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '1' product_id, 100 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '2' product_id, 300 sales_volume from dual union all

select to_date('2011-10-03', 'yyyy-mm-dd') sales_dt, '3' product_id, 200 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '1' product_id, 500 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '2' product_id, 100 sales_volume from dual union all

select to_date('2011-11-01', 'yyyy-mm-dd') sales_dt, '3' product_id, 200 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '1' product_id, 700 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '2' product_id, 500 sales_volume from dual union all

select to_date('2011-11-02', 'yyyy-mm-dd') sales_dt, '3' product_id, 700 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '1' product_id, 200 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '2' product_id, 600 sales_volume from dual union all

select to_date('2011-11-03', 'yyyy-mm-dd') sales_dt, '3' product_id, 100 sales_volume from dual

)

group by rollup( to_char(sales_dt, 'yyyy-mm'), product_id )

having (

( grouping(to_char(sales_dt, 'yyyy-mm')) = 0 and

grouping(product_id ) = 0 ) -- 원래 데이터 (주석처리하고 결과를 확인해보자)

or

( grouping(to_char(sales_dt, 'yyyy-mm')) = 0 and

grouping(product_id ) = 1 ) -- 소계 데이터 (주석처리하고 결과를 확인해보자)

or

( grouping(to_char(sales_dt, 'yyyy-mm')) = 1 and

grouping(product_id ) = 1 ) -- 합계 데이터 (주석처리하고 결과를 확인해보자)

)

;

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

2011-10 1 400

2011-10 2 900

2011-10 3 600

2011-10 소계 1900

2011-11 1 1400

2011-11 2 1200

2011-11 3 1000

2011-11 소계 3600

합계 합계 5500

------------------------------------

-> grouping으로 표현한다면....

------------------------------------

SALES_MONTH PRODUCT_ID SALES_VOLUME

------------------------------------

0 0 400

0 0 900

0 0 600

0 1 1900

0 0 1400

0 0 1200

0 0 1000

0 1 3600

1 1 5500

------------------------------------

********************************************************************

☞ 살아있는 코드를 확인하시려면 아래의 링크를 확인하세요~~

grouping을 사용한 rollup 활용 예

********************************************************************