# Oracle MODEL
###### tags: `oracle` `MODEL`
## 語法
```sql=
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
[範例來源](http://www.mincoder.com/article/1724.shtml)
```sql=
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!
```sql=
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)
```sql=
INSERT INTO A VALUES ('LI',2);
COMMIT;
```
接著執行!
```sql=
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去做設置。
```sql=
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更新或者插入的資料才會顯示**。
```sql=
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年!
```sql=
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的方式呈現比較清楚整個資料結構。
```sql=
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
```sql=
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倍。
```sql=
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
:::info
語法：SUM(MEASURES)[DIMENSION BETWEEN CON1 AND CON2]
:::
```sql=
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
:::info
語法：SUM(MEASURES)[FOR DIMENSION IN (CONDITION)]
:::
```sql=
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本身是數值的話，可以利用表達式
:::info
FOR DIMENSION FROM INT1 TO INT2 INCREMENT N
:::
此例來說，如果P_YEAR為數值，那我們的表達式可以以這樣子來表示
```sql=
for year from 2013 to 2014 increment 1
```
代表從2013年到2014年，迭代部份一次增加1
**最後提到**
:::success
FOR DIMENSION IN (SELECT 子句)
:::
在IN的部份是可以利用SELECT子句來處理
### 範例_MODEL RETURN UPDATED ROWS_6_RULES ANY, ISNAY
ANY>位置標記使用
IS ANY>符號標記使用
```sql=
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表達式!
```sql=
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()
```sql=
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目前的值!
對比上面的範例
```sql=
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']);
```
我們透過currentv()去取得了DIMENSION目前各別為1001與1002
### 範例_MODEL RETURN UPDATED ROWS_8_RULES CURRENTV()+FOR
加入月份，結合FOR與CURRENTV()
```sql=
SELECT * FROM B
MODEL RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year,p_month)
MEASURES (p_val)
RULES (
p_val[1001,'2015', FOR P_MONTH FROM 1 TO 4 INCREMENT 1]=p_val[1001,'2013',CURRENTV()]+p_val[1001,'2014',CURRENTV()]
);
```
結果如下：
```
P_ID P_YEAR P_MONTH P_VAL
1 1001 2015 1 160
2 1001 2015 2 160
3 1001 2015 3 160
4 1001 2015 4 160
```
以FOR來迭代月份，再以CURRENTV()來取得目前的迭代月份
### 範例_MODEL RETURN UPDATED ROWS_9_RULES IS PRESENT
IS PRESENT主要用來判斷數據組是否存在!
大概就是IS NULL快差不多道理了，只是如果存在的話就回傳TRUE。
```sql=
SELECT * FROM B
MODEL RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year,p_month)
MEASURES (p_val)
RULES (
p_val[1001,'2015', FOR P_MONTH FROM 1 TO 5 INCREMENT 1]=
CASE WHEN p_val[1001,'2013',CURRENTV()] IS PRESENT THEN
p_val[1001,'2013',CURRENTV()]+p_val[1001,'2014',CURRENTV()]
ELSE
500 END
);
```
結果如下：
```
P_ID P_YEAR P_MONTH P_VAL
1 1001 2015 1 160
2 1001 2015 2 160
3 1001 2015 3 160
4 1001 2015 4 160
5 1001 2015 5 500
```
在句中加入了CASE判斷，如果TRUE，那就以數據加總，否則為500。
我讓迭代1到5，但是資料庫內並沒有5月的資料，所以5月的部份就預設為500了。
### 範例_MODEL RETURN UPDATED ROWS_10_RULES PRESENTNNV()
PRESENTNNV()的用法，就跟SSRS的IIF觀念一樣，
PRESENTNNV(cell,expr1,expr2) 回傳表達式expr1，如果行不存在或單元值為空值，返回表达式 expr2
```sql=
SELECT * FROM B
MODEL RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year,p_month)
MEASURES (p_val)
RULES (
p_val[1001,'2015', FOR P_MONTH FROM 1 TO 5 INCREMENT 1]=
PRESENTNNV(p_val[1001,'2013',CURRENTV()], --CELL
p_val[1001,'2013',CURRENTV()]+p_val[1001,'2014',CURRENTV()], --EXPR1
500) --EXPR2
);
```
結果如下：
```
P_ID P_YEAR P_MONTH P_VAL
1 1001 2015 1 160
2 1001 2015 2 160
3 1001 2015 3 160
4 1001 2015 4 160
5 1001 2015 5 500
```
將上面的CASE子句調整成以PRESENTNNV()來處理，得到一樣的結果。
### 範例_MODEL RETURN UPDATED ROWS_11_RULES IGNORE NAV、KEEP NAV
基本上，ORACLE將空值視為NULL，所以沒有像MSSQL一樣，可以用' '來判斷是否為空值，只能IS NULL。
所以，在MODEL內可以透過IGNORE來改變!
當數值欄位為空，則視為0
當字串欄位為空，則視為空格
當日期欄位為空，則視為01-JAN-2000
KEEP NAV的部份已是預設狀況，不多說明。
:::warning
注意IGNORE NAV的放置點
:::
```sql=
SELECT * FROM B
MODEL IGNORE NAV RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year,p_month)
MEASURES (p_val)
RULES (
p_val[1001,'2015', FOR P_MONTH FROM 1 TO 5 INCREMENT 1]=
p_val[1001,'2013',CURRENTV()]+p_val[1001,'2014',CURRENTV()]
);
```
範例9、範例9、10、11都是用來處理NULL的。
### 範例_MODEL RETURN UPDATED ROWS_7_RULES cv()
cv的應用在於，可以指定以RULES等式左側的目標式的當前維度值在右等式右側操作!
以關聯式資料庫來看待的話，cv可以當做是關聯操作，很抽像!
看例子!
```sql=
SELECT * FROM B
MODEL IGNORE NAV RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year,p_month)
MEASURES (p_val)
RULES (
p_val[1001,'2015', FOR P_MONTH FROM 1 TO 5 INCREMENT 1]=
p_val[1001,CV(p_year),CV(p_month)]
);
```
結果如下：
```
P_ID P_YEAR P_MONTH P_VAL
1 1001 2015 1 0
2 1001 2015 2 0
3 1001 2015 3 0
4 1001 2015 4 0
5 1001 2015 5 0
```
cv的應用，<font color=red >**在對左側的目標式的當前維度值**</font>在右側操作!
此例中，當前的DIMENSION是『<font color=red>2015</font>』
而2015年在我們的範例資料表內是沒有資料的，所以p_val的值皆為0。
接著，我們調整cv
```sql=
SELECT * FROM B
MODEL IGNORE NAV RETURN UPDATED ROWS
DIMENSION BY (p_id,p_year,p_month)
MEASURES (p_val)
RULES (
p_val[1001,'2015', FOR P_MONTH FROM 1 TO 5 INCREMENT 1]=
p_val[1001,CV(p_year)-1,CV(p_month)]
);
```
然後我們先看一下資料表的資料
```sql=
SELECT * FROM b WHERE p_year=2014 AND p_id=1001;
```
結果如下：
```
P_ID P_YEAR P_MONTH P_VAL
1 1001 2014 95 2
2 1001 2014 95 3
3 1001 2014 95 4
4 1001 2014 95 1
```
MODEL查詢結果如下：
```
P_ID P_YEAR P_MONTH P_VAL
1 1001 2015 1 95
2 1001 2015 2 95
3 1001 2015 3 95
4 1001 2015 4 95
5 1001 2015 5 0
```
我在cv(p_year)-1，代表著要以2015-1=2014的年份來做關聯!
就得到上面的結果了，以2014年的p_val來統計。
所以，透過cv()可以操作DIMENSION條件來做等式右側的資料統計
> [time=Thu, Nov 30, 2017 1:40 PM]
> [name=shaoeChen]
Sign in with Wallet
Connect another wallet