ROLLUP을 대신하는 방법

소계를 구하는 ROLLUP을 사용하지 않고도 소계를 구할 수 있는 방법이 있습니다.

 

 

ROLLUP 쿼리

select to_char(t1.ord_dt,'YYYYMM') ord_ym,t1.cus_id,sum(t1.ord_amt) ord_amt
from t_ord t1
where t1.cus_id in ('CUS_0001','CUS_0002')
and t1.ord_dt >= to_date ('20170301','YYYYMMDD')
and t1.ord_Dt < to_date('20170501','YYYYMMDD')
group by rollup (to_char(t1.ord_dt,'YYYYMM'),t1.cus_id);


ORD_YM CUS_ID                                   ORD_AMT    
------ ---------------------------------------- -----------
201703 CUS_0001                                        2800
201703 CUS_0002                                        4300
201703                                                 7100
201704 CUS_0001                                        5000
201704 CUS_0002                                        1900
201704                                                 6900
                                                      14000

 

UNION ALL 사용하기

정렬순서는 다르지만 결과는 같습니다. 하지만 UNION ALL로 인해 같은 테이블을 3번 접근하고 있기 때문에 성능에서 손해가 발생할 수 있습니다. 소계가 필요한 만큼 UNION ALL이 계속 늘어나기 때문에 성능적으로 점점 손해를 볼 수 있습니다.

select to_char(t1.ord_dt,'YYYYMM') ord_ym,t1.cus_id,sum(t1.ord_amt) ord_amt
from t_ord t1
where t1.cus_id in ('CUS_0001','CUS_0002')
and t1.ord_dt >= to_date ('20170301','YYYYMMDD')
and t1.ord_Dt < to_date('20170501','YYYYMMDD')
group by  to_char(t1.ord_dt,'YYYYMM'),t1.cus_id

UNION ALL
select to_char(t1.ord_dt,'YYYYMM') ord_ym,'total' cus_id,sum(t1.ord_amt) ord_amt
from t_ord t1
where t1.cus_id in ('CUS_0001','CUS_0002')
and t1.ord_dt >= to_date ('20170301','YYYYMMDD')
and t1.ord_Dt < to_date('20170501','YYYYMMDD')
group by to_char(t1.ord_dt,'YYYYMM')

UNION ALL
select 'total' ord_ym,'total' cus_id,sum(t1.ord_amt) ord_amt
from t_ord t1
where t1.cus_id in ('CUS_0001','CUS_0002')
and t1.ord_dt >= to_date ('20170301','YYYYMMDD')
and t1.ord_Dt < to_date('20170501','YYYYMMDD')

ORD_YM CUS_ID                                   ORD_AMT    
------ ---------------------------------------- -----------
201704 CUS_0002                                        1900
201704 CUS_0001                                        5000
201703 CUS_0001                                        2800
201703 CUS_0002                                        4300
201703 total                                           7100
201704 total                                           6900
total  total                                          14000

 

카테시안 조인 사용하기

where절에 t2 (인라인 뷰)와 t1간의 조인 조건이 없으므로 카테시안 조인이 발생합니다.

단점은 테이블에 데이터가 너무 많으면 조인 과정에서 성능 저하가 발생할 수 있습니다. 그리고 SQL을 작성하는 방식이 어렵습니다. 

select case when t2.rno=1 then to_char(t1.ord_dt,'YYYYMM')
        when t2.rno =2 then to_char(t1.ord_dt,'YYYYMM')
        when t2.rno=3 then 'total' end ord_ym
       , case when t2.rno=1 then t1.cus_id
        when t2.rno=2 then 'total'
        when t2.rno=3 then 'total' end cus_id
        ,sum(t1.ord_amt) ord_amt
      from t_ord t1
      ,(select rownum rno from dual connect by rownum <= 3) t2
 where t1.cus_id in ('CUS_0001','CUS_0002')
and t1.ord_dt >= to_date ('20170301','YYYYMMDD')
and t1.ord_Dt < to_date('20170501','YYYYMMDD')

group by case when t2.rno=1 then to_char(t1.ord_dt,'YYYYMM')
        when t2.rno =2 then to_char(t1.ord_dt,'YYYYMM')
        when t2.rno=3 then 'total' end
       , case when t2.rno=1 then t1.cus_id
        when t2.rno=2 then 'total'
        when t2.rno=3 then 'total' end
        
        
ORD_YM CUS_ID                                   ORD_AMT    
------ ---------------------------------------- -----------
total  total                                          14000
201704 CUS_0002                                        1900
201704 CUS_0001                                        5000
201704 total                                           6900
201703 CUS_0001                                        2800
201703 CUS_0002                                        4300
201703 total                                           7100

 

WITH과 UNION ALL 사용하기

 

with t_res as (
select to_char(t1.ord_dt,'YYYYMM') ord_ym,t1.cus_id,sum(t1.ord_amt) ord_amt
from t_ord t1
where t1.cus_id in ('CUS_0001','CUS_0002')
and t1.ord_dt >= to_date ('20170301','YYYYMMDD')
and t1.ord_Dt < to_date('20170501','YYYYMMDD')
group by  to_char(t1.ord_dt,'YYYYMM'),t1.cus_id
)

select t1.ord_ym,t1.cus_id,t1.ord_amt
from t_res t1
union all
select t1.ord_ym,'total',sum(t1.ord_amt)
from t_res t1
group by t1.ord_ym
union all
select 'total','total',SUM(t1.ord_amt)
from t_res t1;


ORD_YM CUS_ID                                   ORD_AMT    
------ ---------------------------------------- -----------
201704 CUS_0002                                        1900
201704 CUS_0001                                        5000
201703 CUS_0001                                        2800
201703 CUS_0002                                        4300
201703 total                                           7100
201704 total                                           6900
total  total                                          14000

이와 같이 다양한 방법으로 소계를 구할 수 있습니다.

 

CUBE

CUBE는 조합가능한 모든 소계를 만듭니다. 사용방법은 ROLLUP과 같습니다. ROLLUP 대신 CUBE를 넣어서 사용할 수 있습니다.

CUBE는 성능의 문제도 있으며, 불필요한 정보까지 생성될 수 있으므로 거의 사용하지 않습니다.

 

GROUP BY CUBE(A,B.C)라고 하면,

A+B,A,A+C,B+C+B,C 이렇게 6개의 소계가 만들어집니다.

 

 

 

GROUPING SETS

소계를 만드는 또 하나의 방법입니다. ROLLUP 대신에 GROUPING SETS라고 표기한 후 그룹화할 컬럼을 괄호로 묶어 나열해 주면 됩니다. 소계가 필요한 집합(SET)을 일일이 지정할 수 있습니다. 전체합계가 필요하면 빈 괄호를 추가하면 됩니다.

 

select to_char(t1.ord_dt,'YYYYMM') ord_ym
,t1.cus_id
,count(*) ord_cnt
,sum(t1.ord_amt) ord_amt
from t_ord t1
where 
t1.cus_id in ('CUS_0061','CUS_0062')
group by grouping sets(
    (to_char(t1.ord_dt,'YYYYMM'),t1.cus_id) --GROUP BY 기본데이터
     ,(to_char(t1.ord_dt,'YYYYMM')) -- 주문년월 별 소계
     ,(t1.cus_id) -- 고객ID별 소계
     ,() --전체합계
     );
     
     
   ORD_YM CUS_ID                                   ORD_CNT     ORD_AMT    
------ ---------------------------------------- ----------- -----------
201702 CUS_0061                                           2        3800
201703 CUS_0061                                           3        7620
201704 CUS_0061                                           3       10900
...생략
       CUS_0061                                          33       63020 -- 고객ID별 소계
       CUS_0062                                          34       47700
..생략
201702                                                    2        3800 -주문년월별 소계
201703                                                    6       11920 
201704                                                    6       14000
                                                         67      110720 --전체합계

 

 

 

 

출처 : SQL BOOSTER 유일환 지음