接PART2:http://blog.chinaunix.net/uid-7655508-id-5835051.html
1.2.4 Null度量和单元格缺失
在Model查询中,对NULL度量有两种:存在单元格的值是NULL,或不存在此单元格。此种情况,一律按NULL处理。有两种方式处理:KEEP或者IGNORE。
可以在Model后面使用KEEP NAV,这是默认情况,会自动对不存在的或为NULL的置NULL,没有默认值。另外还可以使用IGNORE NAV,对指定类型的NULL有默认值,默认值如下:
类型
默认值
数值类型
0
时间类型
2000年1月1日
字符串类型
空字符串
其他类型
NULL
下面的12月的sales为null。
例如:默认的KEEP NAV:
SELECT r, y, m, s
FROM sales_history
WHERE month = 12
MODEL
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,12] = (s[2000,12]+s[2001,12])/2)
ORDER BY y, r, m;
R Y M S
---------- ---------- ---------- ----------
5 2004 12
6 2004 12
7 2004 12
使用IGNORE NAV:
SELECT r, y, m, s
FROM sales_history
WHERE month = 12
MODEL
IGNORE NAV
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004,12] = (s[2000,12]+s[2001,12])/2)
ORDER BY y, r, m;
R Y M S
----------
---------- ---------- ----------
5
2004 12 0
6
2004 12 0
7
2004 12 0
对于找不到的单元格不计算,比如上面规则改为RULES (s[2004,m=12] =
(s[2000,12]+s[y=2001,m=12])/2),因为2004,12这个单元格无(采用的是混合单元格引用),因此没有结果。但是后面会说到根据原有单元格预测新的值。
1.2.5UNIQUE DIMENSION、UNIQUE SINGLE REFERENCE
model查询有相关输入数据的唯一性要求,有两种方式指定模型查询行的唯一性: UNIQUE DIMENSION和UNIQUE SINGLE REFERENCE。
默认的是使用UNIQUE DIMENSION ,也就是用partition by和dimension by的输入数据的键构成列的组合唯一。
当你不指定任何unique条件(默认unique dimension)或指定unique dimension的时候,
数据库引擎会做一个检查,确保由partition by和dimension by构成的列的组合唯一。
如下:
INSERT INTO sales_history VALUES(5,2000,12,10000);
--根据分区键值region_id和dimension year和month得到5号区域的2000年12月数据重复
--因此,输入的数据有不唯一报错ORA-32638,UNIQUE DIMENSION会做全部输入的检查
--如果改为month=11,那么不会出错,12的已经不在输入源中
SELECT r, y, m, s
FROM sales_history
WHERE month =12
MODEL
IGNORE NAV
RETURN UPDATED ROWS
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
--是和输入的数据month=12确定的有关,和下面单元格引用没有关系,比如month<=12,下面改为11
--还是会报错
RULES (s[2004,12] = (s[2000,12]+s[2001,12])/2)
ORDER BY y, r, m;
UNIQUE DIMENSION是一个默认的情况,model查询的数据是month=10,必须要求region_id分区内的year和month=10的数据唯一。否则报错。
查询输入的数据由partition by和dimension by作为关键字确定,那么可以使用UNIQUE SINGLE REFERENCE代替unique dimension,因为unique dimension会对整个model查询的输入做检查,但是unique single refernce不会对model查询的整个输入做检查,只会检查rules的右边的数据,从而避免了检查提高性能(只对需要计算的单元格进行唯一性检查)。下面做个实验:
先将sales_history表中插入一条2001年7月的由partition by和dimension
by的列指定的重复数据。
insertintosales_historyvalues(5,2001,7,null);
1.使用unique dimension会报错。
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
UNIQUE DIMENSION
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004, 10] = (s[2000,10] + s[2001,10]) / 2)
ORDER BY y, r, m;报ORA-32638: Non unique addressing in spreadsheet
dimensions(Model维中的非唯一寻址)错误。
原因是使用unqiue dimension,整个model查询的输入数据基于month<=10,而我们插入了month=7,year=2001,region_id=5,这个由partition和dimension by确定的数据已经存在,产生了重复,unique dimension会多整个输入的数据唯一性做检查,因此报错。
2.还是上面的语句,使用unique single reference则正确。
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
UNIQUE SINGLE REFERENCE
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004, 10] = (s[2000,10] + s[2001,10]) / 2)
ORDER BY y, r, m;
正确,原因在于使用unqiue single reference只会检查rules的右边的数据是否产生重复,当然也根据partition by和dimension by确定的列。也就是只检查2000年10月和2001年10月对应的region_id这3个列的组合是否重复,因为没有重复,所以正确,减少了全部数据的检查,提高了性能。
3. 下面的错误
SELECT r, y, m, s
FROM sales_history
WHERE month <= 10
MODEL
UNIQUE SINGLE REFERENCE
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004, 10] = (s[2000,10] + s[2001,7]) / 2)
ORDER BY y, r, m;因为使用unique single reference,检查rules右边的,发现2001年7月对应region_id的组合列有重复,因此报ORA-32638错误。
4. 不会报错,因为整个输入源已经没有s[2001,7],month<=5决定输入源无此单元格
SELECT r, y, m, s
FROM sales_history
WHERE month <= 5
MODEL
UNIQUE SINGLE REFERENCE
PARTITION BY (region_id r)
DIMENSION BY (year y, month m)
MEASURES (sales s)
RULES (s[2004, 10] = (s[2000,10] + s[2001,7]) / 2)
ORDER BY y, r, m;