oracle--model用法

標籤：style   blog   color   os   io   使用   ar   for   資料   

一. 什麼是 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 ASSELECT ‘lottu‘ AS vname,1 AS vals FROM dual;SELECT vname,vals FROM AMODEL--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 AMODEL RETURN UPDATED ROWSDIMENSION 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>第九章。

oracle--model用法