《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
  )