2018年2月4日 星期日

FIRST_VALUE及LAST_VALUE函數IGNORE NULLS關鍵字用途-取得最後結算匯率為例

以下將以休假日引用前一營日之結算匯率(價格)為例,說明ORACLEFIRST_VALUELAST_VALUE函數中IGNORE NULLS關鍵字用途及MSSQL對應做法。如下圖中,由於2018-01-132018-01-14為星期六日例假日匯市休市,因此將取得前一營業日(2018-01-12)為結算參考價,如下圖:

ORACLEFIRST_VALUELAST_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 [windowing_clause]]
)

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天內,因此可設定1015筆較為合適。


方法-2中設定起始點為前1筆,此也展示LEAD/LAG函數於本範例不適合使用,由於往前遞推天數不確定,難以達成。

沒有留言:

張貼留言