ORACLE高级查询之MODEL PART4

接PART3：http://blog.chinaunix.net/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查询的所有结果，包括2000和2001年3月以及根据此推导出的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;

  下面看一个只查询根据规则更新的结果，也就是只查询根据规则计算出来的2004年3月的更新数据。使用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(规则)

  规则Rules是model查询的核心所在，学好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的值3，CV函数的计算结果=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),那么含义有所不同，第1个CV函数自适应ANY,第2个CV函数的值是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)

      

解释如下：

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

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

 

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

 

v1,v2 是一个对应于维度列d的范围，不一定从小到大，比如from 2004 to 2001 decrement 1和from 2001 to 2004 increment 1效果一样，当然from和increment|decrement的组合必须是从小到大的，其中n必须是正数。此中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两项文档中有，但是在10g、11g中实际测试没有此限制：

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：http://blog.chinaunix.net/uid-7655508-id-5835161.html