oracle 查询reference,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.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;