ORACLE高级查询之MODEL PART3

接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.5 UNIQUE 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的列指定的重复数据。

insert into sales_history values (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;

未完待续，见PART4：http://blog.chinaunix.net/uid-7655508-id-5835079.html