자기보다 작은값중 최대값 하나만 읽어오기

조건.
     TABLE : TEST04

     YMD        US_AMOUNT
     ---------- ----------
     19980102         3171
     19980203         3142
     19980304         3113
     19980405         3084
     19980701         3055
     19980802         3026
     19980903         2997
     19981004         2968
     19981102         2939

     TABLE : TEST05

     YMD        EXC_RATE  
     ---------- ----------
     19971231         1800
     19980630         1300
     19970630          800
     19961231          780
     19980331         1500

     TEST04에는 년월일별로 해당일의 달러금액 이 있고
     TEST05에는 반기또는 분기별로 당시의 환율을 가지고 있다.

문제.
     1.TEST04 를 읽어서 각 RECORD별로 달러금액
     2.TEST05의 그보다 작은 일자중 최근일자가 가지고 있는 환율
     3.원화환산금액
     일때 1*2=3
     과 같은 형식의 값을 보고싶다.
     단 일자가 다음과 같은 DATA만을 원한다.
     '19980102','19980203','19980304','19980405','19980701',
     '19980802','19980903','19981004','19981102'
     결과가 아래와 같으면 된다.

     YMD        US_AMOUNT  EXC_RATE   A.US_AMOUN
     ---------- ---------- ---------- ----------
     19980102         3171       1800    5707800
     19980203         3142       1800    5655600
     19980304         3113       1800    5603400
     19980405         3084       1500    4626000
     19980701         3055       1300    3971500
     19980802         3026       1300    3933800
     19980903         2997       1300    3896100
     19981004         2968       1300    3858400
     19981102         2939       1300    3820700      

생각.
     난이도가 높은 문제는 아니다.
     어느정도 QUERY를 사용해본 사람이면 쉽게 답을
     구할 수 있는 문제다.
     차근차근 생각을 해보고 혼자 해결해보자
     답 또한 간단하다.
     엿보기1.여기서 제시하는 해결 방법은 세가지다.
             그 첫번째가 SUB QUERY이다.
             두 TABLE을 JOIN 시킨후 TEST05의 YMD를
             비교하기위해 TEST04의 YMD보다 작은값중 최대값을
             TEST05 에서 다시 SUB-QUERY 로 읽어서 비교하면된다.
             누구나 쉽게 생각 할 수 있는 부분이다.
     엿보기2.두번째방법은 뭘까?..
             두개의 TABLE을 날짜로 JOIN을걸면된다.
             단지 EQUAL JOIN을 할것인가...
                  부등호 JOIN을 할 것인가....
             둘다 원하는 값을 가져올 수 없을 것이다.
             단지 엿보기1의 SUB QUERY가 하는 역할을 담당할 DATASET을
             IN-LINE VIEW로 가지고 있으면 RECORD 한건마다
             SELECT가 되는게 아니고 JOIN이 된다는 것이다.
     엿보기3.그렇다면 IN-LINE VIEW는 TEST04 의 각 일자가
             TEST05의 어떤 날짜를 읽어와야하는가 하는 정보를 가지고
             있어야한다.
             서로간을 연결결해 줄 수있는 다리가 필요하다는 것이다.
     엿보기4.그리고 만들어진 IN-LINE VIEW로 TEST04와 IN-LINE VIEW를
             연결하고 다시 IN-LINE VIEW와 TEST05를 연결하면 된다.
    
     엿보기5.세번째 방법은 HINT 를 사용한다.
             HINT 중에 INDEX_DESC 이라는 HINT 가 있다.
해법 .
     해법이라고 해봤자 엿보기의 내용을 DB가 알아먹는 말로
     옮기는 일일 뿐이다.
     스스로 해보자.
     스스로 생각하고 스스로 고민하면서 해결하는 문제가 응용력을
     키워주고 자신의 재산으로 남는 것이리라.
     단계1.우선 SUB QUERY를 통한 해결은 이렇다.

          SELECT A.YMD,
                 A.US_AMOUNT,
                 A.US_AMOUNT * B.EXC_RATE
          FROM   TEST04 A,
                 TEST05 B
          WHERE  B.YMD = (SELECT MAX(C.YMD)
                          FROM   TEST05 C
                          WHERE  C.YMD < A.YMD)
          AND    A.YMD  IN ('19981102','19981004','19980903',
                           '19980802','19980701','19980405',
                           '19980304','19980203','19980102')

          두번째방법은 단계2부터이다.
     단계2.그럼먼저 할 일은 두 TABLE간의 연결을 위해 TEST04가
           TEST05의 어떤값을 참조할 것인가 하는 정보를
           읽어오는 것이다.
           그 것을 지정하기 위해 다음 QUERY를 이용해보자.

           SELECT   A.YMD YMD,MAX(B.YMD) R_YMD
           FROM     TEST04 A,
                    TEST05 B
           WHERE    B.YMD < A.YMD
           GROUP BY A.YMD

           YMD        R_YMD    
           ---------- ----------
           19980102   19971231  
           19980203   19971231  
           19980304   19971231  
           19980405   19980331  
           19980701   19980630  
           19980802   19980630  
           19980903   19980630  
           19981004   19980630  
           19981102   19980630  
           이게 뜻하는 것은 SUB QUERY를 통해 RECORD마다 한번씩
           수행되어 나오는 결과가 한번에 읽혀와 JOIN만을 기다리고
           있다는 것이다.
     단계3.다음단계가 뭐가 필요하겠는가 연결만 시켜주면된다.

           SELECT A.YMD,
                  A.US_AMOUNT,
                  B.EXC_RATE,
                  A.US_AMOUNT * B.EXC_RATE
           FROM ( SELECT  A.YMD YMD,MAX(B.YMD) R_YMD
                  FROM    TEST04 A,
                          TEST05 B
                  WHERE A.YMD IN ('19981102','19981004','19980903',
                                  '19980802','19980701','19980405',
                                  '19980304','19980203','19980102')
                  AND   B.YMD < A.YMD
                  GROUP BY A.YMD
                ) C,
                  TEST05 B,
                  TEST04 A
           WHERE A.YMD IN ('19981102','19981004','19980903',
                           '19980802','19980701','19980405',
                           '19980304','19980203','19980102')
           AND   C.YMD = A.YMD
           AND   B.YMD = C.R_YMD

           결과는 이렇게 아래와 같이 나온다.

           YMD        US_AMOUNT  EXC_RATE   A.US_AMOUN
           ---------- ---------- ---------- ----------
           19980102         3171       1800    5707800
           19980203         3142       1800    5655600
           19980304         3113       1800    5603400
           19980405         3084       1500    4626000
           19980701         3055       1300    3971500
           19980802         3026       1300    3933800
           19980903         2997       1300    3896100
           19981004         2968       1300    3858400
           19981102         2939       1300    3820700      
     단계4.세번째 방법은 가장 효과적인 방법이라고 할 수 있다.
           HINT 사용해서 TEST05 TABLE의 INDEX를 역순으로
           ACCESS하는 것이다.
           INDEX_DESC 이라는 HINT는 지정한 TABLE의 지정한 INDEX를
           내림차순으로 ACCESS한다.
           주어진 일자보다 작은 값을 역 INDEX롤 ACCESS 하게 한 후
           ROWNUM = 1인 ROW 만을 읽어오면된다.
           문장은 아래와 같다.
           주의:INDEX 명칭을 지정하지 않아서 SYSTEM이 부여한 명칭을
                사용했다.
                SYS_C0013288

           SELECT A.YMD,
                  B.YMD,
                  A.US_AMOUNT,
                  A.US_AMOUNT * B.EXC_RATE
           FROM   TEST05 B,
                  TEST04 A
           WHERE  B.YMD
                = (SELECT /*+ INDEX_DESC(TEST05 SYS_C0013288) */ YMD
                   FROM   TEST05
                   WHERE  YMD < A.YMD
                   AND    ROWNUM = 1
                  )
           AND    A.YMD  IN ('19981102','19981004','19980903',
                            '19980802','19980701','19980405',
                            '19980304','19980203','19980102')
           주어진 일자의 환율정보를 가지고 있는 일자는 찾는 방법에서
           약간의 차이가 있을 뿐이다.
뒷풀이.  
      RESPONSE TIME을 비교해봐도 IN-LINE VIEW가 SUB QUERY보다
      빠르지 못하다.
      하지만 이런 방법도 있다 라는걸 알았다는게 중요하다.
      SQL문을 잘 만드는 능력을 가질 수 있느냐 없느냐는 사고하는능력,
      그리고 주어진 상황을 파악해서 이리저리 대처할 수 있는 능력이
      어느 정도이냐에 따라서 결정된다.
      이렇게도 생각해보고 저렇게도 생각해보자 방법은 많고 생각하는 사람에
      따라 다르다. 오직 한길만이 항상 정답은 아니다.