- 浏览: 86934 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (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 -- 9.2 Inter-Row Referencing via the Model clause
I
nter-Row Referencing via the Model clause 用Model子句行间引用
(page 274)
In a conventional SQL statement, emulating the spreadsheet described in Listing 9-1 is achieved by a
multitude of self-joins. With the advent of the Model clause, you can implement the spreadsheet
without self-joins because the Model clause provides inter- row referencing ability.
若用传统的SQL语句,仿效
(计算)列表9-1中的电子表格需要多次自连接。而随着Model子句的出现,你可以执行电子表格计算而不需要自连接,因为Model子句拥有行间引用的能力。
Example Data 例子数据
To begin your investigation of the Model clause, you will create a de-normalized fact table using the
script in Listing 9-2. All the tables referred in this chapter refer to the objects in SH Schema supplied by
the Oracle Corporation Example scripts.
为了开始研究Model子句,你要用代码片段9-2的脚本创建一非规范化的事实表。本章引用的所有表指Oracle公司样例脚本提供的SH Schema中的对象。
■ NOTE
To install the Example schema, you can download software from Oracle Corporation at http://
download.oracle.com/otn/solaris/oracle11g/R2/solaris.sparc64_11gR2_examples.zip for the 11gR2
Solaris platform. Refer to the Readme document in the unzipped software directories for installation instructions. Zip files for other platforms and versions are available in this Oracle site.
■ 注意,要安装例子模式,对11gR2 Solaris 平台而言可以从http://download.oracle.com/otn/solaris/oracle11g/R2 /solaris.sparc64_11gR2_examples.zip下载。参考解压缩目录中的Readme安装指导文档。在这个Oracle web站点还提供其他平台和版本的Zip文件。
Listing 9-2. Denormalized sales_fact Table
drop table sales_fact; CREATE table sales_fact AS SELECT country_name country,country_subRegion region, prod_name product, calendar_year year, calendar_week_number week, SUM(amount_sold) sale, sum(amount_sold* ( case when mod(rownum, 10)=0 then 1.4 when mod(rownum, 5)=0 then 0.6 when mod(rownum, 2)=0 then 0.9 when mod(rownum,2)=1 then 1.2 else 1 end )) receipts FROM sales, times, customers, countries, products WHERE sales.time_id = times.time_id AND sales.prod_id = products.prod_id AND sales.cust_id = customers.cust_id AND customers.country_id = countries.country_id GROUP BY country_name,country_subRegion, prod_name, calendar_year, calendar_week_number;
Anatomy of a Model Clause 解剖Model子句
Listing 9-3 shows a SQL statement using the Model clause and emulating the functionality of the
spreadsheet discussed earlier. Let’s explore this SQL statement in detail. I’ll look at the columns
declared in the Model clause and then I’ll discuss rules.
列表8-3展示一SQL语句使用Model子句仿效
之前讨论的电子表格计算功能。让我们详细探查这条SQL语句。我将先看Model子句中声明的columns(列集),再讨论rules(规则集)。
In the Listing 9-3, line 3 declares that this statement is using the Model clause with the keywords
Model return updated rows . In a SQL statement using the Model clause, there are three groups of
columns: partitioning columns, dimension columns, and measures columns. Partitioning columns are
analogous to a sheet in the spreadsheet. Dimension columns are analogous to row tags (A,B,C..) and
column tags (1,2,3..). The measures columns are analogous to cells with formulas.
在列表9-3中,第3行用Model关键字声明该语句使用Model子句返回更新的行集。使用Model子句的SQL语句,有三组列:分区列,维度列和度量列。分区列模拟电子表格的工作表。维度列模拟行标签(A,B,C..)和列标签(1,2,3..)。度量列模拟带公式的单元格。
Line 5 identifies the columns Product and Country as partitioning columns with the clause
partition by (product, country. Line 6 identifies columns Year and Week as dimension columns
with the clause dimension by (year, week. Line 7 identifies columns Inventory, Sales, and, Receipts as
measures columns with the clause measures (0 inventory, sale, receipts). A rule is similar to a
formula, and one such rule is defined in lines 8 through 13.
第5行用子句partition by (product, country) 标示列Product
和Country为分区列。行6用子句dimension by (year, week标示列Year和Week为维度列。行7用子句measures
(0 inventory, sale, receipts)标示列Inventory, Sales,
和Receipts是度量列。规则类似于公式,该规则在8到13行定义。
Listing 9-3. Inventory Formula Calculation using Model Clause 用Model子句计算存货公式
col product format A30
col country format A10
col region format A10
col year format 9999
col week format 99
col sale format 999999
set lines 120 pages 100
1 select product, country, year, week, inventory, sale, receipts
2 from sales_fact
3 where country in ('Australia') and product ='Xtend Memory' -- 注:原书中3,4行位置颠倒
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 )
14* order by product, country,year, week
/
PRODUCT COUNTRY YEAR WEEK INVENTORY SALE RECEIPTS
------------ ---------- ----- ---- ---------- ---------- ----------
..
Xtend Memory Australia 2001 1 4.634 92.26 96.89
Xtend Memory Australia 2001 2 35.424 118.38 149.17
Xtend Memory Australia 2001 3 37.786 47.24 49.60
...
Xtend Memory Australia 2001 9 77.372 92.67 108.64
Xtend Memory Australia 2001 10 56.895 69.05 48.57
..
In a mathematical sense, the Model clause is implementing partitioned arrays.
Dimension
columns are indices into array elements. Each array element, also termed as a cell, is a measures
column.
凭数学的感觉,Model子句按分区的数组执行。
维度列索引数组元素。每个数组元素,也称之为单元格,是一个度量列。
All rows with the same value for the partitioning column(s) are considered to be in a partition.
In
this example, all rows with the same value for product and country are in a partition. Within a
partition, the dimension columns uniquely identify a row. Rules implement formulas to derive the
measures columns and they operate within a partition boundary, so partitions are not mentioned
explicitly in a rule.
所有具有相同分区列值的行被认为是在同一分区中。
在本例中,所有具有相同的product和country的行在一个分区中。在一个分区中,分区列唯一的标示一行。规则集执行公式求取度量列且他们在分区边界内运算,因此分区在规则中没有显式的提及。
NOTE
It is important to differentiate between partitioning columns in the Model clause and the object
partitioning feature. While you can use the keyword partition in the Model clause also, it’s different from the object partitioning scheme used to partition large tables.
注意
区别Model子句和对象分区特性中分区列是重要的。虽然你也能在Model子句使用关键字partition,它不同于用于大表分区的对象分区模式。
Rules
规则(集)
Let’s revisit the rules section from Listing 9-3. You can see both the rule and the corresponding
formula together in Listing 9-4. The formula is accessing the prior week’s inventory to calculate
current week’s inventory, so it requires an inter-row reference. You can see that there is a great
similarity between the formula and the rule.
我们重新考察列表9-3的规则段。在列表9-4中你可看到规则和相应的公式。公式是访问前一周的库存来计算当前周的库存,因此它需要一个行间引用。你能从公式和规则两者间看出极大的相似性。
The SQL statement in Listing 9-4 introduces a useful function named CV. CV stands for Current
Value and can be used to refer to a column value in the right hand side of the rule from the left hand
side of the rule.
For example, cv(year) refers to the value of the Year column from the left hand side of
the rule. If you think of a formula when it is being applied to a specific cell in a spreadsheet, the CV
function allows you to reference the index values for that cell.
在列表9-4的SQL中引入了一个非常有用的名叫CV的函数。CV代表Current
Value(当前值),能用于从规则左手边引用规则右手边的列值。
例如,cv(year)从规则的左手边引用Year列的值。如果你想把某公式应用于电子表格的特定单元格上,CV函数允许你引用那个单元格的索引值。
Listing 9-4. Rule and Formula
Formula for inventory:
Inventory for (year, week) = Inventory (year, prior week)
- Quantity sold in this week
+ Quantity received in this week
Rule from the SQL:
8 inventory [year, week ] =
9 nvl(inventory [cv(year), cv(week)-1 ] ,0)
10 - sale[cv(year), cv(week) ] +
11 + receipts [cv(year), cv(week) ]
Let’s discuss rules with substituted values, as in Listing 9-5. Let’s say that a row with (year, week)
column values of (2001, 3) is being processed. The left hand side of the rule will have the values of
(2001, 3) for the year and column. The cv(year) clause in the right hand side of the rule refers to the
value of the Year column from the left hand side of the rule, that is 2001. Similarly, the clause cv(week)
refers to the value of the Week column from the left hand side of the rule, that is 3. So, the clause
inventory [cv(year), cv(week)-1] will return the value of the inventory measures for the year equal
to 2001 and the prior week, i.e. week equal 2.
让我们用带入的值讨论规则,如列表9-5。我们说带有列值(2001,3)的行在处理中。规则的左边将有year和week值(2001,3)。在规则右手边的cv(year)子句引用规则左手边的Year列值,即2001。相似的,子句cv(week)引用至规则左手边的Week列值,即3。因此,子句
inventory [cv(year), cv(week)-1]将返回year等于2001且上一周,week等于2的库存值。
Similarly, clauses sale[cv(year), cv(week) ] and receipts[cv(year), cv(week)] are referring to
the Sale and Receipts column values for the Year equal to 2001 and Week equal to 3 using CV function.
类似的,子句sale[cv(year), cv(week) ] 和receipts[cv(year), cv(week)]用CV函数引用Year等于2001和Week等于3的Sale和Receipts列值。
Notice that the partitioning columns Product and Country are not specified in these rules. Rules
implicitly refer to the column values for the Product and Country column in the current partition.
注意分区列Product 和Country 在这些规则中没有指定。规则隐含的引用当前分区内的Product 和Country列的列值。
Listing 9-5. Rule Example
Rule example:
1 rules (
2 inventory [2001 , 3] = nvl(inventory [cv(year), cv(week)-1 ] ,0)
3 - sale [cv(year), cv(week) ] +
4 + receipts [cv(year), cv(week) ]
5 )
rules (
inventory [2001 , 3] = nvl(inventory [2001, 3-1 ] ,0)
- sale [2001, 3 ] +
+ receipts [2001, 3 ]
= 35.42 – 47.24 + 49.60
= 37.78
)
- 9-3运行结果.rar (2.9 KB)
- 下载次数: 2
发表评论
-
《Pro Oracle SQL》Chapter 9 -- 9.11 Subquery Factoring
2012-04-29 23:13 1120Subquery Factoring 子查询分解 ... -
《Pro Oracle SQL》CHAPTER 9 -- 9.10Performance Tuning with the Model Clause
2012-04-29 16:11 1199Performance Tuning with the Mod ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.9 NULLs
2012-04-22 19:57 884NULLs (page 291) In ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.8 Lookup Tables
2012-04-21 14:18 918Lookup Tables 查找表 (page ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.7 Iteration
2012-04-21 13:53 1082Iteration 迭代 (page 287) ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.6 Aggregation
2012-04-19 01:47 903Aggregation 聚合 ... -
《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 1231Returning Updated Rows 返回更新的行 ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.3 Positional and Symbolic Refere
2012-04-13 22:01 928Positional and Symbolic Referen ... -
《Pro Oracle SQL》CHAPTER 9 The Model Clause -- 9.1 Spreadsheets
2012-04-08 15:51 946The Model Clause Model子 ...
相关推荐
To preserve them, they need to be below the BEGIN or you will need to edit via the SQL Worksheet. 1.5 Table > Data - Tables > Your_Table > Data - PageUp and PageDown buttons not working correctly if...
Measurement of M2-Curve for Asymmetric Beams by Self-Referencing Interferometer Wavefront Sensor
Referencing using the Harvard author-date 是科技工作者发表论文必须要掌握的要点,大名鼎鼎不多说了。
- NEW: Added UICamera.first referencing the active NGUI event system. - FIX: Alpha should now work as expected with Linear lighting. - FIX: UICamera.isOverUI should now work properly for all types of ...
Wireless 200-355 Official Cert Guide presents you with an organized test-preparation routine through the use of proven series elements and techniques. “Do I Know This Already?” quizzes open each ...
this is targetlink exercise files for model_referencing
使用social network.sql启动MariaDB容器docker-compose up -d 生成/ models / *文件sqlboiler mysql --no-back-referencing -d --no-back-referencing如果不设置它,您的程序将因递归转换而崩溃! 根据sqlboiler...
1.6 From Monolithic via Client/Server to the Internet . . . . . . . . . . 18 1.7 Standardization of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 1.8 What Is Open Source Software?...
Chapter 9: Developing with JavaServer Faces Technology 159 Backing Beans 159 Writing Bean Properties 162 Writing Backing Bean Methods 170 Using Bean Validation 174 Chapter 10: Java Servlet ...
Chapter 9: Espress 9.1The Express Basics 9.2Express Request Routing 9.3. Express Middleware 9.4Express Request Object 9.5 Express Response Obiect 9.6Template Response Sending Chapter 1o: The MongoDB ...
Trust the best selling Official Cert Guide series from Cisco Press to help you learn, prepare, and practice for exam success. They are built with the objective of providing assessment, review, and ...
Chapter 9. Securing web applications 9.1. Getting started with Spring Security 9.1.1. Understanding Spring Security modules 9.1.2. Filtering web requests 9.1.3. Writing a simple security configuration...
Sampling the Anisotropic Microfacet Model Sampling FresnelBlend Specular Reflection and Transmission Application: Estimating Reflectance Sampling BSDFs Sampling Light Sources Basic Interface ...
This is a guide to the Harvard author-date referencing system. It is based on the following manual: Commonwealth of Australia 2002, Style manual for authors, editors and printers, 6th edn, rev. by ...
12.9 The Object Model of JavaScript 524 12.10 Implementation of Object-Oriented Constructs 527 Summary • Review Questions • Problem Set •Programming Exercises 530 Chapter 13 Concurrency ...
paper describing the model, the team also open-sourced the code of the model, and made available for download versions of the model that were already pre-trained on massive datasets. This is a ...
在ORACLE系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的PL/SQL块,不过有一点不同的是,触发器是隐式调用的,并不能接收参数。 触发器优点 (1)触发器能够实施的检查和操作比主键和外键约束、...
java运行依赖jar包
Use of the Pentaho checkstyle format (via mvn checkstyle:check and reviewing the report) and developing working Unit Tests helps to ensure that pull requests for bugs and improvements are processed ...