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”一节对这两个函数讨论和例子。
分享到:
相关推荐
NULL 博文链接:https://caohong286.iteye.com/blog/1493368
NULL 博文链接:https://caohong286.iteye.com/blog/1490004
NULL 博文链接:https://caohong286.iteye.com/blog/1488059
NULL 博文链接:https://caohong286.iteye.com/blog/1486738
NULL 博文链接:https://caohong286.iteye.com/blog/1485710
NULL 博文链接:https://caohong286.iteye.com/blog/1482168
NULL 博文链接:https://caohong286.iteye.com/blog/1504194
NULL 博文链接:https://caohong286.iteye.com/blog/1260817
Pro Oracle SQL unlocks the power of SQL in the Oracle Database—one of the most potent SQL implementations on the market today. To master it requires a three-pronged approach: learn the language ...
Walking the Tree: From the Top Down 5-9 Ranking Rows with the LEVEL Pseudocolumn 5-10 Formatting Hierarchical Reports Using LEVEL and LPAD 5-11 Pruning Branches 5-13 Summary 5-14 Practice 5 Overview 5...
Clause-view个人中心、设置常用的itemview
资源分类:Python库 所属语言:Python 资源全名:clause-1.1.2.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
Considered the best Oracle PL/SQL programming guide by the Oracle community, this definitive guide is precisely what you need to make the most of Oracle’s powerful procedural language. The sixth ...
Chatopera 语义理解系统:机器学习,聊天机器人,意图识别
运算符、表达式、Conditions、函数、Common sql ddl c、Clause
10g-including new features such as regular expressions and the MODEL SQL clause-as well as versions 8, 8i, and 9i <br>The authors are well-known as Oracle gurus-Greenwald is the author of Oracle ...
(b) the name of the table, the names of the table's attributes, the data types of the table's attributes, the formats of the table's attributes, and the maximum number of rows that the table can have...
1000BASE-X IEEE 802.3-2008 Clause 36 - Physical Coding Sublayer (PCS)_latest.tar.gz
关于xeus-sql 主页: : 软件包许可证:BSD-3-Clause 原料许可证: 简介:基于xeus和SOCISQL的Jupyter内核文档: : 当前构建状态蔚蓝 变体状态linux_64 osx_64 win_64当前发行信息姓名资料下载版本平台类安装xeus-sql...