- 浏览: 90919 次
- 性别:
- 来自: 广州
-
最新评论
-
hywill：
spool e:\test\pmd_droptable.sql ...
powerdesigner 12.5 错误 -
yuanzhifei89：
很赞同，和周围朋友共同语言越来越少中...
写给程序员朋友们---你真的还想做程序员吗 -
cq.ronadlo：
不好用 依然不好用
【转】设定tomcat的默认字符集
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年的记录。
发表评论
-
dbms_sql的使用
2010-10-26 10:19 1348dbms_sql的使用 http://space ... -
PLSQL中显示Cursor、隐示Cursor、动态Ref Cursor区别
2010-09-14 16:01 1029PLSQL中显示Cursor、隐示Cursor、动态Ref C ... -
Oracle的model用法简介
2010-09-13 16:55 1337Model语句是Oracle 10g的新功能之一。 ... -
imp/exp impdp/expdp
2010-09-03 13:21 2402EXP常规模式、EXP直接路径模式和EXPDP三种方式导出 ... -
Oracle数据字典概念 收藏
2010-09-02 15:17 1713Oracle数据字典概念 收藏 ... -
[转]OWB
2010-05-31 15:02 1547OWB 前提： 按照先后顺序分别安装oracl ... -
触发器
2010-04-07 16:43 694create or replace trigger TRG_t ... -
[cognos]Cognos入门
2010-03-23 21:13 1384Cognos BI 提供了报表（reporting）， ... -
windows下局域网内的实时数据收发
2009-09-23 23:17 1596最近做了一个 ... -
oracle.jdbc.driver.OracleDriver
2009-09-22 11:49 3468连接Oracle数据库必须先向java程序注册Oracle J ... -
SQL Server数据库备份和恢复措施
2009-09-07 11:05 1371一、备份数据库 1、打开SQL企业管理器，在控制台根目录中 ...
相关推荐
oracle学习笔记，txt格式，完全是自己总结的，特别详细，有例子，还有练习题以及答案。包括1.基本SQL语句 2.限制和排列数据 3.单行函数 4.多表查询 5.组函数 6.子查询 7.数据操作 8.创建和维护表 9.约束 10.创建视图...
利用 SQL MODEL 子句，您可以根据查询结果定义多维数组，然后将规则应用于该数组以计算新值。这些规则可以是复杂的相互依赖的计算。与外部解决方案相比，通过将高级计算集 成到数据库中，可以大幅度提升性能、可伸缩...
主要介绍了oracle RETURNING 子句使用方法,需要的朋友可以参考下
NULL 博文链接：https://weishaoxiang.iteye.com/blog/2003050
一、生成测试数据用以下SQL创建超女基本信息表（T_GIRL），插入一些测试数据 二、where子句的语法select 字段名1,字段名2,字段名n from
oracle day3 笔记 外连接： 查找每个员工的经理ID select e.first_name "Employee",m.first_name "Manager" from s_emp e,s_emp m where e.manager_id=m.id(+); 一、组函数 group 组 group by 分组子句，...
1.FORALL 用法小结 2.如何使用批挷定提高性能 3.FORALL 如何影响回滚 4.用%BULK_ROWCOUNT 属性计算FORALL迭代影响行数 ,用%BULK_ROWCOUNT 属性计算FORALL...8.SQL优化学习笔记 9.给Oracle存储过程传入数组(这是自己的)
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 第一章 Oracle入门 一、 数据库概述 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，它产生于距今五十年前。简单来说是本身可视...
Oracle start with.connect by prior子句实现递归查询
第9章 Model子句 225 第10章 子查询因子化 254 第11章 半联结和反联结 292 第12章 索引 334 第13章 SELECT以外的内容 360 第14章 事务处理 386 第15章 测试与质量保证 415 第16章 计划稳定性与控制 443
Oracle使用技巧之case子句的用途.docx
主要介绍了在oracle中where 子句和having子句中的区别,本文通过sql语句给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下
4. Select 中 避免使用*, oracle需要转换成表的列，通过查询数据字典获得，耗费时间). 5. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序尽可能的让字段顺序与索引顺序...
Oracle Exception汇总(自定义Oracle异常) 使用方法举例: Exception When no_data_found then Dbms_output.put_line(‘no_data_found’); ACCESS_INTO_NULL 为对象赋值前必需初始化对象。对应ORA-06530错误。 CASE...
9 储存结构与关系 目标 9-2 概述 9-3 段的类型 9-4 储存子句的优先次序 9-7 片的分配和重新分配 9-8 使用和释放片 9-9 数据块 9-10 数据块的内容 9-11 块空间的利用的参数 9-12 v 自动段空间管理 9-14 设置自动...