《Pro Oracle SQL》CHAPTER 九 The Model Clause - 9.7 Iteration

Iteration?? 迭代? (page 287)
??? Iteration provides another facility to implementing complex business requirements using a concise
Model SQL statement.? A block of rules can be executed in a loop a certain number of times or while a
condition remains true. The syntax for the iteration is:
??? 迭代提供了另一种功能，用精简的Model SQL 语句执行复杂的业务需求。一段规则可在一循环中执行多次或者只要条件为真(就一直执行)。迭代的句法是：
? [ITERATE (n) [UNTIL <condition>] ]
( <cell_assignment> = <expression> ... )
?
??? Use the syntax? ITERATE (n)? to execute an expression? n times. Use the expression? ITERATE UNTIL
<condition>? to iterate while the given condition remains true.
??? 用句法ITERATE (n) 执行表达式n次。用表达式ITERATE UNTIL <condition>只要给定的条件保持为真就迭代。
An Example???
??? Suppose the goal is to show five weeks of sale column values in a comma-separated list format. This
requirement is implemented in Listing 9-17.
??? 设目标是展示五周的销售列值以逗号分隔的串格式。该需求在列表9-17执行了。
?? NOTE?? Conversion of rows to columns is termed as? pivoting . Oracle database 11g introduces syntax to implement pivoting function natively. In Oracle database 10g, you could use the Model clause to implement pivoting.
??? 注意?? 转换行成列术语称之为pivoting(旋转).Oracle数据库11g引入句法可执行内在的旋转函数。在Oracle数据库10g，你可用Model子句执行旋转。 注：关于pivot和unpivot参考http://blog.csdn.net/tianlesoftware/article/details/7060306?
??? Line 8 specifies that the rules block is to be iterated five times for each row. That’s done through
the clause? rules iterate(5). In line 10, you use Iteration_number,? which is a variable available within
the rules section, to access the current iteration count of the loop. Iteration_number? starts with a value
of 0 for the first iteration in the loop and ends at n-1 where n is the number of loops as specified in the
iterate(n) clause. In this example, the? Iteration_number variable value ranges from 0 to 4. With
Iteration_number and bit of arithmetic, you can access the prior two weeks and the next two weeks’
values using the clause? CV(week)-ITERATION_NUMBER +2. The CASE statement adds a comma for each
element in the list, except for the first element.
??? 第8行指定规则块对每行执行5次。通过子句 rules iterate(5)做到。在第10行，你用 Iteration_number，在规则段内的一个变量，用于访问循环中迭代次数。Iteration_number 对应循环的第一次迭代的起始值是0，且终止值n-1，其中n是在iterate(n) 子句中指定的循环次数。在本例中，Iteration_number 变量值域是0到4。通过Iteration_number 和一点算术，你能用子句CV(week)-ITERATION_NUMBER +2访问之前两周和之后两周的值。CASE语句为列表的每个元素加逗号，除了第一个元素。
??? For example, let’s assume the current row in the process has a value of year=2001 and week=23. In
the first iteration of the loop, iteration_number will be zero, and the clause cv(week)-ITERATION_NUMBER
+2? will access the row with week=23-0+2=25. In the next iteration, week 24 will be accessed, and so on. The FOR loop is repeated for every row in the model output.
??? 例如，我们假设处理的当前行有year=2001 and week=23的值。在循环的第一次迭代中，iteration_number 是0，且子句cv(week)-ITERATION_NUMBER +2 将访问行 week=23-0+2=25。在下一次迭代中，访问week24，如此下去。FOR循环对model输出中的每行重复。
??? Let’s review the output rows in Listing 9-17. For the year 2001, week 23, column Sale_list? has the
following list of values: 233.7, 141.78, 22.38, 136.92, 139.28.? You can see how those values are
centered on the current week. The first two come from sales? column for the immediately preceding
weeks. Then you have the current week’s sales, and then the values from the following two weeks.?
??? 我们查看列表9-17的输出行。对year2001，week23，列Sale_list有下列值：233.7, 141.78, 22.38, 136.92, 139.28。你可以看出这些值是如何以当前周为中心的。前两个来至于最近前两周的sales列。接着是当前周的sales，再是接下来两周的值。
Listing 9-17.? Iteration
?1??? select year, week,sale, sale_list
?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 ( cast(' ' as varchar2(50) ) sale_list, sale)
?8??????? rules?? iterate (5)? (
?9?????????? sale_list [ year, week ] order by year, week =
10??????????????? sale [cv(year), CV(week)- ITERATION_NUMBER +2 ]?????? ||
11??????????????? case when iteration_number =0 then '' else ', '? end? ||
12??????????????? sale_list [cv(year) ,cv(week)]
13???? )
14*??? order by year, week
?YEAR WEEK?????? SALE SALE_LIST
----- ---- ---------- --------------------------------------------------
?2001?? 20???? 118.03???? 22.37, , 118.03, 233.7, 141.78
?2001?? 21???? 233.70???? , 118.03, 233.7, 141.78, 22.38
?2001?? 22???? 141.78???? 118.03, 233.7, 141.78, 22.38, 136.92
?2001?? 23????? 22.38????? 233.7, 141.78, 22.38, 136.92, 139.28
?2001?? 24???? 136.92???? 141.78, 22.38, 136.92, 139.28,
?2001?? 25???? 139.28???? 22.38, 136.92, 139.28, , 94.48?

PRESENTV and NULLs
??? If a rule is accessing a non-existent row, the rule will return a null value. Notice that in the output of
Listing 9-17, Sale_list column in the first row has two commas consecutively. The reason is that the
row for the week=19 does not exist in the data, so accessing that non- existent cell returns a null value.
You can correct this double comma issue using a function to check for cell existence using a PRESENTV
function. This function accepts three parameters and the syntax for the function is:
???? 如果规则访问一非存在行，规则将返回null值。注意列表9-17的输出，第一行的Sale_list列有两个逗号连在一起。原因是行week=19不存在数据，这样访问非存在行返回null值。你可以纠正这个双逗号问题，使用一个函数检查单元格的存在，用PRESENTV函数。这个函数接收3个参数，函数句法如下：
PRESENTV (cell_reference, expr1, expr2)
?
??? If? cell_reference references an existing cell, then the PRESENTV function returns expr1 . If the
Cell_reference references a non-existing cell, then the second argument expr2 is returned. In Listing
9-18, line 10 performs this existence check on the Sale column for the year and week combination
using a clause? sale [cv(year), CV(week)-iteration_number + 2 ]. If the cell exists, then the function
adds the value of the cell and comma to the returned string (lines 11 to 13). If the cell does not exist, the function returns the Sale_list column without altering the string (line 14). This solution eliminates the
double comma in the Sale_ list column value.
???? 如果cell_reference引用存在的单元格，则PRESENTV函数返回expr1。如果cell_reference引用非存在单元格，则第二个参数expr2返回。在列表9-18，行10对year和week的组合在Sale列上执行存在性检查，使用子句sale [cv(year), CV(week)-iteration_number + 2 ]。如果单元格存在，则函数加单元格值和逗号到返回的串(行11到13)。如果单元格不存在，则函数返回Sale_list列不改变串(行14)。这个解决方案消除Sale_list列值中的双逗号。
Listing 9-18.? Iteration and presntv
?1???? select year, week,sale, sale_list
?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 ( cast(' ' as varchar2(120) ) sale_list, sale, 0 tmp)
?8??????? rules? iterate (5) (
?9?????????? sale_list [ year, week ] order by year, week =
10??????????????? presentv ( sale [cv(year), CV(week)-iteration_number + 2 ],
11????????????????????? sale [cv(year), CV(week)-iteration_number +2 ]????????? ||
12????????????????????????? case when iteration_number=0 then '' else ', ' end? ||
13????????????????????????? sale_list [cv(year) ,cv(week)]? ,
14????????????????????? sale_list [cv(year) ,cv(week)] )
15???? )
16*???? order by year, week
?YEAR WEEK? SALE???? SALE_LIST
-----???? ----???? ----------???? --------------------------------------------------
?2001?? 20???? 118.03???? 22.37, 118.03, 233.7, 141.78
?2001?? 21???? 233.70???? 118.03, 233.7, 141.78, 22.38
?2001?? 22???? 141.78???? 118.03, 233.7, 141.78, 22.38, 136.92
?...
?2001?? 29???? 116.85????? 94.48, 116.85, 162.91, 92.21
?
??? The PRESENTNNV function is similar to PRESENTV,? but provides the additional ability to differentiate
between references to non-existent cells and null values in existing cells. The syntax for the function
PRESENTNNV is?
??? PRESENTNNV 函数相似于PRESENTV，但是提供而外的能力区别非存在的单元格与存在的单元格中的null值。函数PRESENTNNV 的句法如下：
PRESENTNNV (cell_reference, expr1, expr2).
?
??? If the first argument? cell_reference references an existing cell and if that cell contains non-null
value, then the first argument expr1? is returned, or else the second argument? expr2? is returned. In
contrast, the? PRESENTV function checks for just the existence of a cell, whereas the? PRESENTNNV function checks for both the existence of a cell and Null values in that cell. Table 9-1 lists shows the values returned from these two functions in four different cases.?
??? 如果第一个参数cell_reference引用一个存在的单元格且如果单元格包含非null值，则第一个参数expr1返回，否则第二个参数expr2返回。相比较而言，PRESENTV函数只检查某个单元格的存在性，而PRESENTNNV函数对单元格的存在性和单元格的Null值都做检查。表9-1列出这两个函数在四种情况下的返回值。
Table 9-1.? Presentv and presentnnv Comparison

?