[SQL] 오라클 누적 곱 계산 방법

오라클에서 누적 곱을 구하는 방법을 아래와 같이 정리했습니다.

첫번 째 : NULL 존재할 경우 무조건 누적 값을 계산 한다.

두번 째 : NULL 존재할 경우 누적 곱을 새로이 계산 한다.

LN : LN 함수는 입력값의 자연 로그 값으로 반환하며, 0보다 큰 값이 나타낸다.

SUM : 함계를 계산한다.

EXP : 누적 곱을 계산한다.

 

1. NULL 존재할 경우 무조건 누적 값을 계산 

WITH T 
     AS (SELECT '20130503' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130504' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130505' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130506' TRD_DT,1.0883949 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130507' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130508' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130509' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130510' TRD_DT,1.0000293 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130511' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130512' TRD_DT,1.0000293 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130513' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130514' TRD_DT,1.0000293 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130515' TRD_DT,1.0001234 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130516' TRD_DT,1.0001234 RT 
         FROM   DUAL) 
SELECT T.*,
       -- 누적 곱을 계산
       -- NULL 값이 존재 할 경우 전일자 값을 그대로 사용
       ROUND(EXP(SUM(LN(RT)) 
                     OVER (ORDER BY TRD_DT ASC)), 9) rt
FROM   T 

2. NULL 존재할 경우 누적 곱을 새로이 계산

WITH T 
     AS (SELECT '20130503' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130504' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130505' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130506' TRD_DT,1.0883949 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130507' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130508' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130509' TRD_DT,NULL RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130510' TRD_DT,1.0000293 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130511' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130512' TRD_DT,1.0000293 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130513' TRD_DT,1 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130514' TRD_DT,1.0000293 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130515' TRD_DT,1.0001234 RT 
         FROM   DUAL 
         UNION ALL 
         SELECT '20130516' TRD_DT,1.0001234 RT 
         FROM   DUAL) 
SELECT X.*,
       -- RN_Q 집합 별로 누적곱을 새로이 계산
       ROUND(EXP(SUM(LN(X.RT)) 
             OVER( PARTITION BY RN_Q 
                   ORDER BY X.TRD_DT ASC)), 9) EXP_RT 
FROM   (SELECT T.*,
               -- NULL 경우 1씩 증가
               COUNT(DECODE(RT, NULL, 1))  
                         OVER ( ORDER BY TRD_DT ASC) RN_Q 
        FROM   T) X