반올림 DATA의 오차 보정

조건.
    C1 C2        
    -- ----------
    A          33
    B          20
    C          11
    
    T100 TABLE에 위와같은 DATA가 있다.

문제.
    T100 TABLE의 C2 COLUMN 을 소수점 아래 3자리까지 반올림하여 비율을
    구하게 되면
  
    C1 C2        
    -- ----------
    A        .516
    B        .313
    C        .172

    와 같이 결과가 나온다.
    비율의 전체합이 1.000 이 되어야 하는데 1.001 이된다.
    0.001 만큼의 반올림 오차가 생긴 것이다.
    우리가 해결할 문제는 이 반올림 오차를 비율이 제일큰 값에 더하거나
    빼서 총 비율의 합이 정확히 1.000 이 되도록 만들어 보자는 것이다.
    전제조건은 C2의 총합을 구하기위해 테이블을 한번 읽을 수 있고,
    C2의 총합을 이용해 비율을 계산하기위해 테이블을 한번 더 읽을 수
    있다.
    T100 TABLE을 두번까지 읽는 것을 허락하지만 그이상은 읽어서는
    안된다.
    SELECT 문장 하나로 답을 구하라.
    결과가 아래와 같이 나오면 된다.

    C1 RATIO    
    -- ----------
    C        .172
    B        .313
    A        .515

    C1으로의 ORDER BY 는 해도좋고 안해도 좋다.

생각.
    DATA의 반올림 차이 보정을 염두에 두지 않는다면 어떻게 답을
    구할 수 있을지를 먼저 생각해 보자.
    엿보기1.우선 T100 TABLE을 한번 읽어서 C2의 총 합을 구할 수 있다
           그렇게 합을 구한 DATASET의 결과를 T100의 각 ROW C2 값에
            나눠주면 비율이 나올 것이다.
    엿보기2.문제는 C1에 대한 C2의 비율을 구한 후에 그합이 정확하게
            1.000 이 되지 않을 경우를 어떻게 처리할 것인가 하는
            것이다.
            이미 T100 TABLE을 2번 읽었기 때문에 더 이상 T100 TABLE을
            읽어서는 안된다.
            문제의 전제조건에 위배될 뿐만 아니라 실제로 2번 읽는 것
            만으로도 이미 대량의 DATA일 경우에는 치명적일 수 있다.
            그렇다면 어떻게 처리할 것인가.
            가장먼저 떠오르는 방법이 무엇인가?.
            우선 어떤 방향으로 처리 할 것인가를 생각해야 그것을
            구현 하기위한 방법을 구상 할 수 있을 것이다.
    엿보기3.어차피 비율의 총합과 1과의 차이가 오차이다.
            1에서 비율의 총합을 뺀 오차를 제일 큰 비율을 가진
            DATA에 그대로 더할 수 있다면 어떤 방법으로 문제를
            풀어가야 할 것인가?.
    엿보기4.우선 필요한 것은 비율의 총합을 구하는 일일 것이다.
            비율의 총합을 구하는 것은 어렵지 않으리라.
            우리가 이제까지의 여러주제를 통해 경험해본 일이 있기
            때문이다.
            복제를 이용해서 DATA의 총계를 구하는 방법을 이용하면
            TABLE을 더이상 ACCESS 하지 않고도 현재까지의 DATASET을
            이용하여 총계를 구할 수 있다.
            이단계를 거치면 DATASET에는 원래의 C1값과 그 비율
            그리고 비율의 총 합이 존재하게 된다.
    엿보기4.머리속에 천천히 DATA가 만들어지는 과정을 그려보자.
            여기까지 완성이 되었으면 위에서 언급했듯이 오차가
            얼마인지를 구하는 것은 어렵지 않다.
            1 에서 비율의 총합을 뺀 값이 제일 큰 비율에 더해야 할
            오차이다.
            1-비율의총합 이 비율의총합이 나오는 자리를 대체하는 일
            또한 어렵지 않다.
            이부분은 나중에 하기로 하고 비율의 총합까지만 구해놓자.
    엿보기5.이제 남은일은 이렇게 구해진 오차를 어디에다 더해
            줄 것인가 하는 것이다.
            비율이 제일 큰값에 더해야 한다는 것은 알고 있지만 그
            방법이 어떻게 되어야 하는지는 지금까지 생각해 보지
            않았다.
            어떤 방법으로 비율이 제일 큰값과 제일 마지막에 구해 놓은
            오차를 결합시킬 것인가.
            GROUP BY 를 이용한 SUM이 해답을 줄 수 있을 것이다.
            물론 더 좋은 훌륭한 방법이 있을 수 도있다.
            이제 비율이 제일 큰 값과, 오차를 가지고 있는 ROW를 어떻게
            하나의 GROUP으로 묶을 수 있을 지를 생각해 보자.
            비율의 총합이 제일 큰 DATA는 실제하는 C1값중 한값을
            KEY 로 가지고 있을 것이다.
            오차를 가진 ROW는 C1값에 NULL을 가지게 하자.
            그러면 오차를 가진 ROW가 제일 마지막에 오게 된다.
            NULL 그리고 뭐가될지 모르는 C1값에 어떻게 같은 GROUPING
            단위를 부여할 수 있을까?.
            그렇게 할 수 있다면 답은 다 나온 것이나 다름없다.
            하지만 최대 비율을 가진 C1값을 찾기위해 TABLE을 한번 더
            ACCESS 하지 않고는 지금으로써는 방법이 없다.
            생각을 조금 바꿔서 우회해 보자.
            GROUPING 단위가 반드시 C1 COLUMN 이 될 필요는 없다.
            단계4에서 구해놓은 DATASET에서도 ROWNUM을 추출할 수 있다.
            이제까지의 DATASET을 비율로 SORT 해서 앞에 ROWNUM이란
            번호표를 붙여주면 어떤일이 일어나는가?.
            이제까지의 DATASET중 비율이 제일 큰 것은 비율의 총
            합이다.
            그 바로 위의 DATA가 비율이 제일 큰 값을 갖는 DATA이다.
            두개 사이에는 RONUM이 반드시 1만큼 차이가 난다.
            전체 ROW의 수가 몇개인지는 몰라도 된다.
            이제 비율의 총합을 가진 DATA의 ROWNUM에서 1을 빼주면
            두 DATA를 묶어 줄 수 있는 GROUPING 단위가 만들어
            질 것이다.
            그렇다면 비율의 총합을 가진 DATA 인지 여부는 어떻게 알
            수 있는가?
            이미 앞에서 C1 값에 NULL을 부여 했다.
            C1 값이 NULL이면 ROWNUM에서 1을 빼주면 된다.
    엿보기6.이제 비율이 제일 큰 값과 1-비율총합 을 GROUP BY해서 SUM을
            구해 보자.
            나머지는 단계4의 DATSET과 마찬가지지만 비율이 제일큰값은
            보정된 전체 오차가 반영되어 결과가 나온다.
    엿보기7.그래도 한가지 문제가 남았다.
            ROWNUM과 보정된 비율은 구했는데 C1 값을 모른다.
            비율이 제일 큰 값을 뺀 나머지 DATA는 하나의 ROW를 GROUP BY
            해서 SUM 했고,비율이 제일 큰 ROW는 자신과 오차 두 DATA가
            SUM 되었다.
            이때 자신은 임의의 C1 값을 가지고 있었을 것이고 ,총합은
            NULL을 C1 값으로 가지고 있었다.
            MAX 또는 MIN 함수가 NULL을 제외시키고 값을 구해주므로
            C1에 MAX 또는 MIN함수를 걸어주면 모든 ROW의 C1 값이
            정상적인 원래의 자기 값을 가지게 된다.
        
해법.
    한 단계씩 SQL로 번역해 보자.
    단계1.보정을 고려하지 않은 상태의 비율은 아래와 같이 구할 수 있다.
            
          SELECT T1.C1  C1,
                 ROUND(T1.C2/T2.TOT,3)  C2_RATIO
          FROM  (SELECT SUM(C2) TOT FROM   T100 ) T2,
                 T100 T1

          C1 C2_RATIO        
          -- ----------
          A        .516
          B        .313
          C        .172
            
    단계2.DATA 복제를 이용해 비율의 총합까지를 구해보자.
          복제용 DATASET 의 첫번째 ROW와 조인이 이루어진 DATA는
          모두 자신의 정상 C1 값을 갖게 하고,
          두번째 ROW와 조인이 이루어진 DATA는 C1값을 무시하고 C1 대신
          NULL을 부여한다.
          GROUPING 단위가 될 문장은 "DECODE(RCNT,2,NULL,D1.C1)" 와
          같이 표현될 것이다.
          SUM을 추가하는 문장은 아래와 같고
        
          SELECT DECODE(RCNT,2,NULL,D1.C1) C1,
                 SUM(D1.C2_RATIO) C2_RATIO
          FROM  (SELECT T1.C1  C1,
                        ROUND(T1.C2/T2.TOT,3)  C2_RATIO
                 FROM ( SELECT SUM(C2) TOT FROM   T100 ) T2,
                        T100 T1
                ) D1,
               (SELECT ROWNUM RCNT FROM USER_TABLES WHERE ROWNUM < 3) D2
          GROUP BY
                 DECODE(RCNT,2,NULL,D1.C1)

          이제까지의 결과는 아래와 같다.

          C1 C2_RATIO  
          -- ----------
          A        .516
          B        .313
          C        .172
                  1.001

    단계3.이제 할일은 C2_RATIO의 순서로 정렬을 해서 비율의 합이
          제일 밑에 오고 비율이 가장 큰 A 가 바로 그 위에 오도록
          정렬을 한다.
          문제는 지금부터 만드는 문장도 최종적으로는 INLINE VIEW 로
          사용될 것이기 때문에 ORDER BY 구문을 줄 수가 없다.
          ORDER BY 없이 SORT를 한다 하면 GROUP BY가 떠오를 것이다.
          DATASET을 C2_RATIO 로 GROUP BY 하면 될 것이다. 하지만
          위의 DATASET을 C2_RATIO 값으로 SORT를 하기위해 다시
          INLINE VIEW로 문장을 집어넣는 대신 위의 문장에
          다음과 같은 GROUPING 단위를 추가 해보자.
          "DECODE(RCNT,2,NULL,D1.C2_RATIO)"
          합계를 가지는 ROW는 C1값에 NULL을 부여하고 원래 DATA는
          C2_RATIO를 가지게 하여 GROUP BY 하겠다는 의도이다.
          RATIO 값이 같은 DATA는 하나로 뭉쳐지지 않을까 하는 걱정은
          하지말자.바로뒤에 C1이 함께 GROUPING 단위로 따라오기
          때문에 그렇게 될 걱정은 없다.

          추가된 문장은 아래와 같다.

          SELECT  DECODE(RCNT,2,NULL,D1.C2_RATIO) KEY,
                  DECODE(RCNT,2,NULL,D1.C1) C1,
                  SUM(D1.C2_RATIO) C2_RATIO
          FROM   (SELECT T1.C1  C1,
                         ROUND(T1.C2/T2.TOT,3)  C2_RATIO
                  FROM ( SELECT SUM(C2) TOT FROM   T100 ) T2,
                         T100 T1
                 ) D1,
                 (SELECT ROWNUM RCNT
                  FROM   USER_TABLES WHERE ROWNUM < 3) D2
          GROUP BY
                 DECODE(RCNT,2,NULL,D1.C2_RATIO),
                 DECODE(RCNT,2,NULL,D1.C1)

          결과 DATASET 은 다음과 같이 나온다.

          KEY                                      C1 C2_RATIO  
          ---------------------------------------- - ----------
          .172                                     C       .172
          .313                                     B       .313
          .516                                     A       .516
                                                          1.001
          역시 우리가 원하던 대로 C2_RATIO의 합이 제일 밑으로 오고
          그 바로위에 C2_RATIO 가 가장 큰 A 가 왔다.
          여기서 사용할 COLUMN 은 C1 과 C2_RATIO 두개이다.
          ROWNUM도 필요하지만 ROWNUM을 이 단계에서 넣어주면 우리가
          원하는 형식의 DATASET을 만드는데 문제가 있어서 다음
          단계로 넘긴다.
    단계4.이번 단계에서는 위에서 언급했듯이 단계4의 DATASET에
          ROWNUM을 붙여주는 일을 한다.
          위의 DATASET을 INLINE VIEW로 넣고 ROWNUM과 C1,C2_RATIO를
          읽기만 하면된다.
          문장은 최종 결과에서 보고 결과를 보자.

          RNUM       C1 C2_RATIO  
          ---------- -- ----------
                   1 C        .172
                   2 B        .313
                   3 A        .516
                   4         1.001

    단계5.만들고자 하는 의도대로 DATA를 다 만들었다.
          이제 C1 이 NULL 이 아닌 DATA는 ROWNUM 을 GROUPING 단위로
          부여하고 C1이 NULL인(비율의 총합을 가진 합계) DATA는
          ROWNUM에서 1을 뺀값을 GROUPING 단위로 부여한 후
          GROUP BY 해 준다.
          이때 물론 위에서 만든 DATASET을 다시 INLINE VIEW로
          사용한다.
          비율의 합과 A 가 SUM이 될때는 비율의합 대신 1-비율의합을
          더해준다.
          GROUPING 단위의 COLUMN은 "DECODE(C1,NULL,RNUM-1,RNUM)" 와
          같이 쓰이고 값을 SUM하는 문장은
          "SUM(DECODE(C1,NULL,1- C2_RATIO,C2_RATIO))" 와 같이 된다.
          이때 "생각" 에서 설명했듯이 MAX(C1)을 함께 읽어온다.
          최종 문장은 아래와 같다.

          SELECT  MAX(C1) C1,
                  SUM(DECODE(C1,NULL,1- C2_RATIO,C2_RATIO)) RATIO
          FROM(
               SELECT  ROWNUM RNUM,C1,C2_RATIO
               FROM (
                     SELECT  DECODE(RCNT,2,NULL,D1.C2_RATIO) KEY,
                             DECODE(RCNT,2,NULL,D1.C1) C1,
                             SUM(D1.C2_RATIO) C2_RATIO
                     FROM   (SELECT T1.C1  C1,
                                    ROUND(T1.C2/T2.TOT,3)  C2_RATIO
                             FROM ( SELECT SUM(C2) TOT
                                    FROM   T100 ) T2,
                                    T100 T1
                            ) D1,
                            (SELECT ROWNUM RCNT FROM USER_TABLES
                             WHERE  ROWNUM < 3) D2
                     GROUP BY
                            DECODE(RCNT,2,NULL,D1.C2_RATIO),
                            DECODE(RCNT,2,NULL,D1.C1)
                    )
               )
          GROUP BY DECODE(C1,NULL,RNUM-1,RNUM)
뒷풀이.
    KEY가  C1 하나뿐만 아니라 여러개를 가질 경우도
    마찬가지로 풀린다.
    복잡한 만큼 효과가 있으리라 확신한다.
    처음단계에서 C2의 총합을 구하기 위해 어쩔 수 없이 TABLE을
    두번 읽는 부분만 해결 할 수 있다면 더욱 완벽할 수
    있을 것이다.