共计 3162 个字符,预计需要花费 8 分钟才能阅读完成。
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