데이터베이스 질문과 답변
by 하수 [2009.06.10 17:40:32]
===이런 ROWDATA를 가지고서=====
ECO_CD TERM TRD_DT AMOUNT
14.08.001.014.006 D 20030602 10326
14.08.001.014.006 D 20030603 10295
14.08.001.014.006 D 20030604 10299
14.08.001.014.006 D 20030605 10314
14.08.001.014.006 D 20030606 10361
14.08.001.014.006 D 20030609 10322
..........................................................................
=====================================이러한 쿼리로 ========================
SELECT ECO_CD,
TERM,
TRD_DT,
AMOUNT,
LAG(AMOUNT) OVER(PARTITION BY ECO_CD ORDER BY TRD_DT) AMOUNT_PDAY,
ㅡㅡ> DECODE(TRD_DT, TO_CHAR(ADD_MONTHS(TO_DATE(TRD_DT, ’yyyymmdd’), -1), ’yyyymmdd’), AMOUNT) AMOUNT_PMON <--------------
FROM FNE_ECO_DATA
WHERE ECO_CD = ’14.08.001.014.006’
요런쿼리인데용 --> <--- 요부분에 trd_dt가 캐릭터형인데 데이트타입으로 바꺼서 한달전날짜를 다시 캐릭터형으로해서 그떄의 amount값을 가져오려고 하는데요 . 자꾸 결과가 amount_pmon값이 안나오네요
==========================amount_pmon
ECO_CD TERM TRD_DT AMOUNT AMOUNT_PDAY AMOUNT_PMON
14.08.001.014.006 D 20030602 10326
14.08.001.014.006 D 20030603 10295 10326
14.08.001.014.006 D 20030604 10299 10295
14.08.001.014.006 D 20030605 10314 10299
==================제가 무엇을 놓치고 있나요? 고수님들 ㅠㅠ
SELECT ECO_CD,
TERM,
TRD_DT,
AMOUNT,
(SELECT AMONUT FROM FNE_ECO_DATA WHERE TRD_DT= TO_CHAR(ADD_MONTHS(TO_DATE(FED.TRD_DT,'YYYYMMDD'),-1),'YYYYMMDD')
FROM FNE_ECO_DATA FED
WHERE ECO_CD = '14.08.001.014.006'
래그함수쓴거는 전날의 AMOUNT값을 가저오려고 쓴거구용~~ 한달전 값을 가저오려고 DECODE문을 ?㎢쨉? 값을 못가저오네요ㅠ
(
SELECT '14.08.001.014.006' eco_cd, 'D' term, '20030502' trd_dt, 10426 amount FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030503', 10495 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030504', 10499 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030505', 10414 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030506', 10461 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030509', 10422 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030602', 10326 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030603', 10295 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030604', 10299 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030605', 10314 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030606', 10361 FROM dual
UNION ALL SELECT '14.08.001.014.006', 'D', '20030609', 10322 FROM dual
)
SELECT eco_cd, term, trd_dt, amount
, MIN(amount) OVER(ORDER BY TO_DATE(trd_dt,'yyyymmdd')
RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING) amount_mon
FROM fne_eco_data
WHERE eco_cd = '14.08.001.014.006'
;
) 버튼을 클릭하여 작성 하시면 됩니다.