- 浏览: 86717 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (81)
- 读书笔记 (14)
- NetBeans学习 (1)
- JavaBeans and Bean Events (3)
- 《Pro Oracle SQL》Chapter 2 SQL Execution (13)
- 《Pro Oracle SQL》Chapter 3 Access and Join Methods (16)
- Pro Oracle SQL Chapter 5 (0)
- Pro Oracle SQL Chapter 6 (0)
- Pro Oracle SQL Chapter 7 (9)
- Pro Oracle SQL Chapter 8 (9)
- 《Pro Oracle SQL》Chapter 9 The Model Clause (11)
- 《Pro Oracle SQL》Chapter 10 Subquery Factoring (7)
最新评论
-
mojunbin:
这个不能不顶。
《Pro Oracle SQL》 Chapter2--2.1 Oracle Architecture Basics -
Branding:
谢谢,获益匪浅
《Pro Oracle SQL》--chapter 5--5.6 Building Logical Expressions -
Branding:
《Pro Oracle SQL》--Chapter 5--5.4 Questions about the Question -
Branding:
谢谢
《Pro Oracle SQL》 翻译序 -- 读书心得 -
jiaoshiguoke:
继续 加油
《Pro Oracle SQL》--Chapter 6--6.1 Explain Plans--之三
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.3 Positional and Symbolic Refere
Positional and Symbolic References 位置和符号引用
(page 277)
As discussed previously, the CV function provides the ability to refer to a single cell. It is also possible
to refer to an individual cell or group of cells using positional or symbolic notations. In addition, you
can write FOR loops as a way to create or modify many cells in an array-like fashion.
如前讨论的,CV函数提供引用单个单元格的能力。使用位置和符号标记(法)也可以引用某个或一组单元格。还有,作为一种方式,你可以像(处理)数组的风格那样写For循环,创建或修改多个单元格。
Positional Notation
位置标记
Positional notation provides the ability to insert a new cell or update an existing cell in the result set.
If the referenced cell exists in the result set, then the cell value is updated; if the cell doesn’t exist, then
a new cell will be added. This concept of “update if exists, insert if not” is termed as an UPSERT feature,
a fused version of the Update and Insert facilities. Positional notation provides UPSERT capability.
位置标记提供在结果集中插入一新单元格或更新某存在的单元格的能力。如果引用的单元格存在于结果集中,则更新单元格的值;如果单元格不存在,则增加新单元格。
“存在即更新,否则插入”的概念就是称为UPSERT特性,Update和Insert功能的熔合版。位置标记提供UPSERT能力。
Suppose that you need to add new cells to initialize the column values for the year equal to 2002
and week equal to 1. You could achieve that with a rule defined using a positional notation. In the
Listing 9-6, line 13 and line 14 are adding new cells for the Year equal to 2002 and Week equal to 1
using the positional notation with the clause sale[2002,1]=0. Within the square brackets, the position
of the value refers to the column order declared in the dimension clause. In this case, column order is
(year, week, hence the clause sale[2002,1] refers to the Sale column value for the row satisfying the
predicate year=2002 and week=1. There are no rows with a column value of year equal to 2002 and
week equal to 1, and a new row was inserted with a zero value for the Sale column for the year =2002
and week=1. The last row in the output was inserted by this rule.
假设你需要加新单元格初始化year等于2002而week等于1的列值。你就能用位置标记定义的规则实现。在列表9-6中,行13和14,用位置标记子
句sale[2002,1]=0添加了新的单元格到Year=2002,Week=1(的位置上)。在方括号中,位置值引用在维度子句中声明的列顺序。在本例中,列顺序是(year,week,因此子句sale[2002,1]引用Sale列值,满足谓词year=2002 and
week=1的行。没有行的列值year等于2002和week等于1,则Sale列year=2002,week=1处插入一为零值的新行。
Listing 9-6. Positional Reference to Initialize for Year 2002 - UPSERT 位置引用初始化Year2002
1 select product, country, year, week, inventory, sale, receipts
2 from sales_fact
3 where country in ('Australia') and product ='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( 0 inventory , sale, receipts)
8 rules automatic order
(
9 inventory [year, week ] =
10 nvl(inventory [cv(year), cv(week)-1 ] ,0)
11 - sale[cv(year), cv(week) ] +
12 + receipts [cv(year), cv(week) ],
13 sale [2002, 1] = 0,
14 receipts [2002,1] =0
15 )
16* order by product, country,year, week
...
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------ ---------- ----- ---- ---------- ---------- ----------
...
Xtend Memory Australia 2001 49 2.519 45.26 47.33
Xtend Memory Australia 2001 50 11.775 23.14 32.40
Xtend Memory Australia 2001 51 -20.617 114.82 82.43
Xtend Memory Australia 2001 52 -22.931 23.14 20.83
Xtend Memory Australia 2002 1 0 .00 .00
...
Symbolic Notation 符号标记
Symbolic notation provides the ability to specify a range of values in the left hand side of a rule.
Let’s
say that you want to update the sales column values to 110% of their actual value for the weeks 1, 52,
and 53 for the years 2000 and 2001. The SQL in the Listing 9-7 does that. The clause year in
(2000,2001) in line 9 uses an IN operator to specify a list of values for the Year column. Similarly, the
clause week in (1,52,53) specifies a list of values for the week column.
符号标记提供为规则左手边的值指定一范围的能力。
如果你想要把week为1,52,53和years为2000,2001的销售列值更新至它们实际值的
110%。列表8-7中的SQL就能做到。第9行的子句year
in(2000,2001)使用IN操作符指定Year列的一列值。类似的,子句week in (1,52,53) 指定week列的一列值。
Note that the output in the Listing 9-7 is not a partial output and that there are no rows for the
week equal to 53. Even though you specified 53 in the list of values for the week column in line 9, there
are no rows returned for that week. The reason is that symbolic notation can only update the existing
cells ; it does not allow new cells to be added.
注意列表8-7中的输出不是部分输出,而且没week等于53的行。即使你在第9行的week列的列值中指定53,而那周没有返回行。原因是符号标记只能更新存在的单元格;它不允许增加新单元格。
NOTE I will discuss a method to insert an array of cells in the upcoming section “For Loops.”
注意 我将在接下来的“For Loops”节中讨论插入一组单元格的方法。
There is no data with a Week column value equal to 53 and no new row was added or updated in
the result set for the week=53. The ability to generate rows is a key difference between symbolic and
positional notations. Symbolic notation provides UPDATE-only facility and positional notation
provides UPSERT facility.
Week列等于53没有数据,且没有新行添加或week=53的结果集更新。生成行的能力是符号和位置标记间关键的区别。符号标记提供“只更新”的功能而位置标记提供UPSERT功能。
Listing 9-7. Symbolic Reference – UPDATE
1 select product, country, year, week, sale
2 from sales_fact
3 where country in ('Australia') and product ='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( sale)
8 rules(
9 sale [ year in (2000,2001), week in (1,52,53) ] order by year, week
10 = sale [cv(year), cv(week)] * 1.10
11 )
12* order by product, country,year, week
PRODUCT COUNTRY YEAR WEEK SALE
------------ ---------- ----- ---- ----------
Xtend Memory Australia 2000 1 51.37
Xtend Memory Australia 2000 52 74.20
Xtend Memory Australia 2001 1 101.49
Xtend Memory Australia 2001 52 25.45
There are a few subtle differences between the SQL statement in Listing 9-7 and prior SQL
statements. For example, the statement in Listing 9-7 is missing automatic order in line 8. I’ll discuss
the implication of that in the “Rule Evaluation Order” section later in this chapter.
在列表9-7中SQL语句与之前的SQL语句有微妙的差别。例如,列表9-7中的语句缺少第八行的“automatic order”。我将在本章后面的“Rule Evaluation Order”一节中讨论它的含义。
FOR Loops For循环
FOR loops allow you to specify list of values in the left hand side of a rule. FOR loops can be defined in
the left hand side of the rule only to add new cells to the output; they can’t be used in the right hand
side of the rule.
Syntax for the FOR loop is:
For循环允许你在规则左手边指定一列值。FOR循环可定义在规则左手边,只增加新单元格到结果集中;它们不能用在规则的右手边。
句法如下:
FOR dimension FROM <value1> TO <value2>
[INCREMENT | DECREMENT] <value3>
For example, say you want to add cells for the weeks ranging from 1 to 53 for the year 2002 and
initialize those cells with a value of 0. Line 13 in Listing 9-8 inserts new rows for the year 2002 and
weeks ranging from 1 to 53 using a FOR loop. Clause Increment 1 increments the week column values
to generate weeks from 1 to 53. Similarly, the receipts column is initialized using the clause receipts
[2002, for week from 1 to 53 increment 1] =0.
例如,你想要加单元格week范围从1到53,year为2002,且初始化这些单元格的值为0。列表9-8的第13行使用FOR循环插入year为
2002且week范围从1到53的新单元格。类似的,receipts列用子句receipts[2002, for week from 1 to
53 increment 1] =0初始化了。
Listing 9-8. Positional Reference, Model and FOR Loops
1 select product, country, year, week, inventory, sale, receipts
2 from sales_fact
3 where country in ('Australia') and product ='Xtend Memory'
4 model return updated rows
5 partition by (product, country)
6 dimension by (year, week)
7 measures ( 0 inventory , sale, receipts)
8 rules automatic order(
9 inventory [year, week ] =
10 nvl(inventory [cv(year), cv(week)-1 ] ,0
11 - sale[cv(year), cv(week) ] +
12 + receipts [cv(year), cv(week) ],
13 sale [2002, for week from 1 to 53 increment 1] = 0,
14 receipts [ 2002,for week from 1 to 53 increment 1 ] =0
15 )
16* order by product, country,year, week
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------ ---------- ----- ---- ---------- ---------- ----------
...
Xtend Memory Australia 2001 52 -22.931 23.14 20.83
Xtend Memory Australia 2002
1 0 .00 .00
...
Xtend Memory Australia 2002 52 0 .00 .00
Xtend Memory Australia 2002 53 0 .00 .00
...
- 9-6_9-7_9-9运行结果.rar (5.9 KB)
- 下载次数: 2
发表评论
-
《Pro Oracle SQL》Chapter 9 -- 9.11 Subquery Factoring
2012-04-29 23:13 1118Subquery Factoring 子查询分解 ... -
《Pro Oracle SQL》CHAPTER 9 -- 9.10Performance Tuning with the Model Clause
2012-04-29 16:11 1197Performance Tuning with the Mod ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.9 NULLs
2012-04-22 19:57 882NULLs (page 291) In ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.8 Lookup Tables
2012-04-21 14:18 915Lookup Tables 查找表 (page ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.7 Iteration
2012-04-21 13:53 1079Iteration 迭代 (page 287) ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.6 Aggregation
2012-04-19 01:47 900Aggregation 聚合 ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.5 Evaluation Order
2012-04-17 00:52 1180Evaluation Order 求值顺序 ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.4Returning Updated Rows
2012-04-14 22:36 1228Returning Updated Rows 返回更新的行 ... -
《Pro Oracle SQL》CHAPTER 9 -- 9.2 Inter-Row Referencing via the Model clause
2012-04-10 22:35 1010I nter-Row Referencing via the ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.1 Spreadsheets
2012-04-08 15:51 943The Model Clause Model子 ...
相关推荐
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/1260817
NULL 博文链接:https://caohong286.iteye.com/blog/1482168
NULL 博文链接:https://caohong286.iteye.com/blog/1504194
Pro Oracle SQL, Second Edition 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 multi-pronged approach: learn ...
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 ...
Combing the SQL WHERE Clause and the SQL ORDER BY Clause Performing Calculations in SQL Queries Using SQL Built-in Functions SQL Expressions in SQL SELECT Statements Grouping in SQL SELECT Statements ...
(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...
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 ...
运算符、表达式、Conditions、函数、Common sql ddl c、Clause
Chatopera 语义理解系统:机器学习,聊天机器人,意图识别
1000BASE-X IEEE 802.3-2008 Clause 36 - Physical Coding Sublayer (PCS)_latest.tar.gz