- 浏览: 134282 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
djb_daydayup：
Map<String, String> map={ ...
JDK1.5/1.6/1.7之新特性总结 -
txin0814：
借鉴。。。 我还得在此上加上英语学习
我的Java学习计划 -
hongmeikaile：
精辟
程序员35岁不是槛 -
king130520：
很好~~借鉴~
我的Java学习计划 -
halk：
工具类的实现目标是：具有很强的通用性；能有较多的重载方法；尽量 ...
编写工具类的总结
oracle MODEL子句
- 博客分类：
- 数据库
转载自：http://blog.csdn.net/wh62592855/article/details/4783021
ORACLE 10G中新增的MODEL子句可以用来进行行间计算。MODEL子句允许像访问数组中元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。
1、MODEL子句示例
下面这个查询获取2003年内由员工#21完成的产品类型为#1和#2的销量，并根据2003年的销售数据预测出2004年1月、2月、3月的销量。
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=sales_amount[1,2003],
Sales_amount[2,2004]=sales_amount[2,2003] + sales_amount[3,2003],
Sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2)
)
Order by prd_type_id,year,month;
现在小分析一下上面这个查询：
partition by (prd_type_id)指定结果是根据prd_type_id分区的。
dimension by (month,year)定义数组的维度是month和year。这就意味着必须提供月份和年份才能访问数组中的单元。
measures (amount sales_amount)表明数组中的每个单元包含一个数量，同时表明数组名为sales_amount。
MEASURES之后的三行命令分别预测2004年1月、2月、3月的销量。
Order by prd_type_id,year,month仅仅是设置整个查询返回结果的顺序。
上面这个查询的输出结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 10034.84
1 2004 2 35282.48
1 2004 3 25172.29
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1034.84
2 2004 2 3582.48
2 2004 3 2547.29
30 rows selected.
2、用位置标记和符号标记访问数据单元
前面的例子已经介绍了如何使用位置标记来访问数组中的某个单元。还可以使用符号标记显式指定维度的含义。例如，sales_amount[month=1,year=2004]。下面这个查询用符号标记重写了前面的查询。
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[month=1,year=2004]=sales_amount[month=1, year=2003],
Sales_amount[month=2, year=2004]=sales_amount[month=2, year=2003] + sales_amount[month=3, year=2003],
Sales_amount[month=3, year=2004]=ROUND(sales_amount[month=3, year=2003]*1.25,2)
)
Order by prd_type_id,year,month;
使用位置标记或符号标记之间有一个区别需要了解，即它们处理维度中空值的方式不同。例如，sales_amount[null,2003]返回月份为空值、年份为2003的销量，而sales_amount[month=null,year=2004]则不会访问任何有效的数据单元，因为null=null的返回值总是false。
3、用BETWEEN和AND返回特定范围内的数据单元
BETWEEN和AND关键字可用于访问一段范围内的数据单元。例如，下面这个表达式将2004年1月的销量设置为2003年1月至3月销量的平均值取整：
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)
)
Order by prd_type_id,year,month;
结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 15105.77
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1539.11
26 rows selected.
4、用ANY和IS ANY访问所有的数据单元
可以用ANY和IS ANY谓词访问数组中所有的数据单元。ANY和位置标记合用，IS ANY和符号标记合用。例如，下面这个表达式将2004年1月的销量设置为所有年份月份的销量之和取整：
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)
)
Order by prd_type_id,year,month;
结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 197916.96
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 20426.96
26 rows selected.
5、用CURRENTV()获取某个维度的当前值
CURRENTV()函数用于获得某个维度的当前值。例如，下面的表达式将2004年第一个月的销量设置为2003年同月销量的1.25倍。注意此处用CURRENTV()获得当前月份，其值为1
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
运行结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 12543.55
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1293.55
26 rows selected.
6、用FOR循环访问数据单元
可以通过FOR循环访问数据单元。例如，下面这个表达式将2004年前三个月的销量设置为2003年相应月份销量的1.25倍。注意其中使用了FOR循环，还通过INCREMENT关键字定义每一次循环迭代中month的增量：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
下面这个和查询语句展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
运行结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
1 2004 1 12543.55
1 2004 2 18930.81
1 2004 3 25172.29
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
2 2004 1 1293.55
2 2004 2 1930.81
2 2004 3 2547.29
30 rows selected.
7、处理空值和缺失值
1）使用IS PRESENT
当数据单元指定的记录在MODEL子句执行之前存在，则IS PRESENT返回TRUE。例如：
Sales_amount[CURRENTV(),2003] IS PRESENT
如果Sales_amount[CURRENTV(),2003]存在，则返回TRUE。
下面的表达式将2004年前三个月的销量设置为2003年同期销量的1.25倍：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
)
Order by prd_type_id,year,month;
2）使用PRESENTV()
如果cell引用的记录在MODEL子句执行以前就存在，那么PRESENTV(cell,expr1,expr2)返回表达式expr1。如果这条记录不存在，则返回表达式expr2。例如：
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在，上面的表达式返回取整后的销量；否则，返回0.下面这个查询展示了上述表达式的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;
3）使用PRESENTNNV()
如果cell引用的单元在MODEL子句执行之前已经存在，并且该单元的值不为空，则PRESENTNNV(cell,expr1,expr2)返回表达式expr1。如果记录不存在，或单元值为空值，则返回表达式expr2。例如：
PRESENTNNV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在且为非空值，那么上面的表达式将返回取整后的销量；否则返回0。
4）使用IGNORE NAV和KEEP NAV
IGNORE NAV的返回值如下：
空值或缺失数字值时返回0。
空值或缺失字符串值时返回空字符串。
空值或缺失日期值时返回01-JAN-2000。
其他所有数据库类型时返回空值。
KEEP NAV对空值或缺失数字值返回空值。注意默认条件下使用KEEP NAV。
下面这个查询展示了IGNORE NAV的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
8、更新已有的单元
默认情况下，如果表达式左端的引用单元存在，则更新该单元。如果该单元不存在，就在数组中创建一条新的记录。可以用RULES UPDATE改变这种默认的行为，指出在单元不存在的情况下不创建新纪录。
下面这个查询展示了RULES UPDATE的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
RULES UPDATE
(
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
)
Order by prd_type_id,year,month;
运行结果如下：
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 1 10034.84
1 2003 2 15144.65
1 2003 3 20137.83
1 2003 4 25057.45
1 2003 5 17214.56
1 2003 6 15564.64
1 2003 7 12654.84
1 2003 8 17434.82
1 2003 9 19854.57
1 2003 10 21754.19
1 2003 11 13029.73
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
1 2003 12 10034.84
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 10 2754.19
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 11 1329.73
2 2003 12 1034.84
24 rows selected.
可以看到，虽然2004年的单元不存在，可是同时指定了RULES UPDATE，所以不会在数组中为2004年创建新纪录，因此这条查询语句不会返回2004年的记录。
发表评论
-
awrrpt
2012-03-08 15:21 983export ORACLE_SID=payment sqlp ... -
脏读-不可重复读-幻影读
2012-03-07 17:55 2231脏读，不可重复读，幻 ... -
Oracle自治事务介绍
2011-11-07 10:41 1024这是一个非常实用的功能，特别用在记录错误日志同时要回滚主事 ... -
oracle批量插入随机数的方法
2011-10-26 14:16 2231创建表dimension_tab DROP TABLE ... -
ORACLE10G清除死锁进程
2011-09-15 10:10 858select Distinct 'alter system k ... -
杀掉JOB
2011-04-25 16:04 1428今天，一同事的job不能直接remove掉。 考虑到是 ... -
Oracle同义词
2010-07-23 12:24 1564Oracle数据库 中 提供了 ... -
oracle连接一直等待也不报错的问题
2010-04-21 14:51 1722今天测试服务器上的程序访问数据库出现了异常，取不到 ... -
Oracle Created (Default) Database Users
2010-03-31 12:42 917Oracle Created (Default) Databa ... -
oracle行列转换总结(转载)
2010-03-31 12:26 987oracle行列转换总结 最近论坛很多人提的问题都与行列转换 ... -
ORA-12500：TNS：监听程序无法启动专用服务器进程
2009-08-12 11:02 11244最近老遇到:ORA-12500：TNS：监听程序无法启动专用服 ... -
初学者oracle里常用命令详细讲解
2009-08-03 10:47 949第一章：日志管理 1.forcing log switch ... -
oracle重启
2009-08-03 10:44 5666有时候数据死了，或者没有监听，不必重启机子，重启一下数据库服务 ... -
在shell中调用执行存储过程
2009-04-27 13:50 5250基本的shell文件结构如下： sqlplus user/p ... -
logminer的使用和安装
2009-01-16 13:35 1227LogMiner安装和使用的一些心得 ... -
查看最近的日志记录
2008-05-08 15:09 781日志表查看最近的纪录 我想选时间最近的5条记录： select ...
相关推荐
利用 SQL MODEL 子句，您可以根据查询结果定义多维数组，然后将规则应用于该数组以计算新值。这些规则可以是复杂的相互依赖的计算。与外部解决方案相比，通过将高级计算集 成到数据库中，可以大幅度提升性能、可伸缩...
主要介绍了oracle RETURNING 子句使用方法,需要的朋友可以参考下
一、生成测试数据用以下SQL创建超女基本信息表（T_GIRL），插入一些测试数据 二、where子句的语法select 字段名1,字段名2,字段名n from
第9章 Model子句 225 第10章 子查询因子化 254 第11章 半联结和反联结 292 第12章 索引 334 第13章 SELECT以外的内容 360 第14章 事务处理 386 第15章 测试与质量保证 415 第16章 计划稳定性与控制 443
Oracle start with.connect by prior子句实现递归查询
Oracle使用技巧之case子句的用途.docx
主要介绍了在oracle中where 子句和having子句中的区别,本文通过sql语句给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下
Oracle Exception汇总(自定义Oracle异常) 使用方法举例: Exception When no_data_found then Dbms_output.put_line(‘no_data_found’); ACCESS_INTO_NULL 为对象赋值前必需初始化对象。对应ORA-06530错误。 CASE...
作者通过总结各自多年的软件开发和教学培训经验，与大家分享了掌握Oracle SQL所独有的丰富功能的技巧所在，内容涵盖SQL执行、联结、集合、分析函数、子句、事务处理等多个方面。读者可以学习到以下几个方面的技巧：...
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表...
《Oracle11g从入门到精通》面向数据库管理人员和数据库开发人员，从实际角度出发，系统地介绍了数据库和Oracle的相关概念和原理、Oracle的数据库管理（如安装与启动，用户权限、备份与恢复等），以及Oracle的应用...
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表...
子句集消解实验
比如当一个where子句中的一列有索引时去走索引。 COST (基于成本) 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. CHOOSE (选择性) 如果table已经被analyze过, 优化器...
对oracle11g中常出现的问题进行总结