SQL 쿼리 중 '집계' , '정산' 같은 계산이 필요할 경우 사용하는

그룹함수...

대표적으로 는 Group by 가 있지..

그중 Group by 의 한계? 를 개선? 한 여러가지 함수가 있다는 사실

이번에는 ROLLUP ,  CUBE , GROUPING SETS 에 대해 이야기해 보자 -_-;

그..3가지도 -_- 쪼개서 이 글에서는...ROLLUP 까지만,,

실습은 첨부한  test_darkneo_1.zip

참고 하던지..자기가 만들던지..선택!!

용어에 대한 설명은 인터넷 서핑 하거나 책보면  더 자세하게 나오므로 생략 ...
여기서는 어떻게 , 언제 쓰는가에  대해 중점으로

일단은..
오리지날 데이터

COMPANY_CD CUSTOMER_CD TRAN_DATE SUBJECT    AMT                   
---------- ----------- --------- ---------- ----------------------
11111      99999       20061201  AAA        100                   
11111      99999       20061202  BBB        200                   
11111      77777       20061203  AAA        200                   
22222      99999       20061204  CCC        500                   
22222      99999       20061203  CCC        400                   
11111      88888       20061202  BBB        200                   
11111      88888       20061203  BBB        200                   

7 rows selected  

이 데이터를 기준으로 작업시작..

1. Group BY
요구조건 :  COMPANY_CD ,CUSTOMER_CD,  SUBJECT 별로 집계 집계
                각 그룹별의  합계AMT   구하시오
               

요거는 쉽다 -_-; 그치?
SQL :

SELECT company_cd ,
             customer_cd ,
            '200612' as tran_date ,
            subject ,
           sum(amt)
  FROM t_darkneo
 GROUP BY  ( company_cd , customer_cd ,  subject )

결과 :

COMPANY_CD CUSTOMER_CD TRAN_DATE SUBJECT    SUM(AMT)              
---------- ----------- --------- ---------- ----------------------
11111      99999       200612    BBB        200                   
22222      99999       200612    CCC        900                   
11111      99999       200612    AAA        100                   
11111      88888       200612    BBB        400                   
11111      77777       200612    AAA        200          

         

5 rows selected

2. Rollup
요구조건 :  SUBJECT 별로 소계 ,  CUSTOMER_CD , SUBJECT  소계 ,
                COMPANY_CD , CUSTOMER_CD ,SUBJECT 소계 를 구하고 총집계도 구하시오 .

요것도 별로 어렵지는 않지..SQL 을 여러번 사용한다면야 -_-;
문제는 pl-sql의 매력은 한방!! 어떤 복잡한 쿼리도 한방에 해야되는거지..ㅎㅎ


일단 SQL 를 여러번 사용한다면 어떻게 해야할까??

잠깐..생각을 필히 하는것이 좋지 않을까나.....

..
.
.
.
.

음..정답이라고 하기는 그렇지만..

가장 간단하 계 생각한다면, Group by 를 여러개 만들어서 Union All 로 처리하면 되겠지?

WITH temp as (
SELECT company_cd ,
       customer_cd ,
       '200612' as tran_date ,
       subject ,
       sum(amt)
  FROM t_darkneo
 GROUP BY  ( company_cd , customer_cd ,  subject )
UNION ALL
SELECT company_cd,
        customer_cd,
        '200612' as tran_date ,
       '*',
       sum(amt)
  FROM t_darkneo
 GROUP BY  (  company_cd , customer_cd   )
UNION ALL
SELECT company_cd,
        '*',
        '200612' as tran_date ,
        '*',
       sum(amt)
  FROM t_darkneo
 GROUP BY  (  company_cd  )
 ) select *
    from temp
order by  company_cd , customer_cd desc ,  subject desc  ;

이 SQL 은 안 적을려다가 -_- 내가 test 한다고 ㅡ.ㅡ; 쓴게 아까워서..

order by 는 보기 좋으라고 -_- 썼다는;

아 '*' 는 union all 자리 맞추기도 그렇고 '*' 나왔다는 것은 그 이전값들의 합이라고 생각하면 될듯;

결과 :

COMPANY_CD CUSTOMER_CD TRAN_DATE SUBJECT    SUM(AMT)              
---------- ----------- --------- ---------- ----------------------
11111      99999       200612    BBB        200                   
11111      99999       200612    AAA        100                   
11111      99999       200612    *             300                   
11111      88888       200612    BBB        400                   
11111      88888       200612    *            400                   
11111      77777       200612    AAA        200                   
11111      77777       200612    *            200                   
11111      *             200612    *            900                   
22222      99999       200612    CCC       900                   
22222      99999       200612    *            900                   
22222      *             200612    *            900                   

11 rows selected

그러나..이런 무식한 쿼리는 -_- 말이 안되겠지;

결정적으로 저렇게 긴 쿼리 사용하는것도 그렇고..

그래서 오라클에서 -_- 제공한 함수

ROLLUP  두둥!!

SELECT NVL(company_cd,'*') as company_cd ,
             NVL(customer_cd,'*') as customer_cd ,  
            '200612'                      as tran_date ,
            NVL(subject,'*')          as subject,
           sum(amt)                    as amt
  FROM t_darkneo
 GROUP BY ROLLUP ( company_cd , customer_cd ,  subject );

그 긴 쿼리 문이 요렇게 간단히 -_-;

결과;

COMPANY_CD CUSTOMER_CD TRAN_DATE SUBJECT    AMT                   
---------- ----------- --------- ---------- ----------------------
11111      77777       200612    AAA         200                   
11111      77777       200612    *              200                   
11111      88888       200612    BBB         400                   
11111      88888       200612    *              400                   
11111      99999       200612    AAA         100                   
11111      99999       200612    BBB         200                   
11111      99999       200612    *             300                   
11111      *             200612    *             900                   
22222      99999       200612    CCC        900                   
22222      99999       200612    *             900                   
22222      *             200612    *             900                   
*             *            200612    *             1800  

12 rows selected

잘 나오지? 음..근데 문제는 -_-;

*             *            200612    *             1800  

이 데이터 겠지 이런 데이터는 필요가 없으니깐..
그럼 이 데이터는 어떻게 제거 하냐? -_-

그건 다음 시간에 .....쿨럭;

결론
ROLLUP (A , B ) = GROUP BY ( A , B )
                           UNION ALL
                           GROUP BY ( A )
                           UNION ALL
                           ALL 합계