Chinaunix首页 | 论坛 | 博客
  • 博客访问: 650848
  • 博文数量: 90
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2899
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(90)

文章存档

2020年(55)

2014年(3)

2013年(32)

分类: Oracle

2020-06-29 09:12:51

接PART3:/uid-7655508-id-5835078.html

1.2.6 返回行

  SQL查询的目标是返回结果集。在model查询中,有两种方式:返回所有的model查询结果和只返回根据规则更新的结果。如下:

       RETURN [ALL | UPDATED] ROWS

  其中return all rows是默认情况。return updated rows是返回根据rules条件更新的行。此选项紧跟在model关键字后面,如果model查询有ignore nav,keep nav,unique dimension,unique single reference选项,则return选项放在这些选项之后(顺序是先nav后unique最后return)。

  下面的例子是默认情况return all rows,返回所有model查询的所有结果,包括200020013月以及根据此推导出的2004年的预测数据,原表中不包括2004年的数据(因为s[2004,3]使用的是位置单元格引用,可以预测)

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES  (s[2004,3] = (s[y=2000,3] + s[2001,3]) / 2)

ORDER BY y, r, m;




  下面看一个只查询根据规则更新的结果,也就是只查询根据规则计算出来的20043月的更新数据。使用return updated rows只查看更新的结果,这里是预测的原本不存在的行,当然如果是存在的行字段被更新,也会返回更新后的结果。

 

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES  (s[2004,3] = (s[y=2000,3] + s[2001,3]) / 2)

ORDER BY y, r, m;



--存在的行也会返回更新后的结果

SELECT r, y, m, s

FROM sales_history

WHERE month = 3

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES  (s[2000,3] = (s[y=2000,3] + s[2001,3]) / 2)

ORDER BY y, r, m;

R     Y     M    S

5     2000       3     849724.5

6     2000       3     955546.5

7     2000       3     983989.5

 

 

1.3 Rules(规则)

  规则Rulesmodel查询的核心所在,学好rules,也就掌握了model查询的核心。规则制定了表中单元格的计算公式。规则使用 rules关键字指定,以括号包含,多个规则以逗号分隔,每条规则分左表达式和右表达式,以等号分开,左边是最终计算的结果,右边是源数据对应单元格。

       RULES [其他关键字] (规则1,规则2,规则3…..)

 

1.3.1 构造RULES

  规则由左右两部分组成,规则子句和前面说的的类似,比如:

       RULES  (s[2004,3] = (s[2000,3] + s[2001,3]) / 2)

  左边的s[2004,3]也就是根据右边的规则计算更新的一些单元格。右边的表达式是做的一些计算,你能够使用任何合法的SQL操作符和函数在规则中使用 (比如符号单元格引用可以使用between,>=等,比如s[2000,3]可以写成s[to_number('2000',3)]),当然,也可以使用一些其他的构造,这些由model子句的指定。如果多条规则对同一单元格计算,则会覆盖前面同一单元格的结果,不会增加。

 

1.3.1.1 CV函数

  

  CV函数语法:CV(可选的一个维度列)。CV函数使用在规则的右边表达式中,它的作用是自适应规则左边对应的维度列的位置(值),特别是在左边需要计算多个维度情况下,自使用维度列非常有用,比如在FOR循环中使用。它的返回值类型是与对应的维度列一致。

  CV函数也可以使用在单元格外边,比如s[2004,3]=s[2000,1]+CV(m),那么这里的CV必须带参数,对应的CV(m)的计算结果就是3

 

1

SELECT r, y, m, s

FROM sales_history

WHERE month <= 3

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

--这里的CV()自适应左边的维度m的值3CV函数的计算结果=3

RULES (s[2004, 3] = (s[2000,CV()] + s[2001,CV()]) / 2)

ORDER BY y, r, m;


  相当于RULES (s[2004, 3] = (s[2000,3] + s[2001,3]) / 2)当然这里也可以写成CV(m)。如果CV函数用在单元格引用外面,那么必须要加参数,比如RULES (s[2004, 3] = (s[2000,3] + s[2001,3]+CV(m)) / 2)CV()函数默认是位置单元格引用,当然也可以写成m=CV()这就是自适应左边对应维度列值的符号单元格引用,但是一般直接写CV()。


1.3.1.2 ANY

  ANY关键字相当于一个通配符,用在规则左边,表示选中所有的对应维度列值(包括NULL值),那么右边一般用CV函数配合,自适应,如果不用CV函数,那么SQL就不好写了。

  注意ANY虽然是位置单元格引用,但是实际上还是符号单元格引用,和后面要说的IS ANY的作用一致,看下面的例子:

 

SELECT r, y, m, s

FROM sales_history

WHERE month <= 3

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

--左边ANY,得到所有month<=3的年,右边CV自适应

RULES (s[ANY, 3] = (s[CV(),1] + s[CV(),2]) / 2)

ORDER BY y, r, m;


  当然也可以改成RULES (s[ANY, 3] = (s[CV(),CV()] + s[CV(),CV()]) / 2),那么含义有所不同,第1CV函数自适应ANY,2CV函数的值是3

  注意ANY关键字会阻止单元格插入,后面会详细讲解。

 

1.3.1.3  FOR  Loops

  FOR Loops允许你写一个规则影响多个单元格的计算,就像PL/SQL中的FOR Loop一样。FOR Loops会在编译器展开,变为多个规则,对Oracle来说,它会看到有很多个规则,而不是一个。

  FOR Loops仅仅允许定义在规则RULE的左边,这样在运算的时候,会一个规则插入多个单元格,它有3种形式:

 

FOR d IN (subquery | list)

 

FOR d [LIKE pattern] FROM v1 TO v2 INCREMENT | DECREMENT n

 

FOR (d1, d2,  . . . ) IN (multi_column_subquery | multi_column_list)

      

解释如下:

其中| 表示可选一个,[ ]表示可以省略。第1FOR Loops是单列,第2种单列模式匹配,第3种多列。

d: 单个维度列,可以使用子查询或list,list也就是逗号分隔的对应维度列值的字面量

 

patternwhere里使用的like不同,它只能使用%,在运行期根据from后的值替换这个%,当然可以有多个%。模式匹配的只能对单列,要对多列模式匹配,只能写多次。

 

v1,v2 是一个对应于维度列d的范围,不一定从小到大,比如from 2004 to 2001 decrement 1from 2001 to 2004 increment 1效果一样,当然fromincrement|decrement的组合必须是从小到大的,其中n必须是正数。此中v1肯定包含,然后按后面的递增或递减的幅度以及终点v2选择对应的单元格范围。v1,v2的类型经常是数值,但是不一定。

 

d1,d2: 多个维度列,可以使用子查询,也可以使用组合列表,比如(1,2),(3,4)等。

 

为什么要使用FOR Loops?看下面的例子:

 

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

--RULES (s[2004,2] = (s[2000,3]+s[2001,3])/2,s[2003,2] = (s[2000,3]+s[2001,3])/2)

RULES (s[y IN (2003,2004),2] = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;


  为了预测2003以及2004年两年2月对应的销售额,不用FOR Loops必须要写两条规则,如果使用符号单元格引用,因为2003,2004都是不存在的单元格,因此没有结果,使用位置单元格只能写两条规则,见注释部分。

 

  OK,使用FOR Loops就可以避免不存在的单元格没有结果,Oracle会在编译器将带有FOR Loops的规则展开为多个规则(类似于逗号分隔的多个规则),而且是位置单元格形式,所以可以插入新的多个单元格。如下:

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR y IN (2003,2004),2] = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;

 

  现在是不是简单多了?这是FOR Loops的最简单应用,下面看个在FOR Loops中使用子查询的例子:

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR (y,m) IN (SELECT 2003,1 FROM dual

  UNION ALL SELECT 2004,2 FROM dual)]

          = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;

 

当然也可以修改为组合列表形式:


SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR (y,m) IN ((2003,1),(2004,2))] = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;


  单列的比较简单,可以自己体会。下面再看个模式匹配的FOR Loops:

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR y FROM 2003 TO 2004 INCREMENT  2,2] = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;

 
 
注意上面写的是INCREMENT 2,这样只能选到2003,下一个应该是2005,但是不在v1 to v2范围内。改为INCREMENT 1则能选中两个。下面举个带LIKE的例子:

sales[FOR product LIKE 'product-%' FROM 1 TO 3 INCREMENT 1, 2003] =

sales[CV(product), 2002] * 1.2

相当于

sales['product-1', 2003] = sales['product-1', 2002] * 1.2,

sales['product-2', 2003] = sales['product-2', 2002] * 1.2,

sales['product-3', 2003] = sales['product-3', 2002] * 1.2

 

--类型不一致,会报越界错误

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR y LIKE '200%' FROM 3 TO 4 INCREMENT  1,2] = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;

 

FOR Loops的限制:

1)子查询不能是相关子查询

2)不能使用with子查询

3)子查询的结果不能超过1000

4)子查询展开后的规则数(也就是子查询的行数),非子查询规则的总量不能超过10000,这个不是FOR Loops特有的,也就是MODEL查询中的规则总量不能超过10000,否则会报错。

3,4两项文档中有,但是在10g11g中实际测试没有此限制:

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR (y,m) IN (SELECT 2003+LEVEL,1 FROM dual

CONNECT BY LEVEL<10002)]

          = (s[2000,3]+s[2001,3])/2)

ORDER BY y, r, m;

 

SELECT r, y, m, s

FROM sales_history

WHERE month <= 10

MODEL

RETURN UPDATED ROWS

PARTITION BY (region_id r)

DIMENSION BY (year y, month m)

MEASURES (sales s)

RULES (s[FOR (y,m) IN (SELECT 2003+LEVEL,1 FROM dual

CONNECT BY LEVEL<10001)]

          = (s[2000,3]+s[2001,3])/2,

          s[1999,1]=s[2000,3])

ORDER BY y, r, m;


未完待续,见PART5:/uid-7655508-id-5835161.html

阅读(455) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~