자기보다 작은값중 최대값 하나만 읽어오기
조건.
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문을 잘 만드는 능력을 가질 수 있느냐 없느냐는 사고하는능력,
그리고 주어진 상황을 파악해서 이리저리 대처할 수 있는 능력이
어느 정도이냐에 따라서 결정된다.
이렇게도 생각해보고 저렇게도 생각해보자 방법은 많고 생각하는 사람에
따라 다르다. 오직 한길만이 항상 정답은 아니다.
조건.
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문을 잘 만드는 능력을 가질 수 있느냐 없느냐는 사고하는능력,
그리고 주어진 상황을 파악해서 이리저리 대처할 수 있는 능력이
어느 정도이냐에 따라서 결정된다.
이렇게도 생각해보고 저렇게도 생각해보자 방법은 많고 생각하는 사람에
따라 다르다. 오직 한길만이 항상 정답은 아니다.