程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle--model用法，oraclemodel

oracle--model用法，oraclemodel

編輯：Oracle教程

oracle--model用法，oraclemodel

一. 什麼是 model語句

model 語句是 Oracle10g 的新功能，
SQL中的MODEL從句是Oracle 10g數據庫為企業智能領域引入的一個重要的新功能。這個從句對喜歡從Oracle中區出來，再放入電子表格進行分析的會計人員有很大幫助。SQL中的MODEL從句的目的就是讓SQL語句擁有從普通的SELECT結果中創建多維數組的能力，然後再在這個SQL電子表格上進行行間或數組的計算。
MODEL從句通過講一個查詢中的各列映射為三組而定義一個多位數組，這三個組分別是分區(partitions)、維度(dimensions)和計量(measures)
對 SQL 的結果集進行處理。執行順序是位於 Having 之後。

二. model 的使用場景

model 典型使用場景。
1. 合計行追加
2. 行列變換
3. 使用當前行的前後行
4. RegExp_Replace 函數的循環執行

三. 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]

例子說明：

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');

輸出結果：
0924 1

解釋參數：

model： model 語句的關鍵字，必須。
partition by : 按照××分組
dimension by n dimension：維度的意思，可以理解為數組的索引，必須。
measures：指定作為數組的列
rules：對數組進行各種操作的描述

四. model return updated rows含義
說明：rules 的缺省行為是存在就更新，不存在則追加;使用 model return updated rows 的話，被 rules 更新或者插入的行才顯示，沒有更
新過的行不再作為 SQL 的結果

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

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

五. 舉例說明：

例如：數據如下

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);

1.預測2015的收入是前兩年的總和。

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']);

2.預測2015年的營業收入數據。假設2015年1001公司營業收入是其前兩年的總和，1002公司2015年的數據比其上年收入2倍。那麼MODEL從句如下

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']);

3.用BETWEEN和AND返回特定范圍內的數據單元

 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']);

4. 使用for in語句來返回特定范圍內

 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_year 是數值類型，還可以用 for year from 2013 to 2014 increment 1 的語法，如果是其他類型，
還可以用在 in 子句帶子查詢的辦法，比如 for p_year in (select year from B)

5.用ANY和IS ANY訪問所有的數據單元可以用ANY和IS ANY謂詞訪問數組中所有的數據單元。ANY和位置標記合用，IS ANY和符號標記合用
例如預測2017年的營業收入數據；是所有年份的總和；

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]);--或者any改為 p_year is any都可以。

6.用CURRENTV()獲取某個維度的當前值
改寫事例2

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']);

7.至於為什麼要用到它；有什麼好處？請看下面一例
前不久我看到群裡一個問題；找出p_color為'red'的p_value比為'blue'多的p_product
這個題目不難；你用decode；或者case when，把blue的p_value判斷為負；再利用組函數sum即可。
若用model來寫；你看有沒有高大上的感覺。

select p_roduct,p_value
 (select * from tb 
 model return updated rows
 partition by (p_product)
 dimension by (p_color)
 measures (p_value)
 rules (p_value['red-bule']=p_value['red']-p_value['blue'])) 
 from p_vlaue > 0;

總結：功能很強大，語法太復雜了，

資料 --<pro oracle sql>第九章。