Oracle 11g学习笔记–model子句

197次阅读

共计 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]，返回指定年月的销量。

执行结果：
Oracle 11g 学习笔记–model 子句

图中的所有 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;