Oracle 11g学习笔记–model子句

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)