데이터베이스 질문과 답변

구간 이율을 12개월의 년도별로 이율 조회 0 4 2,210

by 오라클초보 [SQL Query] [2013.10.29 15:23:57]


안녕하세요 쿼리 질문드려요.
시작일자 종료일자 이율
2010-12-14 2011-04-05 0.085
2011-04-06 2011-05-31 0.07
2011-06-01 2012-08-20 0.06
2012-08-21 9999-12-31 0.055

위와 같은 데이터 아래 결과와 같이 12개월 이율을 년도별로 조회를 하려고 합니다.
적요 1월 2월 3월 4월 5월 6월 7월 8월 9월 10월 11월 12월
2013년 0.055 0.055 0.055 0.055 0.055 0.055 0.055 0.055 0.055 0.055 　 　
2012년 0.06 0.06 0.06 0.06 0.06 0.06 0.06 0.055 0.055 0.055 0.055 0.055
2011년 0.085 0.085 0.085 0.07 0.07 0.06 0.06 0.06 0.06 0.06 0.06 0.06
2010년 　 　 　 　 　 　 　 　 　 　 　 0.085
종료일자의 월이 다음 row 시작일자의 월과 같으면 시작일자의 이율을 구하고
종료일자가 9999-12-31이면 현재 월까지 이율을 구하여야 합니다.

위처럼 나오고 싶은데 어떻하면 좋을까요?
by 마농 [2013.10.29 16:17:23] 
WITH t1 AS
(
SELECT '2010-12-14' sdt, '2011-04-05' edt, 0.085 rat FROM dual
UNION ALL SELECT '2011-04-06', '2011-05-31', 0.070 FROM dual
UNION ALL SELECT '2011-06-01', '2012-08-20', 0.060 FROM dual
UNION ALL SELECT '2012-08-21', '9999-12-31', 0.055 FROM dual
)
, t2 AS
(
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201001', 'yyyymm'), LEVEL - 1), 'yyyy') yyyy
     , TO_CHAR(ADD_MONTHS(TO_DATE('201001', 'yyyymm'), LEVEL - 1), 'mm') mm
     , TO_CHAR(ADD_MONTHS(TO_DATE('201001', 'yyyymm'), LEVEL - 1), 'yyyy-mm-dd') sdt
     , TO_CHAR(ADD_MONTHS(TO_DATE('201001', 'yyyymm'), LEVEL) - 1, 'yyyy-mm-dd') edt
  FROM dual
 CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(sysdate, 'mm'), TO_DATE('201001', 'yyyymm')) + 1
)
SELECT yyyy
     , MIN(DECODE(mm, '01', rat)) m01
     , MIN(DECODE(mm, '02', rat)) m02
     , MIN(DECODE(mm, '03', rat)) m03
     , MIN(DECODE(mm, '04', rat)) m04
     , MIN(DECODE(mm, '05', rat)) m05
     , MIN(DECODE(mm, '06', rat)) m06
     , MIN(DECODE(mm, '07', rat)) m07
     , MIN(DECODE(mm, '08', rat)) m08
     , MIN(DECODE(mm, '09', rat)) m09
     , MIN(DECODE(mm, '10', rat)) m10
     , MIN(DECODE(mm, '11', rat)) m11
     , MIN(DECODE(mm, '12', rat)) m12
  FROM (SELECT yyyy, mm
             , MIN(rat) KEEP(DENSE_RANK LAST ORDER BY a.sdt, a.edt) rat
          FROM t1 a, t2 b
         WHERE b.sdt <= a.edt(+)
           AND b.edt >= a.sdt(+)
         GROUP BY yyyy, mm
        )
 GROUP BY yyyy
 ORDER BY yyyy DESC
;
by 오라클초보 [2013.10.29 18:59:14]

마농님 도움 감사합니다.

추가적인 질문이 있습니다.
예시는 첫번째 시작일자가 2010년부터이지만
1980년, 1998년, 2002년이 될수도 있습니다.
즉, 첫번째 Row의 시작일자가 다를 경우는 어떻게 하면 좋을까요?

by 마농 [2013.10.30 09:08:13] 
WITH t1 AS
(
SELECT '2010-12-14' sdt, '2011-04-05' edt, 0.085 rat FROM dual
UNION ALL SELECT '2011-04-06', '2011-05-31', 0.070 FROM dual
UNION ALL SELECT '2011-06-01', '2012-08-20', 0.060 FROM dual
UNION ALL SELECT '2012-08-21', '9999-12-31', 0.055 FROM dual
)
, t2 AS
(
SELECT TO_CHAR(ADD_MONTHS(sdt, LEVEL - 1), 'yyyy') yyyy
     , TO_CHAR(ADD_MONTHS(sdt, LEVEL - 1), 'mm') mm
     , TO_CHAR(ADD_MONTHS(sdt, LEVEL - 1), 'yyyy-mm-dd') sdt
     , TO_CHAR(ADD_MONTHS(sdt, LEVEL) - 1, 'yyyy-mm-dd') edt
  FROM (SELECT TO_DATE(SUBSTR(MIN(sdt), 1, 7), 'yyyy-mm') sdt FROM t1)
 CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(sysdate, 'mm'), sdt) + 1
)
SELECT yyyy
     , MIN(DECODE(mm, '01', rat)) m01
     , MIN(DECODE(mm, '02', rat)) m02
     , MIN(DECODE(mm, '03', rat)) m03
     , MIN(DECODE(mm, '04', rat)) m04
     , MIN(DECODE(mm, '05', rat)) m05
     , MIN(DECODE(mm, '06', rat)) m06
     , MIN(DECODE(mm, '07', rat)) m07
     , MIN(DECODE(mm, '08', rat)) m08
     , MIN(DECODE(mm, '09', rat)) m09
     , MIN(DECODE(mm, '10', rat)) m10
     , MIN(DECODE(mm, '11', rat)) m11
     , MIN(DECODE(mm, '12', rat)) m12
  FROM (SELECT yyyy, mm
             , MIN(rat) KEEP(DENSE_RANK LAST ORDER BY a.sdt, a.edt) rat
          FROM t1 a, t2 b
         WHERE b.sdt <= a.edt(+)
           AND b.edt >= a.sdt(+)
         GROUP BY yyyy, mm
        )
 GROUP BY yyyy
 ORDER BY yyyy DESC
;
by 오라클초보 [2013.10.31 09:00:10]

마농님. 감사합니다.
도움이 많이 되었습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입