《Pro Oracle SQL》Chapter 9 -- 9.11 Subquery Factoring

Subquery Factoring    子查询分解     (page 303)
    In a business setting, requirements are complex and multiple levels of aggregation are often needed.
When writing complex queries, you can often combine subquery factoring with the Model clause to
prevent a SQL statement from becoming unmanageably complex.
    在实际的业务环境中，需求是复杂的而多层次聚合经常是必须的。当写复杂查询，通常把子查询分解与Model子句组合，防止SQL语句变得不可管理的复杂。
    Listing 9-34 provides one such example. Two Model clauses are coded in the same SQL statement.
The first Model clause is embedded within a view that is the result of a subquery being factored into the
WITH clause. The main query uses that view to pivot the value of the Sale column from the prior year.
The output shows that prior week sales are pivoted into the current week’s row.
    列表9-34提供了这样一个例子。两个Model子句在写在同一个SQL语句中。第一个Model子句嵌入一视图中，是一子查询被分解入WITH子句结果中。主查询用视图旋转来自前一年的Sale列值。输出显示前一周的销售额旋转进了当前周的行中。
Listing 9-34.  More Indexing with SQL Access in Mind    多索引用于SQL访问的思考
with t1 as (
  select  product, country, year, week, inventory, sale, receipts
  from sales_fact sf
  where country in ('Australia') and product='Xtend Memory'
  model return updated rows
  partition by (product, country)
  dimension by (year, week)
  measures ( 0 inventory , sale, receipts)
  rules automatic order(
       inventory [year, week ] order by year, week =
                                 nvl(inventory [cv(year), cv(week)-1 ] ,0)
                                  - sale[cv(year), cv(week) ] +
                                  + receipts [cv(year), cv(week) ]
   )
)
select product, country, year, week , inventory, sale,receipts,  prev_sale
from t1
model return updated rows
partition by (product, country)
dimension by (year, week)
measures (inventory, sale, receipts,0 prev_sale)
rules sequential order (
  prev_sale [ year, week ] order by year, week =
    nvl (sale [ cv(year) -1, cv(week)],0 )
)
order by 1,2,3,4
/
 
PRODUCT      COUNTRY      YEAR WEEK  INVENTORY       SALE   RECEIPTS  PREV_SALE
------------ ---------- ------ ---- ---------- ---------- ---------- ----------
Xtend Memory Australia    1998   50     11.504      28.76     40.264          0
...
Xtend Memory Australia    2000   50     12.714      21.19     25.428          0
...
Xtend Memory Australia    2001   50     11.775      23.14     32.396      21.19

 

 

注： Factoring 在数学上的意思是“因式分解” ， 以后均简称“分解”。pivot本意是“绕...旋转”，但是很多书将这里的PIVOT翻译成“转置”，使人同线性代数中的转置(transpose)混淆,实际上两个不同的概念。本人喜欢直接称之为"旋转"。

 

Summary    总结
     I can’t stress enough the importance of thinking in terms of sets when writing SQL statements. Many
SQL statements can be rewritten concisely using the Model clause discussed in this chapter. As an
added bonus, rewritten queries such as Model or analytic functions can perform much better than
traditional SQL statements. A combination of subquery factoring, Model, and analytic functions
features can be used effectively to implement complex requirements. 
    我 必须强调依据集合思想编写SQL语句的重要性 。许多SQL语句能用本章讨论的Model子句精简的重写。作为一个额外的好处，用Model或者功能函数重写的查询将比传统SQL语句执行的更好。与子查询因式分解结合，Model和分析函数的特性能高效的用于执行复杂需求。