首页 > 代码库 > oracle--model用法

oracle--model用法

2024-07-20 16:41:28   221人阅读

一. 什么是 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用法


联系
我们