2018年3月27日 星期二

利用CTE遞迴計算零存整付複利報酬以及應用數學推導解決問題

本範例為類似零存整付之複利報酬程式開發作業,通常會以程式迴圈方式處理,在此提供以遞迴運算指令以一組SQL指令完成所需,一位網友提供以財務公式直接計算求得,利用數學概念取代原有迴圈邏輯,以下列出公式推導過程及SQL
假設抵達目標金額是1000萬,選擇一個年投資報酬率10%的產品,每月固定投資2萬元,請問要多久才可以抵達目標?

提示:複利計算公式 S = P(I + r)^n (^在這裡是表示次方)
輸出範例:XX年後可以達到目標

方法1: CTE (Common Table Expression)
首先使用CTE (Common Table Expression)以遞迴運算處理此問題,請注意ORACLE雖在9i版本即已提供WITH指令,但僅具暫作用,11g R2版本後才提供遞迴運算功能,SQL及運行結果如下。

MSSQL 2005
ORACLE 11g R2
SQL
WITH RESULT(N, AMT, Interest)
AS
(
SELECT 1
, CAST(2 AS FLOAT)
, CAST(0 AS FLOAT)
UNION ALL
SELECT N+1
, AMT*(1+ 0.1/12) + 2
, AMT*(0.1/12)
FROM RESULT
WHERE AMT < 1000
--WHERE AMT*(1+ 0.1/12) + 2 < 1000
)
SELECT *
FROM RESULT
OPTION (maxrecursion 500)
WITH RESULT(N, AMT, Interest)
AS
(
SELECT 1
, CAST(2 AS FLOAT)
, CAST(0 AS FLOAT)
FROM DUAL
UNION ALL
SELECT N+1
, AMT*(1+ 0.1/12) + 2
, AMT*(0.1/12)
FROM RESULT
WHERE AMT < 1000
)
SELECT *
FROM RESULT
結果

方法2: 數學
參考網友所回覆之方法,以下撰寫數學推論過程。



MSSQL 2005
ORACLE 11g R2
SQL
SELECT LOG(1000/2*0.1/12+1)
/ LOG(1+0.1/12)
SELECT LOG((1+0.1/12), (1+10000000/20000 *0.1/12))
FROM DUAL

請注意,MSSQL僅提供固定以10e為底之對數函數,但可用換底作法處理(如上)。

2018年3月24日 星期六

DATEADD 及ADD_MONTHS日期運算函數之差異比較

透過日期資料加減運算,可將日期資料延後或提前。SQL SERVER提供DATEADD()函數,可針對各種時間區間單位(如年、月、日),以增加或減少整數單位數,進行日期單位增減運算。ORACLE則提供以月為增減整數單位的ADD_MONTHS函數,ORACLE 9遵循SQL 99提供INTERVAL型態,藉此可達成各種日期或混合單位的運算,TO_YMINTERVAL函數則是提供特定年月字串轉換為日期型態功能。

SQL SERVER所提供的DATEADD()函數,第一個引數為時間間隔單位(Interval),用以指定增、減運算的時間間隔單位,常用參數整理如下表。第二個引數為增、減時間間隔單位的次數(Count),此值可為正數(未來),或負數(過去),但需特別注意,此值可接受浮點數,當指定的數值不是整數時,系統會自動捨棄值小數部分。第三個引數為基準日期(Date),將基準日期加上Count次數的Interval後,即為傳回值之日期。
Interval
縮寫
yyyyyy
qqq
mmm
ddd
wkww
hh
min
sss
毫秒
ms

ORACLE對日期加減運算提供以月為增減單位的ADD_MONTHS函數。至於其他日期單位如週、日、小時、分、秒均是以「天」為單位進行加減運算,「季」則可轉換為以月為單位的計算,而「年」則可採用TO_YMINTERVAL函數處理。

在此將兩種資料庫對「月」為單位的日期加減運算函數之差異,在此特別以2016-01-30(非月底)、2009-01-31(月底)、2016-02-28(月底)及2016-04-30(月底)四筆基準日期,分別增加12個月,以說明兩函數應用上的異同點。SQL語去及執行結果如下所示:
SQL SERVER
ORACLE
SELECT Target
    , "月底"
    , DATEADD(M, 1, Target)    "M+1"
    , DATEADD(M, 2, Target)    "M+2"
FROM
  (
  SELECT '2016-01-30' Target, '' "月底"
  UNION ALL
  SELECT '2016-01-31',   ''
  UNION ALL
  SELECT '2016-02-28',   ''
  UNION ALL
  SELECT '2016-04-30',   ''
  ) A
SELECT Target
    , "月底"
    , ADD_MONTHS(Target, 1)   "M+1"
    , ADD_MONTHS(Target, 2)    "M+2"
FROM
  (
  SELECT DATE '2016-01-30' Target, '' "月底"
  FROM DUAL
  UNION ALL
  SELECT DATE '2016-01-31',   ''
  FROM DUAL
  UNION ALL
  SELECT DATE '2016-02-28',   ''
  FROM DUAL
  UNION ALL
  SELECT DATE '2016-04-30',   ''
  FROM DUAL
  )

SQL執行結果如下表。
(1)  對於非月底日的2016-01-30增加12個月兩種測試結果均相同。

但月底日的三筆測試,兩個函數的執行結果則有所差異(以字註記)
(2)  2016-01-31測試,兩者相同結果;
(3)  2016-02-29為例,DATEADD函數增加12個月結果均為28日,但ADD_MONTHS運算則月底日即03-3104-30
(4)  2016-04-30為例,DATEADD的結果為05-3006-30,而ADD_MONTHS運算仍為月底日即05-3106-30

SQL SERVER(DATEADD)

ORACLE(ADD_MONTHS)
Target
月底
M+1
M+2
2016-01-30
02-29
03-30
2016-01-31
02-29
03-31
2016-02-29
03-29
04-29
2016-04-30
05-30
06-30

Target
月底
M+1
M+2
2016-01-30
02-29
03-30
2016-01-31
02-29
03-31
2016-02-29
03-31
04-30
2016-04-30
05-31
06-30

將兩個函數之Pseudo Code整理如下表:

SQL SERVER(DATEADD)
ORACLE(ADD_MONTHS)
Pseudo Code
1.  目標月份的同一天日期是否存在
存在(合法)則直接傳回
2.  若不存在則傳回最後一天(與ORACLE不為月底的定義相同)

IF 判斷基準日期為月底日 THEN
則目標月份都是傳回的最後一天
ELSE 若基準日期不為月底
則傳回目標月份的同一天日期,若不存在則傳回最後一天。


ADD_MONTHS(ORACLE)DATEADD(MSSQL)函數於月份運算差異分析

基準日期(月底日
基準日期(非月底日)
DATEADD

目標日期
MIN(月底日,同一日)
目標日期
MIN(月底日,同一日)
ADD_MONTHS
目標日期
月底日
目標日期
MIN(月底日,同一日)