你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle 11g学习笔记–model子句

[日期:2016-10-17] 来源:Linux社区  作者:e_xiake [字体: ]

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()获取当前月份,其值为1sales_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.252

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.252), 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.252), 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

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款