ROLLUP

ROLLUP?

소계(중간합계)와 전체합계를 구하는 방법 중 SQL만 사용해서 구할 수 있는 기능이 ROLLUP입니다.

 

ROLLUP은 GROUP BY 뒤에 ROLLUP이라고 적어서 사용합니다. 

예를들어 GROUP BY ROLLUP(A,B,C,D)라고 사용하면 다음과 같은 데이터들이 조회됩니다.

- GROUP BY 된 A+B+C+D별 데이터

- A+B+C 별 소계 데이터

- A+B 별 소계 데이터

- A별 소계 데이터

- 전체 합계

 

SQL> 
 select to_char(t1.ord_dt,'YYYYMM') 주문년월
 ,t1.cus_id 고객ID
 ,sum(t1.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);
 
 
 주문년월 고객ID                                   주문금액   
------ ---------------------------------------- -----------
201703 CUS_0001                                        2800
201703 CUS_0002                                        4300
201703                                                 7100
201704 CUS_0001                                        5000
201704 CUS_0002                                        1900
201704                                                 6900
                                                      14000

ROLLUP을 사용할 경우 주문년월별 소계가 추가되고, 전체 합계가 추가되었습니다.

 

ROLLUP의 컬럼순서?

 

ROLLUP에 컬럼순서는 매우중요합니다. 컬럼순서에 따라 다른 소계가 나오기 때문입니다.

 

1.GROUP BY ROLLUP(A,B,C,D) : A+B+C별 소계, A+B별 소계, A별 소계, 전체합계

2.GROUP BY ROLLUP(B,A,C,D) : B+A+C별 소계, B+A별 소계, B별 소계, 전체합계

 

순서에 따라 다른 소계가 나오게 됩니다.

원하는 값을 잘 파악하여 원하는 값이 정확하게 나올 수 있도록 쿼리를 짜야 할 것입니다.

 

GROUPING?

GROUPING함수는 특정 컬럼의 값이 소계인지 아닌지 구분해줍니다. 

ROLLUP으로 만들어진 소계에서 ROLLUP된 컬럼은 NULL로 표시됩니다. 그러므로 원래 NULL값인 데이터가 ROLLUP되면 원래 데이터인지 ROLLUP된 결과인지 구분할 수 없습니다.

이때 필요한것이 GROUPING함수입니다.

 

GROUPING함수는 해당 컬럼이 ROLLUP처리되었으면 1을 반환하고, 그렇지 않으면 0을 반환합니다.

--GROUPING 함수 사용 전

SQL>
select t1.ord_st,t1.pay_tp, count(*) ord_cnt
from t_ord t1
group by rollup(t1.ord_st,t1.pay_tp);


ORD_ST                                   PAY_TP                                   ORD_CNT    
---------------------------------------- ---------------------------------------- -----------
COMP                                     BANK                                             915
COMP                                     CARD                                            1827
COMP                                                                                     2742
WAIT                                                                                      305
WAIT                                                                                      305
                                                                                         3047
                                                                                         
                                                                                         
                                                                                         
--GROUPING함수 사용 후
SQL>
select t1.ord_st, grouping(t1.ord_st) gr_ord_st,
t1.pay_tp, grouping(t1.pay_tp) gr_ord_st,
count(*) ord_cnt
from t_ord t1
group by rollup(t1.ord_st,t1.pay_tp);

ORD_ST                                   GR_ORD_ST   PAY_TP                                   GR_ORD_ST   ORD_CNT    
---------------------------------------- ----------- ---------------------------------------- ----------- -----------
COMP                                               0 BANK                                               0         915
COMP                                               0 CARD                                               0        1827
COMP                                               0                                                    1        2742
WAIT                                               0                                                    0         305
WAIT                                               0                                                    1         305
                                                   1                                                    1        3047