농부지기

☞ ROLLUP 예제1

원하는 결과 ] 
    --------------------------------------------------------------------------
      계정명           전표일자     일련번호     차변금액         대변금액 
    --------------------------------------------------------------------------
    인건비            2010.01.01        1           100
    인건비            2010.01.01        2           110
    인건비            2010.03.03        1           110
    인건비            2010.03.04        1                            50
    인건비계                                        270
    차량유지비        2010.02.14      300            50
    차량유지비       2010.02.26        3            20
    차량유지비        2010.03.15       50           100
    차량유지비계                                    150
    총계                                            570              50 

SQL : 
   WITH DATA1 AS (
       SELECT '인건비'     AS NAME, '2010.01.01' AS DATE1, 1   AS NUMBER1, 100  AS AMT1, NULL  AS AMT2, '비고1' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.01.01' AS DATE1, 1   AS NUMBER1, 100  AS AMT1, NULL  AS AMT2, '비고2' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.02.01' AS DATE1, 2   AS NUMBER1, 110  AS AMT1, NULL  AS AMT2, '비고3' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.03.03' AS DATE1, 1   AS NUMBER1, 110  AS AMT1, NULL  AS AMT2, '비고4' AS NOTES FROM DUAL UNION ALL
       SELECT '인건비'     AS NAME, '2010.03.04' AS DATE1, 1   AS NUMBER1, NULL AS AMT1, 50    AS AMT2, '비고5' AS NOTES FROM DUAL UNION ALL
       SELECT '차량유지비' AS NAME, '2010.02.14' AS DATE1, 300 AS NUMBER1, 50   AS AMT1, NULL  AS AMT2, '비고6' AS NOTES FROM DUAL UNION ALL
       SELECT '차량유지비' AS NAME, '2010.02.26' AS DATE1, 3   AS NUMBER1, NULL AS AMT1, 20    AS AMT2, '비고7' AS NOTES FROM DUAL UNION ALL
       SELECT '차량유지비' AS NAME, '2010.03.15' AS DATE1, 50  AS NUMBER1, 100  AS AMT1, NULL  AS AMT2, '비고8' AS NOTES FROM DUAL 
    )

     SELECT DECODE(A.GUBUN, 0, A.NAME, 1, A.NAME || ' 계', 3, '총계')   AS "계정명"
          , A.DATE1  AS "전표일자"
          , A.NOTES
          , DECODE(A.GUBUN, 0, A.NUMBER1, 1, NULL)                      AS "일련번호"
          , DECODE(A.GUBUN, 0, A.AMT1, 1, A.AMT1 - A.AMT2, 3, AMT1)     AS "차변금액"
          , DECODE(A.GUBUN, 0, A.AMT2, 1, NULL, 3, AMT2)                AS "대변금액"
          , GUBUN
       FROM (
             SELECT A.NAME
                  , A.DATE1
                  , A.NOTES
                  , MIN(A.NUMBER1)   AS NUMBER1
                  , SUM(AMT1)    AS AMT1
                  , SUM(AMT2)    AS AMT2
                  , GROUPING_ID(A.NAME, A.DATE1) AS GUBUN
               FROM DATA1 A
              GROUP BY ROLLUP(a.name, (a.date1, a.number1, a.notes))
             ) A ; 


☞ GROUPING_ID

정의 : GROUPING_ID함수는 행과 관련되는 GROUPING 비트 벡터에 대응되는 수치를 반환.
       GROUPING_ID함수는 행과 관련된 GROUPING 비트 벡터에 대응하는 수치를 반환한다. 
        GROUPING_ID는 ROLLUP,CUBE,GROUPING함수와 같은 GROUP BY 확장 기능을 포함한 SELECT문장에서
       적용할수 있다. 많은 GROUP BY 표현을 가지는 쿼리에서,많은 GROUPING함수를 필요로 하는 
       특정한 행의 GROUP BY 레벨을 지정하기 위해서는 복장한 SQL이 요구된다. 
       GROUPING_ID는 이런 경우 유용하다.

       GROUPING_ID함수는 다수의 GROUPING함수의 결과와 비트 벡터(1과 0의 문자열)를 연결하는 것과
       동일하다. GROUPING_ID를 이용하여서 다수의 GROUPING함수를 사용하지 않고서,
       표현하기 위한 행의 필터 조건이 간단해진다. 행 필터링은 원하는 행이 GROUPING_ID=n의 
       단일 조건으로 정의 될수 있어서 더 쉬워진다. 
       함수는 단일 테이블에서 다수의 집계의 레벨을 저장할때 유용하다.

SQL 예 : SELECT channel_id, promo_id, sum(amount_sold) s_sales  
               , GROUPING(channel_id) gc
              , GROUPING(promo_id) gp
              , GROUPING_ID(channel_id, promo_id) gcp
              , GROUPING_ID(promo_id, channel_id) gpc 
         FROM   sales 
         WHERE  promo_id > 496 
         GROUP BY CUBE(channel_id, promo_id) ;  


결과)

    C  PROMO_ID      S_SALES       GC         GP        GCP       GPC 
    - ----------   ---------- ---------- ---------- ---------- ---------- 
    C        497     26094.35       0         0         0         0 
    C        498      22272.4       0         0         0         0 
    C        499      19616.8       0         0         0         0 
    C       9999     87781668       0         0         0         0 
    C              87849651.6       0         1         1         2 
    I        497      50325.8       0         0         0         0 
    I        498      52215.4       0         0         0         0 
    I        499     58445.85       0         0         0         0 
    I       9999    169497409       0         0         0         0 
    I               169658396       0         1         1         2 
    P        497     31141.75       0         0         0         0 
    P        498      46942.8       0         0         0         0 
    P        499        24156       0         0         0         0 
    P       9999     70890248       0         0         0         0 
    P              70992488.6       0         1         1         2 
    S        497    110629.75       0         0         0         0 
    S        498     82937.25       0         0         0         0 
    S        499     80999.15       0         0         0         0 
    S               267480357       0         1         1         2 
    T        497       8319.6       0         0         0         0 
    T        498      5347.65       0         0         0         0 
    T        499        19781       0         0         0         0 
    T       9999     28095689       0         0         0         0 
    T              28129137.3       0         1         1         2 
             497    226511.25       1         0         2         1 
             498     209715.5       1         0         2         1 
             499     202998.8       1         0         2         1 
             9999    623470805       1         0         2         1 
                     624110031       1         1         3         3 

설명)
   1. GROUPING(channel_id) gc
         : 0 -> channel_id가 Record에 나오는 ROW는 0
                즉, CUBE, ROLL_UP 후 해당 컬럼별로 SUM한 결과가 나오는 ROW는 0
           1 -> channel_id가 Record에 나오지 않는 ROW는 1
                즉, CUBE, ROLL_UP 후 총 SUM한 결과가 나오는 ROW는 1

   2. GROUPING_ID(channel_id, promo_id) gcp
         : 0 -> channel_id, promo_id 가 Record에 나오는 ROW는 0
           1 -> channel_id는 존재하고, promo_id는 미존재하는 Record일 경우 ROW는 1
           2 -> channel_id는 미존재하고, promo_id는 존재하는 Record일 경우 ROW는 2
           ....