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