DATA 복사를 이용한 QUERY

조건.
    TABLE : TEST06

          YMD        LEASE    
    ---------   ----------
     19980115      2100000
     19980127      2400000
     19980316      2400000
     19980320      2400000
     19980720      2300000
     19980731      2200000
     19980822      2300000
     19980831      2200000
     19980906      2200000
     19980915      2200000
      
    TEST06 에 위와같이 DATA가 있다.
    YMD는 VARCHAR2 이고 LEASE는 NUMBER TYPE이다.

문제.
     1998년 1월 15일에 2,100,000원의 차입이 있은 이후 수시로
     차입이 있었다.
     1998년 말에 1월부터 당시까지 있었던 차입금에 대한 이자를
     매월 말일 기준으로 계산해서 매월말 지급되었어야할 이자를
     계산한다.
     이자에대한 이자는 계산하지 않는다.
     실제상황이라면 3개월 또는 1개월단위 2개월 단위로도 일어날 수
     있지만, 기간은 PARAMETER로 받아서 처리할 수 있으므로
     문제의 이해를 돕기위해 12개월로한다.
     계산 방식은 다음과 같다.
     1998년01월31일에는 19980115 에 차입한 2,100,000원에 대한
     31-15일 만큼의 이자와 1998년01월27일에 차입한 2,400,000원에 대한
     31-27만큼의 이자를 더해서 발생시켜주고,
     1998년02월28일에는 1998년1월에 차입한 2,100,000 + 2,400,000원
     에 대한 28일치의 이자를 발생시켜주며,
     1998년03월31일에는 1월에차입한 4,500,000원에대한 31일치의 이자와
     3월16일에 차입한 2,400,000에대한 31-16일 만큼의 이자와 3월20일에
     차입한 2,400,000원에대한 31-20만큼의 이자를 더해서 발생시켜주는
     형식으로 계산을 해나간다.
     결국 1월에 차입한 금액에 대한 이자는 1월부터 12월까지 12번
     발생하며,2월은 11번 ..이런식으로 12월에 차입한 금액은 12월에
     한번만 발생되면 된다.
     이율은 년 12.5% 이다.

     누누히 얘기하지만 해법은 여러가지가 있을 수 있다.
     따라서 제공된 문제를 제공된 방식으로만 해결하는게 정답이란
     얘기도 아니다.
     단,여기서 원하는 답은 TABLE에서 읽어온 DATA를 1월이면 12번,
     2월이면 11번 식으로 복사를 해서 사용하자는 것이다.
     그런 방식을 익히기 위하여 이번 주제가 마련된 것이기 때문이다.
생각.
     문제의 의도와 가고자하는 방향을 이해했으면 스스로
     문제를 해결해보자.
     아리송하면 엿보기로 넘어가자.
     가장 큰 고민은 어떻게 DATA COPY의 횟수를 월에따라 조절할 수
     있느냐 이다.
     엿보기1.12개월을 기간으로 계산된다는 조건이 있었으므로 일단
             RECORD가 12개있는 JOIN된 ROWNUM만을 읽어올 DATASET이
             필요하다.
     엿보기2.그리고 1월차입 DATA는 JOIN용 DATASET의 12개 RECORD
             전체와 JOIN을 걸고
             2월에는 2~12까지,3월에는 3~12까지 ...와 같은 식으로
             JOIN을 한다.
     엿보기3.JOIN을 통한 복사가 성공적으로 이루어지면,
             TEST06 TABLE의 각 RECORD에서 복사된 DATA는 매월말을
             기준으로 GROUPING할 KEY값을 만들어주고
             차입이 일어난 월이면 말일에서 차입일을뺀 일자에
             이율/365를 곱하고 이후월은 해당월의 일수에
             이율/365를 곱하면 된다.
     엿보기4.일련의 모든 과정이 마무리되면 매월말 별로 계산된 금액을
             일자별로 GROUP지어 주면 된다.
             12개(계산기간)만큼의 RECORD가 나올것이다.

해법.
     엿보기를 참고삼아 자신이 스스로 해법을 찾아보고 답을 비교해보자.
     도저히 뭘 요구하느지 이해를 못하겠다 싶으면 해법의 단계를
     하나씩 밟아보자.
     길이 보일 것이다.
     한단계씩 밟아보자.
     단계1.일단 12개의 RECORD가 있는 DATASET을 만들자.

     SELECT  ROWNUM C_CNT
     FROM    USER_TABLES
     WHERE   ROWNUM  < 13    
     12개 이상의 RECORD 가 들어있는 TABLE이라면 어떤 TABLE이라도
     FROM 절에 쓸 수있다.
     단계2.문제는 WHERE절이다. TEST06 과 JOIN을 걸긴 거는데 어떻게
           JOIN될 RECORD수를 조절하느냐 하느걸 해결하는 부분이
           WHERE 절이기 때문이다.
           이렇게 해보자.

      SELECT   어쩌구저쩌구
      FROM    (SELECT  ROWNUM C_CNT
               FROM    USER_TABLES
               WHERE  ROWNUM  < 13
              ) B,
               TEST06 A
      WHERE   C_CNT <= 12 -  TO_NUMBER(SUBSTR(A.YMD,5,2)) + 1
      이렇게 쓰면 어떻게 될까?.
      C_CNT가 1에서부터 JOIN이걸린다.
      즉 1월 차입금은 1~12의 RECORD와
         2월 차입금은 1~11의 RECORD와
         3월 차입금은 1~10의 RECORD와 같은식으로 JOIN이 걸린다.

      반대의 방법도 있다.

      SELECT   어쩌구저쩌구
      FROM    (SELECT  ROWNUM C_CNT
               FROM    USER_TABLES
               WHERE  ROWNUM  < 13
              ) B,
               TEST06 A
      WHERE    LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                        LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM')
                       )  >=  TO_DATE(A.YMD,'YYYYMMDD')
      이렇게 쓰면 된다.
      TEST06 에서 읽어온 각 RECORD의 YMD중 년4자리 에 JOIN된 RECORD의
      CNT를 월로 대체해 붙이고,
      다시 DATE TYPE으로 바꾼뒤 그 월의 마지막 일자를 구한것이
      TEST06의 YMD보다 큰 일자가 되는 RECORD 와만 JOIN을 하겠단
      이야기다.

      즉 1월 차입금은 1~12의 RECORD와
         2월 차입금은 2~12의 RECORD와
         3월 차입금은 3~12의 RECORD와 같은식으로 JOIN이 걸린다.
      두가지 모두를 사용하여 계산이 가능하지만 여기서는 2번째것을
      이용하기로 한다.
      첫번째 방식은 스스로 해보기 바란다.
     단계3.이제  "SELECT 어쩌구저쩌구" 를 해결하자.
           먼저 JOIN된 B.C_CNT를 이용해 복사된 각 RECORD의 집계월을
           계산한다.

     LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
     LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM'))
     이건 무슨말인가?.
     JOIN된 B.C_CNT를 '1998'에 붙여서 마지막 일자를 붙여서
     DATE TYPE으로 바꾼뒤 마지막 일자를 구하자는 얘기다.
     1998년01월15일 같은 경우는 JOIN된 B.C_CNT의 첫번째와 결합되어
     19980131,19980228,19980331,.....19981231 의 12개 RECORD가
     생기며 나머지도 마찬가지 형식으로 복사가 일어난다.
     단계3.COPY되어온 RECORD의 월이 YMD의 월가 같으면
           해당월의 마지막일자에서 YMD를 뺀일자만큼이 필요하고
           그렇지 않으면 해당월의 일수가 필요하다.
      IF B.C_CNT 가 A.YMD의 월을 숫자로 바꾼게 같으면
         A.YMD의 앞자리4개와 C_CNT를 2자리 CHAR로 바꾼것을 붙여서
         DATE TYPE으로 바꾼후 그월의 마지막일자를 구하고,
         그 마지막 일자에서 A.YMD를 일자로 바군것을 뺀다.
      ELSE 즉 같지않으면
         A.YMD의 앞자리4개와 C_CNT를 2자리 CHAR로 바꾼것을 붙여서
         DATE TYPE으로 바꾼후 그월의 마지막일자를 구하고,
         그 마지막일자를 'DD' 형식의 CHAR로 전환해서 NUMBER TYPE으로
         바꾼다.
      END IF;
  
      이말을 IF문으로 바꾸면 다음과 같고
      IF B.C_CNT = TO_NUMBER(SUBSTR(A.YMD,5,2)) THEN
         LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                          LPAD(TO_CHAR(C_CNT),2,'0'),
                 'YYYYMM')
                 ) - TO_DATE(A.YMD,'YYYYMMDD')
      ELSE
         TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                                    LPAD(TO_CHAR(C_CNT),2,'0'),
                                   'YYYYMM')
                                   ),
                      'DD')
                  )
      END IF;
          
      다시 SELECT 절에서 사용가능한 DECODE로 바꾸면 다음과 같다.
      DECODE(B.C_CNT,TO_NUMBER(SUBSTR(A.YMD,5,2)),
                     LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                     LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM')) -
                     TO_DATE(A.YMD,'YYYYMMDD'),
             TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                       LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM')),'DD'))
             )  
     A.YMD를 기준으로 보면 다음과 같은 DATA가 나올 것이다.

     YMD        YYMMDD    TERM      
     ---------- --------- ----------
     19980115   98/01/31          16
     19980115   98/02/28          28
     19980115   98/03/31          31
     19980115   98/04/30          30
     19980115   98/05/31          31
     19980115   98/06/30          30
     19980115   98/07/31          31
     19980115   98/08/31          31
     19980115   98/09/30          30
     19980115   98/10/31          31
     19980115   98/11/30          30
     19980115   98/12/31          31
     19980127   98/01/31           4
     19980127   98/02/28          28
     19980127   98/03/31          31
     19980127   98/04/30          30
     19980127   98/05/31          31
     19980127   98/06/30          30
     19980127   98/07/31          31
     19980127   98/08/31          31
     19980127   98/09/30          30
     19980127   98/10/31          31
     19980127   98/11/30          30
     19980127   98/12/31          31
     19980316   98/03/31          15
     19980316   98/04/30          30
     19980316   98/05/31          31
     19980316   98/06/30          30
     점점점......

     단계4.이제 다 했다.
           마지막으로 집계일자별로 나온금액을 SUM할때  
           계산된일자 * 차입금 * 년이율 / 365 만 해주면된다.

      SELECT  LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                       LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM'))  YYMMDD,
              SUM((LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
                            LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM'))
                         - TO_DATE(A.YMD,'YYYYMMDD'))
                   * LEASE * 0.125 / 365
                 ) AMT
      FROM  ( SELECT  ROWNUM C_CNT
              FROM    USER_TABLES
              WHERE  ROWNUM  < 13
            ) B,
              TEST06 A
      WHERE   LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
              LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM'))
              >=  TO_DATE(A.YMD,'YYYYMMDD')
      GROUP BY LAST_DAY(TO_DATE(SUBSTR(A.YMD,1,4)||
               LPAD(TO_CHAR(C_CNT),2,'0'),'YYYYMM'))

      결과는 이렇다.

      YYMMDD    AMT      
      --------- ----------
      98/01/31  14794.5205
      98/02/28  57945.2055
      98/03/31  127089.041
      98/04/30  222636.986
      98/05/31  321369.863
      98/06/30  416917.808
      98/07/31  524315.068
      98/08/31  677910.959
      98/09/30  895308.219
      98/10/31  1136301.37
      98/11/30  1369520.55
      98/12/31   1610513.7

뒷풀이.
      VARCHAR2 TYPE의 일자를 날자형식으로 치환하고 재치환해서
      일수를 계산 하는것 때문에 지저분해서 그렇지 그렇게 복잡한
      문장은 아니다.
      이곳에서 이해하기를 바라는 것은 단 한가지 DATA를 원하는 수
      만큼 복사해서 내가 원하는 새로운 DATA 를 만드는 과정이다.
      계산 기간을 "ROWNUM < 13" 로 고정 시켰지만 얼마든지
      PARAMETER화 해서 유연성을 높일 수 있으며,
      하나의 ROW를 읽어서 LOOP을 수행해야하는 번거로움을 덜 수
      있다는 장점이 있다.
      한 주제에 하나씩의 DATA 처리 방식을 익힌다면 그로인해 얻어지는
      이익은 그 이상이라고 생각한다.
      열심히 익히자.
사족. 아는게 힘이다.
      단지 쉬운길을 놔두고 어렵게 돌아가지만 않는다면.....