[oracle] 적금 월복리

* 적금 월복리

WITH BA AS (
   SELECT A.REV_NO, A.RCV_DT,  A.RCV_AMT
        , ROW_NUMBER() OVER(ORDER BY a.REV_NO DESC) AS LV 
     FROM (
              SELECT LEVEL AS REV_NO //적립회차 
                  , ADD_MONTHS(TO_DATE('20220101', 'YYYYMMDD'), LEVEL-1) AS RCV_DT //적금일자
                  , 100000 AS RCV_AMT //적금금액
                FROM DUAL
              CONNECT BY LEVEL < =12 //12개월로 row 형성
           ) A
  )
, T1 AS (
  SELECT 0.05 AS ITR   //금리
       , 12 AS D        //금리적용기간(12개월)
   FROM DUAL
)
SELECT   SUM( O2_1)
  FROM 
(
    SELECT  R.REV_NO
          , MIN(R.LV)
          , MIN(R.RCV_AMT)
          , EXP(SUM(LN(O2))) AS O2_1
       FROM (
           SELECT  BA.REV_NO, LV
                 , BA.RCV_DT
                 , BA.RCV_AMT
                 , BA. RCV_AMT*POWER(1+(ITR/D ), LV) AS O2 //복리계산
             FROM BA, T1 
            ORDER BY BA.REV_NO   
      ) R
GROUP BY R.REV_NO
ORDER BY R.REV_NO
) R;