Data Architecture/SQL Query
[SQL] 오라클 누적 곱 계산 방법
EstenPark
2013. 9. 23. 15:02
오라클에서 누적 곱을 구하는 방법을 아래와 같이 정리했습니다.
첫번 째 : 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