ROLLUP 과 CUBE

우선 ROLLUP과 CUBE의 기본 내용은 이전에 정리했던 내용을 확인하자. (링크 -> ROLLUP과 CUBE)

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

grouping을 사용한 rollup 활용 예

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

- 추가된 그룹핑 함수

▷ GROUPING(expr)

expr 값이 NULL일 경우(소계,합계)에는 1을, NULL이 아닌 경우(원래 데이터)에는 0을 반환한다.

작성 예는 위의 링크를 확인하자.

▷ GROUPING_ID(expr)

그룹핑되어 추출된 각각의 로우들에 대해 그룹핑 레벨을 알 수 있다.

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

, grouping_id(to_char(sales_dt, 'yyyy-mm')) group1

, grouping_id(product_id) group2

, grouping_id(to_char(sales_dt, 'yyyy-mm'), product_id) group3

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_id(to_char(sales_dt, 'yyyy-mm'), product_id) >= 0 (=0, >0 과 같이 조건을 변경해 조회해보자)

--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 GROUP1 GROUP2 GROUP3

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

2011-10 1 400 0 0 0

2011-10 2 900 0 0 0

2011-10 3 600 0 0 0

2011-10 소계 1900 0 1 1

2011-11 1 1400 0 0 0

2011-11 2 1200 0 0 0

2011-11 3 1000 0 0 0

2011-11 소계 3600 0 1 1

합계 합계 5500 1 1 3

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

GROUPING_ID 함수의 처리로직

① 맨 먼저 GROUPING 함수처럼 표현식 값의 NULL 여부에 따라 0과 1을 반환한다.

② 그 다음에 이 값을 비트벡터(bit vector)로 만든다. 즉 2진수로 변환한다.

③ 변환된 비트벡터를 다시 십진수로 변환한다.

④ 십진수로 변환된 값을 반환한다.

▷ GROUP_ID()

반환되는 로우가 중복되는 경우에는 1, 중복되지 않은 경우에는 0을 반환

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

, product_id

, sum(sales_volume) sales_volume

, group_id() g_id -- 반환되는 로우가 중복되는 경우에는 1, 중복되지 않은 경우에는 0을 반환

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 to_char(sales_dt, 'yyyy-mm'), rollup(to_char(sales_dt, 'yyyy-mm'), product_id) -- to_char(sales_dt, 'yyyy-mm') 중복 사용 가능

--having group_id() < 1 -- 중복 제거

;

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

SALES_MONTH PRODUCT_ID SALES_VOLUME G_ID

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

2011-10 1 400 0

2011-10 2 900 0

2011-10 3 600 0

2011-11 1 1400 0

2011-11 2 1200 0

2011-11 3 1000 0

2011-10 1900 0

2011-11 3600 0

2011-10 1900 1

2011-11 3600 1

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

▷ GROUPING_SETS(expr)

GROUP BY 절에 사용되며, expr에 명시된 컬럼이나 표현식에 따라 개별 집계를 수행한다.

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

, 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 grouping sets ( to_char(sales_dt, 'yyyy-mm'), product_id )

;

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

SALES_MONTH PRODUCT_ID SALES_VOLUME

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

2011-10 1900 -- 10월 합계

2011-11 3600 -- 11월 합계

1 1800 -- 1 합계

2 2100 -- 2 합계

3 1600 -- 3 합계

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