《Pro Oracle SQL》CHAPTER 九 The Model Clause - 9.9 NULLs
NULLs
(page 291)
In SQL statements using Model SQL, values can be null for two reasons: null values in the existing
cells and references to non-existent cells. I will discuss the later scenario in this section.
By default, the reference to non-existent cells will return null values. In Listing 9-21, the rule in
line 10 is accessing the Sale column for the year =2002 and the week =1 using the clause sale[2002,1].
There is no data in the sales_fact table for the year 2002 and so sale[2002,1] is accessing a non-
existent cell. Output in this listing is null due to the arithmetic operation with a null value.
In Line 4, I added a KEEP NAV clause after the Model keyword explicitly even though KEEP NAV is the
default value. NAV stands for Non Available Values and reference to a non-existent cell returns a null
value by default.
使用Model子句的SQL，有两种情况会导致值为null：单元格存在null值和引用了非存在的单元格。在本节我讨论后面这种情况。默认情况下，引用非存在的单元格将返回null值。在列表9-21中，第10行的规则用子句sale[2002,1]访问year =2002 且 week
=1的Sale列。在sales_fact表中没有2002年的数据，因此sale[2002,1]访问的是非存在的单元格。因为与null值进行算术运
算所以该列的输出是null。在第4行，我在Model关键字之后显示的加KEEP NAV 子句，尽管KEEP NAV是默认值。NAV代表Non
Available Values，而默认情况下引用非存在的单元格将返回null值。
Listing 9-21 KEEP NAV Example
1 select product, country,year,week,sale
2 from sales_fact
3 where country in ('Australia') and product = 'Xtend Memory'
4 model KEEP NAV
return updated rows
5 partition by (product,country)
6 dimension by (year, week)
7 measures (sale)
8 rules sequential order(
9 sale[2001,1] order by year, week = sale[2001,1],
10 sale[2002,1] order by year,week = sale[2001,1] + sale[2002,1]
11)
12 * order by product, country, year, week
PRODUCT COUNTRY YEAR WEEK SALE
------------------------------ ---------- ----- ---- -------
Xtend Memory Australia 2001 1 92
Xtend Memory Australia 2002 1
This default behavior can by modified using the IGNORE NAV clause.
Listing 9-22 shows an example. If the non-existent cells are accessed,
then 0 is returned for numeric columns and an empty string is returned
for text columns instead of null values.
You can see that the output in
Listing 9-22 shows that a value of 92.26 is returned for the clause
sale[2001,1] + sale[2002,1] as zero is returned for the non existing
cell sale[2002,1].
可用IGNORE
NAV子句改变默认行为。
列表9-22展示了一个例子。若访问了非存在的单元格，则替换null，数值列将返回0而文本列将返回空串。
你可从列表
9-22的输出中看到，对子句sale[2001,1] + sale[2002,1]
返回了值92.26，因为非存在的单元格sale[2002,1]返回0。
Listing 9-22. IGNORE NAV
1 select product, country,year,week,sale
2 from sales_fact
3 where country in ('Australia') and product = 'Xtend Memory'
4 model IGNORE NAV
return updated rows
5 partition by (product,country)
6 dimension by (year, week)
7 measures (sale)
8 rules sequential order(
9 sale[2001,1] order by year, week = sale[2001,1],
10 sale[2002,1] order by year,week = sale[2001,1] + sale[2002,1]
11)
12 * order by product, country, year, week
PRODUCT COUNTRY YEAR WEEK SALE
------------------------------ ---------- ----- ---- -------
Xtend Memory Australia 2001 1 92
Xtend Memory Australia 2002 1 92
The functions PRESENTV and PRESENTNNV are also useful in handling NULL
values. Refer to the earlier section called "Iteration" for discussion
and examples of these two functions.
函数PRESENTV 和PRESENTNNV对于处理NULL值也很有用。参考之前“Iteration”一节对这两个函数讨论和例子。