2017年4月16日 星期日

[ORACLE] Model指令應用簡介

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

跨列(Row)參照類型問題,如使用EXCEL則相當容易且方便即可達成,本範例,可於新欄位(Column)使用IF函數,以EXCELE3欄位運式為例,首先判斷匯率欄位是否為空值(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).運算公式,當同一筆ROWRATE為空值時,則採用前一筆運算欄位值替代