Oracle 11g学习笔记–model子句
oracle 10g中新增的model子句可以用来进行行间计算。model子句允许像访问数组中元素那样访问记录中的某个列,这就提供了诸如电子表格计算之类的计算能力;
先来看一个简单的例子:
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分区的,所谓的分区就是在prd_type_id不等的情况下,一下的定义数组是互相不能访问的;
dimension by(month, year) 定义数组的维数是month, year,这就意味着必须提供月份和年份才能访问数组中的单元;
measures(amount sales_amount)表明数组中的每个单元包含一个数量,同时表明数组名为sales_amount.为了访问sales_amount数组中标示2003年1月的那个单元,可以使用sales_amount[1, 2003],返回指定年月的销量。
执行结果:
图中的所有2004年的数据就是通过model生成出来的;
补充:对于数组的访问方式,还可以显示的访问,如sales_amount[1, 2003]可以显示指定维度sales_amount[month = 1, year = 2003],但是这种方式明显更复杂。必须提一下的是两者的区别:他们处理维度中空值得方式是不同的,例如sales_amount[null, 2003]返回月份为空值,年份为2003的销量,而sales_amount[month = null, year = 2003]则不会返回任何有效数据,因为null=null的返回值总是false;
对于数组角标的维度还可以通过以下扩展方法:
1.between 和 and :
--将2004年1月的销量设置为2003年1月至3月的销量的平均值取整;
sales_amount[1, 2004] = round(sum(sales_amount)[month between 1 and 3, 2003], 2);
2.any 和 isany
--表示将2004年1月的销量设置为所有年份月份的销量值和取整
sales_amount[1, 2004] = round(sum(sales_amount)[any, year is any], 2);
3.currentv()
该函数用于获的某个维度的当前值。
--表示将2004年第一个月的销量设置为2003年同月销量的1.25倍。注意此处用currentv()获取当前月份,其值为1;
sales_amount [1, 2004] = round(sales_amount[currentv(), 2003] *1.25, 2)
4.for循环
该表达式将2004年前三个月的销量设置为2003年相应月份销量的1.25倍。其中increment 1表示了变量每次循环+1
sales_amount[for month from 1 to 3 increment 1, 2004] = round (sales_amount[currentv(), 2003] * 1.25, 2)
5.处理空值和缺失值
■使用is present
当数据单元指定的单位在model子句执行之前存在,则is precent返回ture.
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
■presentv()
如果cell引用的记录在model子句执行之前就存在,那么presentv(cell, expr1, expr2)返回表达式expr1。如果这条记录不存在,则返回表达式expr2。
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)
■presentnnv
presentnnv(cell, expr1, expr2)如果cell引用的单元在model子句执行之前已经存在,并且该单元的值不为空,则返回表达式expr1。如果记录不存在,或单元值为空,则返回表达式expr2;
sales_amount[for month from 1 to 3 increment 1, 2004] = presentnnv(sales_amount[currentv(), 2003],
round(sales_amount[currentv(), 2003] * 1.25, 2), 0)
■ignore nav 和 keep nav
此关键词使用在 model后面;
ignore nav返回值如下:
●空值或缺失数字值时返回0
●空值或缺失字符串值时返回空值字符串
●空值或缺失日期值时返回01-jan-2000。
●其它所有数据库类型时返回空值
keep nav对空值或缺失数字值返回空值,默认条件;
select .....
from table
model ignore nav
paratition by ....
dimension by ....
measures .......
更新已有的单元
默认情况下,如果表达式左端的引用单元存在,则更新该单元。如果该单元不存在,就在数组中创建一条新的记录。可以用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
partition by (prd_type_id)
dimension by (month, year)
measures (amount sales_amount)
rules update (
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;
引用从图中可以看出已经没有2004年的数据了;
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-10/136114.htm