oracle--model用法

一. 什么是 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>第九章。