[ORACLE] NTH_VALUE分析函數

FIRST_VALUE函數取得視窗框架(WINDOW)中第一筆資料，依執運算式定義計算並傳回結果；LAST_VALUE函數為傳回視窗框架中最後一筆記錄的運算結果。ORACLE於11gR2版本提供取得指定名次的NTH_VALUE函數，為原有FIRST_VALUE、LAST_VALUE之通用函數，可更為直接取得任意名次，語法如下 :

NTH_VALUE (expr, offset)     [FROM FIRST   | FROM LAST]     [IGNORE NULLS | RESPECT NULLS] OVER ( [PARTITION BY ] [ORDER BY                      [] )

NTH_VALUE函數的FROM FIRST及RESPECT NULLS參數為預設值，即預設從第一筆資料開始尋找移位後的記錄，對應記錄如為NULL值仍將傳回。 

以《FIRST_VALUE及LAST_VALUE函數IGNORE NULLS關鍵字用途-取得最後結算匯率》範例資料，改用NTH_VALUE函數重新撰寫，如下：

SELECT D.DT

     , R.Fx_Rate

     , LAST_VALUE(R.DT IGNORE NULLS)

           OVER (ORDER BY D.DT ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)            "LAST_VALUE"

     , NTH_VALUE(R.DT, 1) FROM LAST IGNORE NULLS

           OVER (ORDER BY D.DT ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)            "NTH_VALUE"          

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