以下將以休假日引用前一營日之結算匯率(價格)為例，說明ORACLE之FIRST_VALUE及LAST_VALUE函數中IGNORE NULLS關鍵字用途及MSSQL對應做法。如下圖中，由於2018-01-13及2018-01-14為星期六日例假日匯市休市，因此將取得前一營業日（2018-01-12）為結算參考價，如下圖：
ORACLE的FIRST_VALUE及LAST_VALUE語法如下，可指定RESPECT NULLS（預設值）及IGNORE NULLS RESPECT NULLS將直接傳回第一筆（或最後一筆）運算結果，無論其是否為NULL，此為ORACLE函數預設值(MSSQL現行版本之設定值)；IGNORE NULL則指定僅回傳運算結果中的第一筆非NULL值之資料，本範例將應用此特性，如當天為休假日時，由於無參考匯率/價格可供使用（即NULL），則需取得最後營業日匯率為參考匯率，本範例介紹此參數之使用方式，並說明MSSQL如何達成。
ORACLE
FIRST_VALUE | LAST_VALUE
( expression [ RESPECT NULLS | IGNORE NULLS ] )
OVER
(
[ PARTITION BY
[ ORDER BY
)
|
ORACLE語法如下，結果如上圖。RESPECT NULLS為預設值，請注意視框架（WINDOW）設定為ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW即由排序資料集中的第一筆資料為起始點而目前資料列(Row)為結束點，當使用LAST_VALUE函數預設將取得目前資料列（當天）；但如額外使用IGNORE NULLS關鍵字，則會往前找到非NULL值（營業日）的資料，即為所求，此功能非常實用有效，可往前遞推找到最後一筆具值的資料，可解決相當多難以克服的問題，如仍無法解決所需則建議可採用MODEL指令，此將原資料集視為EXCEL，可達成類似在EXCEL進行互相參照功能。
SELECT D.DT
, R.Fx_Rate
, LAST_VALUE(R.Fx_Rate IGNORE NULLS)
OVER (ORDER BY D.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "IGNORE"
, LAST_VALUE(R.Fx_Rate RESPECT NULLS)
OVER (ORDER BY D.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "RESPECT"
, LAST_VALUE(R.DT IGNORE NULLS)
OVER (ORDER BY D.DT ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FX_DT
FROM
(
--產生6天[日曆日]
SELECT TO_CHAR(DATE'2018-01-11' + LEVEL -1, 'YYYYMMDD') DT
FROM DUAL
CONNECT BY LEVEL <= 6
) D
LEFT OUTER JOIN
(
--產生4天[營業日]匯率資料
SELECT '20180111' DT, 29.604 Fx_Rate FROM DUAL
UNION ALL
SELECT '20180112' DT, 29.631 Fx_Rate FROM DUAL
UNION ALL
SELECT '20180115' DT, 29.536 Fx_Rate FROM DUAL
UNION ALL
SELECT '20180116' DT, 29.550 Fx_Rate FROM DUAL
) R
ON D.DT = R.DT
ORDER BY D.DT
MSSQL並無對應關鍵字可用，但可透過子查詢達成，為避免SQL過於龐大而難以理解，請先建立測試資料。
--Step 00. 建立測試資料
--DROP TABLE #Forex
SELECT D.DT
, R.Fx_Rate
INTO #Forex
FROM
(
--產生6天[日曆日]
SELECT CONVERT(CHAR(8), DATEADD(dd, number, '20180111'), 112) DT
FROM master.dbo.spt_values
WHERE 1=1
AND type ='P'
AND number<6
) D
LEFT OUTER JOIN
--產生4天[營業日]匯率資料
(VALUES('20180111', 29.604)
, ('20180112', 29.631)
, ('20180115', 29.536)
, ('20180116', 29.550)
) R(DT, Fx_Rate)
ON D.DT = R.DT
ORDER BY D.DT
以下將說明類似的二種方法，方法-1為常用之寫法並附上LAST_VALUE函數以進行比較，方法-2則在模擬ORACLE參數用法，用分析函數設計想法來推導。
--方法-1:
SELECT C.*
, COALESCE(C.Fx_Rate, P.Fx_Rate) Fx_Rate --當日有值(非NULL)優先使用，否則用子查詢(前一營業日)
, LAST_VALUE(C.Fx_Rate )
OVER (ORDER BY C.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "RESPECT"
FROM #Forex C
OUTER APPLY
(
SELECT TOP 1 * -- 2. TOP 1: First_Value
FROM #Forex P
WHERE 1=1
AND C.DT > P.DT -- 1.主查詢使用COALESC且判斷並優先使用當日，因此>或>=結果相同
AND P.Fx_Rate IS NOT NULL-- 2.剔除NULL值, 即可找到
ORDER BY P.DT DESC
) P
--方法-2:
WITH Store AS
(
SELECT F.*
, ROW_NUMBER() OVER (ORDER BY DT) SEQ
FROM #Forex F
)
SELECT C.*
, COALESCE(C.Fx_Rate, P.Fx_Rate) Fx_Rate--如當日有值(非NULL)優先使用，否則採子查詢(前一營業日)
FROM Store C
OUTER APPLY
(
SELECT TOP 1 * -- 3.TOP 1: First_Value
FROM Store P
WHERE 1=1
AND P.SEQ BETWEEN C.SEQ-1-- 1.模擬WINDOW: 1 PRECEDING(僅用1天呈現效果,實際則10筆較適合)
AND C.SEQ -- 1.模擬WINDOW: CURRENET ROW
AND P.Fx_Rate IS NOT NULL-- 2.剔除NULL值, 即可找到
ORDER BY P.SEQ DESC -- 3.配合TOP
) P
此方法使用ROW_NUMBER次序函數建立對應序號，再以視窗框架(WINDOW)取得前一筆（1 PRECEDING）至目前資料列(CURRENET ROW)，其次再將NULL值剔除（Filter out），最後以序號順序取得第1第資料（非NULL），為呈現視窗框架(WINDOW)效果，而於範例中僅使用前1筆資料，以休假日大部分都在10天內，因此可設定10至15筆較為合適。
方法-2中設定起始點為前1筆，此也展示LEAD/LAG函數於本範例不適合使用，由於往前遞推天數不確定，難以達成。
沒有留言:
張貼留言