[ORACLE] Model指令應用簡介

MODEL子句是10g起所提供之新功能，提供類似EXCEL中之功能，與傳統的一般語法之最大差別，在於提供跨列（Row）引用、多單元格引用以及單元格聚合等，以下以匯率市場為例，當股滙市休市時，由於無法當時取得當日（即時）匯率，則以前一營日之匯率替代，如下圖，由於03/18（六）及03/19（日）為例假休市，因此將以前一營業日03/17（五）匯率替代此兩日之匯率。

跨列(Row)參照類型問題，如使用EXCEL則相當容易且方便即可達成，本範例，可於新欄位（Column）使用IF函數，以EXCEL的E3欄位運式為例，首先判斷匯率欄位是否為空值（D3<>""），若不為空值則取直接取用（D3）；反之，則取得同欄位中前一筆資料（E2），判斷運算式如下。

=IF(D3<>"", D3, E2)

SELECT *

FROM

    (

    SELECT 'USD' CRNCY, DATE'2017-03-17' CDATE, 30.5 Rate

    FROM DUAL     

    UNION ALL

    SELECT 'USD' CRNCY,  DATE'2017-03-18' DT, NULL

    FROM DUAL     

    UNION ALL

    SELECT 'USD' CRNCY,  DATE'2017-03-19' DT, NULL

    FROM DUAL 

    UNION ALL

    SELECT 'USD' CRNCY,  DATE'2017-03-20' DT, 30.2

    FROM DUAL

     )

MODEL

--(1).以此資料進行分群，幣別

PARTITION BY (CRNCY) 

--(2).創造虛擬列(ROW)順序流水號

DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY CRNCY ORDER BY CDATE) SEQ)

--(3)輸出欄位(RATE, CDATE)及新增之匯率運算欄位（RATE_Adj，預設為RATE欄位值）

MEASURES (RATE, CDATE, RATE RATE_Adj)

RULES

(

  RATE_Adj[ANY] = COALESCE(RATE[CV()], RATE_Adj[CV()-1], 0)  

--(3).運算公式，當同一筆ROW的RATE為空值時，則採用前一筆運算欄位值替代

)