ORACLE MODEL子句学习笔记

ORACLE 10G中新增的MODEL子句可以用来进行行间计算。MODEL子句允许像访问数组中元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。

 

1、MODEL子句示例

下面这个查询获取2003年内由员工#21完成的产品类型为#1和#2的销量，并根据2003年的销售数据预测出2004年1月、2月、3月的销量。

 

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分区的。
• dimension by (month,year)定义数组的维度是month和year。这就意味着必须提供月份和年份才能访问数组中的单元。
• measures (amount sales_amount)表明数组中的每个单元包含一个数量，同时表明数组名为sales_amount。
• MEASURES之后的三行命令分别预测2004年1月、2月、3月的销量。
• Order by prd_type_id,year,month仅仅是设置整个查询返回结果的顺序。

上面这个查询的输出结果如下：

PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT

----------- ---------- ---------- ------------

          1       2003          1     10034.84

          1       2003          2     15144.65

          1       2003          3     20137.83

          1       2003          4     25057.45

          1       2003          5     17214.56

          1       2003          6     15564.64

          1       2003          7     12654.84

          1       2003          8     17434.82

          1       2003          9     19854.57

          1       2003         10     21754.19

          1       2003         11     13029.73

 

PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT

----------- ---------- ---------- ------------

          1       2003         12     10034.84

          1       2004          1     10034.84

          1       2004          2     35282.48

          1       2004          3     25172.29

          2       2003          1      1034.84

          2       2003          2      1544.65

          2       2003          3      2037.83

          2       2003          4      2557.45

          2       2003          5      1714.56

          2       2003          6      1564.64

          2       2003          7      1264.84

 

PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT

----------- ---------- ---------- ------------

          2       2003          8      1734.82

          2       2003          9      1854.57

          2       2003         10      2754.19

          2       2003         11      1329.73

          2       2003         12      1034.84

          2       2004          1      1034.84

          2       2004          2      3582.48

          2       2004          3      2547.29

 

30 rows selected.

 

2、用位置标记和符号标记访问数据单元

前面的例子已经介绍了如何使用位置标记来访问数组中的某个单元。还可以使用符号标记显式指定维度的含义。例如，sales_amount[month=1,year=2004]。下面这个查询用符号标记重写了前面的查询。

 

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[month=1,year=2004]=sales_amount[month=1, year=2003],

Sales_amount[month=2, year=2004]=sales_amount[month=2, year=2003] + sales_amount[month=3, year=2003],

Sales_amount[month=3, year=2004]=ROUND(sales_amount[month=3, year=2003]*1.25,2)

)