CREATE TABLE TEST35 (KEY1 VARCHAR2(05),KEY2 VARCHAR2(05), AMT NUMBER);
CREATE INDEX TEST35_I ON TEST35(KEY1,KEY2);
INSERT INTO TEST35 VALUES ('0001','A', 500);
INSERT INTO TEST35 VALUES ('0001','B', 400);
INSERT INTO TEST35 VALUES ('0002','A', 400);
INSERT INTO TEST35 VALUES ('0002','B', 300);
INSERT INTO TEST35 VALUES ('0003','A', 600);
INSERT INTO TEST35 VALUES ('0003','B', 400);
INSERT INTO TEST35 VALUES ('0004','A', 700);
INSERT INTO TEST35 VALUES ('0004','B', 300);
INSERT INTO TEST35 VALUES ('0005','A', 800);
INSERT INTO TEST35 VALUES ('0005','B', 200);
INSERT INTO TEST35 VALUES ('0006','A', 700);
INSERT INTO TEST35 VALUES ('0006','B', 600);


복제를 이용한 합계에서 비율계산

조건.
    TEST35 에 아래와 같은 DATA 가  있다.

    KEY1  KEY2  AMT      
    ----- ----- ----------
    0001  A            500
    0001  B            400
    0002  A            400
    0002  B            300
    0003  A            600
    0003  B            400
    0004  A            700
    0004  B            300
    0005  A            800
    0005  B            200
    0006  A            700
    0006  B            600

문제.
    위의 DATA를 이용해 다음과 같은 결과를 보여라.

    KEY1  A          B          C          PER      
    ----- ---------- ---------- ---------- ----------
    0001         500        400        100         20
    0002         400        300        100         25
    0003         600        400        200         33
    0004         700        300        400         57
    0005         800        200        600         75
    0006         700        600        100         14
    합계        3700       2200       1500         41

    A   = KEY2 의 A에 해당하는 값
    B   = KEY2 의 B에 해당하는 값
    C   = A - B
    PER = 100 * C / A

    각 KEY1 에 대해서뿐만 아니라 합계에 대해서도 동일한 규칙을
    적용한다.
    특히 합계 ROW 의 PER 값은 001 ~ 006 의 PER의 합이 아니다.
    합계 ROW의 100*C/A 를 적용한 값이다.

생각.
    이제까지의 단계를 숙지했다면 결코 어려운 문제는 아닌 듯 하다.
    우리는 이미 이책에서 뿐만 아니라 여러 좋은 교재를 통해서
    최종합계를 SQL안으로 끌어들여 함께 보여주는데 익숙하다.
    이문제는 단지 그과정의 반복이라고도 할 수 있다.
    단지 합계의 PER 에 해당하는 부분을 구하는 것이 단순히 DATA의
    합이 아니라는 것이다.
    계산된 자신의 값들을 이용해 한번 더 계산이 이루어 져야 한다.
    문제를 푸는 열쇠는 합계를 구하는데서 끝나지 않고 그 결과를
    DATASET으로 이용하여 한번 더 가공이 이루어져야 한다는 데 있다.
    
해법.
    단계1.우선 RECORD 로 들어있 가장 기본이 되는 A와 B의 값을 같은
          ROW 안으로 끌어들이자.
          C와 PER도 A와B를 이용해 계산되므로 A와 B를 먼저 같은 ROW에
          나타내야 할 것이다.
          이러한 유형의 문제또한 이미 우리에게 익숙하다.
          길게 설명하지 않고 식을 보자.

          SELECT KEY1,
                 SUM(DECODE(KEY2,'A',AMT)) A,
                 SUM(DECODE(KEY2,'B',AMT)) B
          FROM   TEST35
          WHERE  KEY1 > ' '
          AND    KEY2 > ' '
          GROUP BY KEY1

          KEY1  A          B        
          ----- ---------- ----------
          0001         500        400
          0002         400        300
          0003         600        400
          0004         700        300
          0005         800        200
          0006         700        600

    단계2.전단계에서 만들어 놓은 DATASET이 우리가 이용해야 하는 가장
          기본이 되는 DATASET이다.
          C,PER 그리고 합계를 구하는 것은 어렵지 않을 것이다.
          단지 합계의 PER값이 단순 합계가 아니라는데 어려움이 있다.
          2건의 ROW를 가진 DATASET으로 복제를 한다.
          첫번째 ROW와 JOIN이 된 DATA는 원래 값으로 GROUP BY 한다.
          두번째 ROW와 JOIN이 된 DATA는 모두 '합계'로 GROUP BY 한다.          

          SELECT DECODE(RNUM,2,'합계',KEY1) KEY1,
                 SUM(A)           A,
                 SUM(B)           B,
                 SUM(A-B)         C
          FROM   단계1의DATASET,
                (SELECT ROWNUM RNUM
                 FROM   USER_TABLES WHERE ROWNUM < 3)
          GROUP BY
                 DECODE(RNUM,2,'합계',KEY1)
          
          일반적으로 합계를 구하는 문제였다면 이번 단계까지만
          수행하면 된다.
          하지만 현재 까지로는 합계ROW의 PER 값을 구하지 못했다.
          합계의 PER는 각 PER값의 합계도 아니고 평균도 아니다.
          구해진 합계를 다시한번 이용해
          PER = 100 * C / A    식을 적용해야만 구할 수 있는 값이다.
    단계3.결론은 단계2에서 끝나는 문제가 아니고 한번 더  전단계를
          DATASET으로 이용해 가공이 이루어져야 한다는 것이다.
              
          SELECT KEY1,A,B,C,ROUND(100*(A-B)/A) PER
          FROM   단계2의DATASET
          와 같이.
          최종 결과는 아래와 같다.

          SELECT KEY1,A,B,C,ROUND(100*(A-B)/A) PER
          FROM (  
                SELECT DECODE(RNUM,2,'합계',KEY1) KEY1,
                       SUM(A)           A,
                       SUM(B)           B,
                       SUM(A-B)         C
                FROM (SELECT KEY1,
                             SUM(DECODE(KEY2,'A',AMT)) A,
                             SUM(DECODE(KEY2,'B',AMT)) B
                      FROM   TEST35
                      WHERE  KEY1 > ' '
                      AND    KEY2 > ' '
                      GROUP BY KEY1 ) T1,
                     (SELECT ROWNUM RNUM
                      FROM   USER_TABLES WHERE ROWNUM < 3)
                GROUP BY DECODE(RNUM,2,'합계',KEY1)
                )
뒷풀이.
    INLINE VIEW를 사용하는데 거부감을 가질 수도 있다.
    실제로 DATASET의 범위를 충분히 줄이지 못하면서 계속 INLINE VIEW로
    양파껍질처럼 둘러싼다면 문제가 있을 수도 있다.
    하지만 실제로 그렇다 하더라도 어차피 동일한 DATA를 구하기 위해
    TABLE을 한번 더 읽는 등의 다른 작업을 해야한다면 그보다는
    나을 것이다
    그렇게 생각한다면 최선의 선택이 될 수 있다.
    두려움과 거부감까지 가질 대상은 아닌듯 하다.