Oracle MODEL

tags: oracle MODEL

語法

MODEL [RETURN [UPDATED | ALL] ROWS]
[reference models]
[PARTITION BY (<cols>)]
DIMENSION BY (<cols>)
MEASURES (<cols>) [IGNORE NAV] | [KEEP NAV]
[RULES
[UPSERT | UPDATE]
[AUTOMATIC ORDER | SEQUENTIAL ORDER]

語法說明

MODEL:是一個宣告的關鍵字
PARTITION BY:以XX欄位為分組
DIMENSION BY:MODEL維度設定，看成INDEX，可以是複合PK
MEASURES:指定資料欄位，可自行定義
RULES:規則，你怎麼去操作它，如any,cv…

範例

範例_MODEL

範例來源

CREATE TABLE A AS
SELECT 'lottu' AS vname,
1 AS vals FROM dual;
SELECT vname,vals FROM A
MODEL
--partition by ()可以忽略
DIMENSION BY(vals) 
MEASURES(vname)    
RULES (vname[1]='0924');

結果如下：

VNAME VALS
0924     1

執行結果會發現，vname的部份被指定為0924，因為只有一筆資料，所以index(VALS)=1的部份即為該資料，而且被指定為0924!
如果調整一下RULES!

SELECT vname,vals FROM A
MODEL
--partition by ()可以忽略
DIMENSION BY(vals) 
MEASURES(vname)    
RULES (vname[0]='0924');

結果如下：

    VNAME   VALS
1   lottu 1
2   0924 0

這時候會發現，多了一筆資料了，並且VALS為0!

我們再插入一筆資料，(‘LI’,2)

INSERT INTO A VALUES ('LI',2);
COMMIT;

接著執行!

SELECT vname,vals FROM A
MODEL 
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[2]='0924');

結果如下：

    VNAME   VALS
1   lottu 1
2   0924 2

跟剛才一樣，RULES將LI調整為0924了!
當然也可以跟剛才不一樣，用不存在的INDEX去做設置。

SELECT vname,vals FROM A
MODEL 
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[5]='0924',vname[0]='99');

這次我們加了兩筆記錄進去。

範例_MODEL RETURN UPDATED ROWS_1

MODEL後面如果加上RETURN UPDATED ROWS即代表，有被RULES更新或者插入的資料才會顯示。

SELECT vname,vals FROM A
MODEL RETURN UPDATED ROWS
DIMENSION BY(vals)
MEASURES(vname)
RULES (vname[0]='0924');

結果如下：

    VNAME   VALS
1   0924 0

我們有兩筆資料，按上面的練習應該是會出現三筆才對!
但這次的SELECT卻只出現一筆，這就是加入RETURN UPDATED ROWS的用途!

範例_MODEL RETURN UPDATED ROWS_2_加總

用另一個例子來說明!
建立另一個新的table，並加入數據!
我們建立了2011年到2014年的資料，希望預測2015年!

CREATE TABLE B(p_id NUMBER,p_year Varchar2(5),p_val NUMBER);
INSERT INTO B VALUES (1001,'2011',25); 
INSERT INTO B VALUES (1001,'2012',35); 
INSERT INTO B VALUES (1001,'2013',65); 
INSERT INTO B VALUES (1001,'2014',95); 
INSERT INTO B VALUES (1002,'2011',25); 
INSERT INTO B VALUES (1002,'2012',55); 
INSERT INTO B VALUES (1002,'2013',75); 
INSERT INTO B VALUES (1002,'2014',95);

接著先以不加入RETURN的方式呈現比較清楚整個資料結構。

SELECT * FROM B
MODEL 
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2015']=p_val['2014']+p_val['2013']);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1001 2011 25
2 1001 2012 35
3 1001 2013 65
4 1001 2014 95
5 1002 2011 25
6 1002 2012 55
7 1002 2013 75
8 1002 2014 95
9 1001 2015 160
10 1002 2015 170

我們以P_ID為分組依據，以P_YEAR為維度，設定P_VAL為呈現的數據，然後設置2015年的值=2013年加上2014年!
接著我們加入RETURN UPDATED ROWS

SELECT * FROM B
MODEL RETURN UPDATED ROWS
PARTITION BY (p_id)
DIMENSION BY (p_year)
MEASURES (p_val)
RULES (p_val['2015']=p_val['2014']+p_val['2013']);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1001 2015 160
2 1002 2015 170     

只回傳異動的資料，所以只會有2015年的資料呈現。

範例_MODEL RETURN UPDATED ROWS_3_個別處理

資料集的部份一樣是剛才建置的TABLE B
2015年的1001是前兩年的總合，而1002是上年度的2倍。

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 DIMENSION BY (p_id,p_year)
 MEASURES (p_val)
 RULES (p_val[1001,'2015']=p_val[1001,'2013']+p_val[1001,'2014'],
        P_val[1002,'2015']=2 * p_val[1002,'2014']);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1002 2015 190
2 1001 2015 160

這次不設置PARTITION
將P_ID加入維度內(DIMENSION)
並且搜尋數據組一樣為P_VAL
就可以個別的處理兩個P_ID的2015年的銷售計算了。

範例_MODEL RETURN UPDATED ROWS_4_RULES BETWEEN AND

語法：SUM(MEASURES)[DIMENSION BETWEEN CON1 AND CON2]

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 PARTITION BY (p_id)
 DIMENSION BY (p_year)
 MEASURES (p_val)
 RULES (p_val['2015']=sum(p_val)[p_year BETWEEN '2013' AND '2014']);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1001 2015 160
2 1002 2015 170

透過SUM(MEASURES)[DIMENSION BETWEEN CON1 AND CON2]
可以像在操作標準SQL語法一樣設置條件。
此例加總了2013年至2014年的P_VAL

範例_MODEL RETURN UPDATED ROWS_5_RULES FOR

語法：SUM(MEASURES)[FOR DIMENSION IN (CONDITION)]

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 PARTITION BY (p_id)
 DIMENSION BY (p_year)
 MEASURES (p_val)
 RULES (p_val['2015']=sum(p_val)[for p_year in ('2014','2013')]);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1001 2015 160
2 1002 2015 170

這個案例我們用了FOR + IN的語法
FOR DIMENSION IN 條件
所以一樣求得了2013年與2014年的加總
如果P_YEAR本身是數值的話，可以利用表達式

FOR DIMENSION FROM INT1 TO INT2 INCREMENT N

此例來說，如果P_YEAR為數值，那我們的表達式可以以這樣子來表示

for year from 2013 to 2014 increment 1 

代表從2013年到2014年，迭代部份一次增加1

最後提到

FOR DIMENSION IN (SELECT 子句)

在IN的部份是可以利用SELECT子句來處理

範例_MODEL RETURN UPDATED ROWS_6_RULES ANY, ISNAY

ANY>位置標記使用
IS ANY>符號標記使用

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 PARTITION BY (p_id)
 DIMENSION BY (p_year)
 MEASURES (p_val)
 RULES (p_val['2017']=SUM(p_val)[ANY]);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1001 2017 220
2 1002 2017 250

我們假設2017年的預測為前面幾年的部份，這時候可以利用ANY表達式!

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 PARTITION BY (p_id)
 DIMENSION BY (p_year)
 MEASURES (p_val)
 RULES (p_val['2017']=SUM(p_val)[P_YEAR IS ANY]);

也可以得到一樣的結果

範例_MODEL RETURN UPDATED ROWS_7_RULES CURRENTV()

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 DIMENSION BY (p_id,p_year)
 MEASURES (p_val)
 RULES (p_val[1001,'2015']=p_val[currentv(),'2013']+p_val[currentv(),'2014'],
 P_val[1002,'2015']=2 * p_val[currentv(),'2014']);

結果如下：

        P_ID   P_YEAR  P_VAL
1 1002 2015 190
2 1001 2015 160

CURRENTV主要用來取得某個DIMENSION目前的值!
對比上面的範例

SELECT * FROM B
 MODEL RETURN UPDATED ROWS
 DIMENSION BY (p_id,p_year)
 MEASURES (p_val)
 RULES (p_val[1001,'2015']=p_val[1001,'2013']+p_val[1001,'2014'],
        P_val[1002,'2015']=2 * p_val[1002,'2014']);