基金漲跌幅計算-LAG/LEAD分析函數介紹

以下將以2013-08-29至2013-09-04期間的『富達新興市場』基金淨值為例，計算每日基金淨值漲跌幅，漲跌幅是指營業日(本期，t)淨值(NAV)與前一營業日(前期，t-1)淨值的差異量，為達計算目的需在查詢結果集（Recordset）之資料列中同時呈現本期（t）與前期(t-1)基金淨值，ORACLE 9i及MSSQL 2012起提供LEAD/LAG分析函數以解決同一資料列中取得前/前期資料值。

LAG / LEAD: 在跟隨目前資料列的已指定實體位移中（t±N），提供存取資料列。語法如下:

LAG|LEAD (scalar_expr [,offset] [,default])           OVER ( [ partition_by_clause ] order_by_clause )

scalar_expr：

指定位移傳回值。可為任何純量（Scalar）類型運算式。 

offset：

差異期數。預設為1。 

default：

在第1或最後1期無傳回資料之指定回傳值。若未指定預設值，則回傳NULL。

OVER ( [ partition_by_clause ] order_by_clause)

partition_by_clause：資料分群（選擇性參數）。 未指定則視為所有資料列為同一群組。

order_by_clause: 資料順序（必要參數）。 若指定 partition_by_clause 時，則各資料分群中獨立各自排序。

功能	SQL (當月日曆)	說明
SQL	SELECT NAV_DATE   , NAV   , LAG(NAV) OVER(ORDER BY NAV_DATE)                 Pre_NAV   , NAV - LAG(NAV, 1) OVER(ORDER BY NAV_DATE)       Diff   , (NAV - LAG(NAV) OVER(ORDER BY NAV_DATE))          / LAG(NAV) OVER(ORDER BY NAV_DATE) * 100   "Diff(%)" , NAV - LEAD(NAV, 1) OVER(ORDER BY NAV_DATE DESC) "Diff(LEAD)" , ROW_NUMBER() OVER(ORDER BY NAV_DATE DESC)       Seq FROM   (   SELECT '2013-09-04' NAV_DATE, 20.9 NAV   --FROM DUAL   UNION ALL   SELECT '2013-09-03',  20.81   --FROM DUAL   UNION ALL   SELECT '2013-09-02',  20.93   --FROM DUAL   UNION ALL   SELECT '2013-08-30',  20.7   --FROM DUAL   UNION ALL   SELECT '2013-08-29',  20.61   --FROM DUAL   ) A WHERE 1=1 ORDER BY NAV_DATE DESC 註: ORACLE使用者請自行加回FROM DUAL	ü  ORACLE與MSSQL語法相同。 ü  LAG的OFFSET預設1，差異期數為1時可省。 ü  LAG/LEAD函數兩者差異在於方向相反，概念及用法實質上並無差異，可由ORDER BY中指定正/反向排序即可解決。
結果		ü  第1期的前期資料為NULL，可於default參數中設定，如 LAG(NAV, 1, 0)。

前述SQL中，以ROW_NUMBER函數產生序號（Seq），對於MSSQL2005/2008使用者而言，因尚未提供LAG/LEAD函數，則可利用ROW_NUMBER產生期數，再以Self-Join即可解決，後續將介紹此用法。

本範例所介紹之LAG / LEAD函數，對於前後期相關之運算相當實用，如半導體的Time Commonality分析，或客服系統報表的進線時間計算，可用一組簡單SQL即可計算完成。