oracle MODEL子句

最新推荐文章于 2021-04-12 21:34:42 发布

iteye_9088

最新推荐文章于 2021-04-12 21:34:42 发布

阅读量125

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/iteye_9088/article/details/82297102

版权

数据库专栏收录该内容

17 篇文章 0 订阅

订阅专栏

转载自：http://blog.csdn.net/wh62592855/article/details/4783021

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)

)

Order by prd_type_id,year,month;

使用位置标记或符号标记之间有一个区别需要了解，即它们处理维度中空值的方式不同。例如，sales_amount[null,2003]返回月份为空值、年份为2003的销量，而sales_amount[month=null,year=2004]则不会访问任何有效的数据单元，因为null=null的返回值总是false。

3、用BETWEEN和AND返回特定范围内的数据单元

BETWEEN和AND关键字可用于访问一段范围内的数据单元。例如，下面这个表达式将2004年1月的销量设置为2003年1月至3月销量的平均值取整：

Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)

下面这个查询展示了上述表达式的用法：

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]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2)

)

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 15105.77

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

2 2003 8 1734.82

2 2003 9 1854.57

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

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

2 2003 10 2754.19

2 2003 11 1329.73

2 2003 12 1034.84

2 2004 1 1539.11

26 rows selected.

4、用ANY和IS ANY访问所有的数据单元

可以用ANY和IS ANY谓词访问数组中所有的数据单元。ANY和位置标记合用，IS ANY和符号标记合用。例如，下面这个表达式将2004年1月的销量设置为所有年份月份的销量之和取整：

Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)

下面这个查询展示了上述表达式的用法：

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]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2)

)

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 197916.96

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

2 2003 8 1734.82

2 2003 9 1854.57

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

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

2 2003 10 2754.19

2 2003 11 1329.73

2 2003 12 1034.84

2 2004 1 20426.96

26 rows selected.

5、用CURRENTV()获取某个维度的当前值

CURRENTV()函数用于获得某个维度的当前值。例如，下面的表达式将2004年第一个月的销量设置为2003年同月销量的1.25倍。注意此处用CURRENTV()获得当前月份，其值为1

Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

下面这个查询展示了上述表达式的用法：

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]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

)

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 12543.55

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

2 2003 8 1734.82

2 2003 9 1854.57

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

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

2 2003 10 2754.19

2 2003 11 1329.73

2 2003 12 1034.84

2 2004 1 1293.55

26 rows selected.

6、用FOR循环访问数据单元

可以通过FOR循环访问数据单元。例如，下面这个表达式将2004年前三个月的销量设置为2003年相应月份销量的1.25倍。注意其中使用了FOR循环，还通过INCREMENT关键字定义每一次循环迭代中month的增量：

Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=

ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

下面这个和查询语句展示了上述表达式的用法：

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[FOR month from 1 TO 3 INCREMENT 1,2004]=

ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

)

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 12543.55

1 2004 2 18930.81

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 1293.55

2 2004 2 1930.81

2 2004 3 2547.29

30 rows selected.

7、处理空值和缺失值

1）使用IS PRESENT

当数据单元指定的记录在MODEL子句执行之前存在，则IS PRESENT返回TRUE。例如：

Sales_amount[CURRENTV(),2003] IS PRESENT

如果Sales_amount[CURRENTV(),2003]存在，则返回TRUE。

下面的表达式将2004年前三个月的销量设置为2003年同期销量的1.25倍：

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

下面这个查询展示了上述表达式的用法：

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[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

)

Order by prd_type_id,year,month;

2）使用PRESENTV()

如果cell引用的记录在MODEL子句执行以前就存在，那么PRESENTV(cell,expr1,expr2)返回表达式expr1。如果这条记录不存在，则返回表达式expr2。例如：

PRESENTV(sales_amount[CURRENTV(),2003],

ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)

如果sales_amount[CURRENTV(),2003]存在，上面的表达式返回取整后的销量；否则，返回0.下面这个查询展示了上述表达式的用法：

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[FOR month from 1 TO 3 INCREMENT 1,2004]=

PRESENTV(sales_amount[CURRENTV(),2003],

ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)

)

Order by prd_type_id,year,month;

3）使用PRESENTNNV()

如果cell引用的单元在MODEL子句执行之前已经存在，并且该单元的值不为空，则PRESENTNNV(cell,expr1,expr2)返回表达式expr1。如果记录不存在，或单元值为空值，则返回表达式expr2。例如：

PRESENTNNV(sales_amount[CURRENTV(),2003],

ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)

如果sales_amount[CURRENTV(),2003]存在且为非空值，那么上面的表达式将返回取整后的销量；否则返回0。

4）使用IGNORE NAV和KEEP NAV

IGNORE NAV的返回值如下：

空值或缺失数字值时返回0。

空值或缺失字符串值时返回空字符串。

空值或缺失日期值时返回01-JAN-2000。

其他所有数据库类型时返回空值。

KEEP NAV对空值或缺失数字值返回空值。注意默认条件下使用KEEP NAV。

下面这个查询展示了IGNORE NAV的用法：

select prd_type_id,year,month,sales_amount

from all_sales

where prd_type_id between 1 and 2

and emp_id=21

model IGNORE NAV

partition by (prd_type_id)

dimension by (month,year)

measures (amount sales_amount)

(

Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=

ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

)

Order by prd_type_id,year,month;

8、更新已有的单元

默认情况下，如果表达式左端的引用单元存在，则更新该单元。如果该单元不存在，就在数组中创建一条新的记录。可以用RULES UPDATE改变这种默认的行为，指出在单元不存在的情况下不创建新纪录。

下面这个查询展示了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 IGNORE NAV

partition by (prd_type_id)

dimension by (month,year)

measures (amount sales_amount)

RULES UPDATE

(

Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=

ROUND(sales_amount[CURRENTV(),2003]*1.25,2)

)

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

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

2 2003 8 1734.82

2 2003 9 1854.57

2 2003 10 2754.19

PRD_TYPE_ID YEAR MONTH SALES_AMOUNT

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

2 2003 11 1329.73

2 2003 12 1034.84

24 rows selected.

可以看到，虽然2004年的单元不存在，可是同时指定了RULES UPDATE，所以不会在数组中为2004年创建新纪录，因此这条查询语句不会返回2004年的记录。