1.2.6 返回行
SQL查询的目标是返回结果集。在model查询中,有两种方式:返回所有的model查询结果和只返回根据规则更新的结果。如下:
RETURN[ALL | UPDATED] ROWS
其中return all rows是默认情况。return updated rows是返回根据rules条件更新的行。此选项紧跟在model关键字后面,如果model查询有ignore nav,keep nav,uniquedimension,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.1CV函数
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的年,右边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的范围,不一定从小到大,比如from2004 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;
现在是不是简单多了?这是FORLoops的最简单应用,下面看个在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,但是不在v1to v2范围内。改为INCREMENT 1则能选中两个。下面举个带LIKE的例子:
sales[FOR productLIKE '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