- 浏览: 137257 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
djb_daydayup：
Map<String, String> map={ ...
JDK1.5/1.6/1.7之新特性总结 -
txin0814：
借鉴。。。 我还得在此上加上英语学习
我的Java学习计划 -
hongmeikaile：
精辟
程序员35岁不是槛 -
king130520：
很好~~借鉴~
我的Java学习计划 -
halk：
工具类的实现目标是：具有很强的通用性；能有较多的重载方法；尽量 ...
编写工具类的总结
转载自：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 1037export ORACLE_SID=payment sqlp ... -
脏读-不可重复读-幻影读
2012-03-07 17:55 2267脏读，不可重复读，幻 ... -
Oracle自治事务介绍
2011-11-07 10:41 1052这是一个非常实用的功能，特别用在记录错误日志同时要回滚主事 ... -
oracle批量插入随机数的方法
2011-10-26 14:16 2316创建表dimension_tab DROP TABLE ... -
ORACLE10G清除死锁进程
2011-09-15 10:10 891select Distinct 'alter system k ... -
杀掉JOB
2011-04-25 16:04 1463今天，一同事的job不能直接remove掉。 考虑到是 ... -
Oracle同义词
2010-07-23 12:24 1599Oracle数据库 中 提供了 ... -
oracle连接一直等待也不报错的问题
2010-04-21 14:51 1753今天测试服务器上的程序访问数据库出现了异常，取不到 ... -
Oracle Created (Default) Database Users
2010-03-31 12:42 950Oracle Created (Default) Databa ... -
oracle行列转换总结(转载)
2010-03-31 12:26 1027oracle行列转换总结 最近论坛很多人提的问题都与行列转换 ... -
ORA-12500：TNS：监听程序无法启动专用服务器进程
2009-08-12 11:02 11292最近老遇到:ORA-12500：TNS：监听程序无法启动专用服 ... -
初学者oracle里常用命令详细讲解
2009-08-03 10:47 970第一章：日志管理 1.forcing log switch ... -
oracle重启
2009-08-03 10:44 5703有时候数据死了，或者没有监听，不必重启机子，重启一下数据库服务 ... -
在shell中调用执行存储过程
2009-04-27 13:50 5300基本的shell文件结构如下： sqlplus user/p ... -
logminer的使用和安装
2009-01-16 13:35 1261LogMiner安装和使用的一些心得 ... -
查看最近的日志记录
2008-05-08 15:09 811日志表查看最近的纪录 我想选时间最近的5条记录： select ...
相关推荐
Oracle中的SQL MODEL子句是一种强大的工具，用于在数据库内执行复杂的行间计算，它允许用户在不依赖外部应用程序或电子表格的情况下对数据进行高级处理。MODEL子句的主要优点在于其性能提升、可伸缩性和可管理性，...
在Oracle数据库中，Model子句是一种强大的SQL工具，用于处理复杂的建模和计算任务，尤其在数据建模、预测分析以及复杂的数据转换方面表现出色。本章“《Pro Oracle SQL》CHAPTER 9 The Model Clause”重点讲解了...
在Oracle SQL中，Model子句是一种高级的行处理工具，用于进行复杂的行计算和模拟迭代过程，比如解决最优化问题或者模拟时间序列分析。本节内容主要涉及Model子句执行时的逻辑流程和数据处理顺序。 Model子句允许...
《Pro Oracle SQL》一书的第9章深入探讨了Oracle数据库中的"Model"子句，这一章节重点关注如何使用Model子句来更新数据行。在Oracle SQL中，Model子句是一种强大的功能，允许进行复杂的行处理和模拟迭代计算，通常...
在Oracle数据库中，Model子句是一种强大的功能，它允许数据建模和复杂的计算，尤其适用于解决多步骤计算问题，如模拟、预测和序列生成等。本章节深入探讨了如何利用Model子句来提升SQL查询的执行效率。 Model子句的...
在Oracle数据库10g中，`MODEL`子句提供了一种强大而灵活的方法来执行行间计算，它允许用户根据查询结果定义多维数组，并将规则应用于这些数组以计算新值。与传统方法（如在应用程序或PC电子表格中进行计算）相比，...
《Pro Oracle SQL》是Oracle数据库查询的一本权威指南，其中第9章主要讲解了如何使用Model子句进行行间引用，这是一个高级SQL特性，用于处理复杂的行与行之间的计算和逻辑操作。9.2章节专注于Inter-Row Referencing...
第9章 Model子句 225 第10章 子查询因子化 254 第11章 半联结和反联结 292 第12章 索引 334 第13章 SELECT以外的内容 360 第14章 事务处理 386 第15章 测试与质量保证 415 第16章 计划稳定性与控制 443
9.10 使用Model子句进行性能调优 243 9.10.1 执行计划 243 9.10.2 谓语前推 246 9.10.3 物化视图 247 9.10.4 并行 249 9.10.5 Model子句执行中的分区 250 9.10.6 索引 251 9.11 子查询因子化 252 9.12 小结...
本文将深入探讨Oracle中实现行列转换的几种方法，包括使用`UNION ALL`、`MODEL`子句以及集合类型(`collections`)，并提供具体的示例来帮助理解。 ### 1. 使用`UNION ALL`进行行列转换 `UNION ALL`是一种简单直接的...
这种操作可以使用 UNION ALL 语句、MODEL 子句和 COLLECTION 语句来实现。 1. 使用 UNION ALL 语句 UNION ALL 语句可以将多个 SELECT 语句的结果合并成一个结果集。 示例代码： ```sql SELECT * FROM T_COL_ROW;...
18. MODEL语句提供了对表中数据进行基于规则的计算和更新的能力。 19. 10G闪回查询功能允许用户查看或回滚到之前的数据库版本，是数据恢复的重要工具。 20. 行列转换是一个常见的数据处理问题，通过特定的函数和子...
《Pro Oracle SQL》一书中的第9章详细探讨了Oracle SQL中的“模型”子句，这一章的主题是“9.7 迭代”。在Oracle数据库中，模型子句是一种强大的功能，它允许用户进行复杂的多步骤计算，尤其适用于处理数组、矩阵...
以上介绍了几种常用的行列转换方法，包括使用`UNION ALL`、`MODEL`子句以及`AGGREGATE FUNCTION`等方式。这些方法各有优缺点，在实际应用中可以根据需求选择最合适的方式。需要注意的是，不同Oracle版本支持的功能...
他们共同探讨了Oracle SQL语言的强大特性，包括分析函数、MODEL子句以及高级分组语法，这些都是在报告和商业智能应用中创建高效查询所不可或缺的。 为了掌握Oracle SQL的精髓，读者需要采取一种三管齐下的方法。...
《Pro Oracle SQL》一书中的第9章详细探讨了Oracle SQL中的“模型”子句，这一章重点关注了位置引用和符号引用的概念。在Oracle数据库中，模型子句是一种强大的工具，用于处理复杂的行列操作，例如数据建模、数据...
这部分介绍了如何在 EBS 11i 中使用 Model-View-Controller (MVC) 设计模式来开发 Forms，这种模式有助于分离数据、用户界面和控制逻辑，从而提高代码的可维护性和扩展性。 #### 十一、EBS 预警功能自定义 **11.1 ...