《Pro Oracle SQL》Chapter 10 Subquery Factoring --10.1 Standard Usage

 Subquery Factoring 子查询分解 (page 305)

Jared Still
    You may not be familiar with the term subquery factoring .  Prior to the release of Oracle 11gR2, the
official Oracle documentation barely mentions it, providing just a brief synopsis of its use, a couple of
restrictions, and a single example.  If I instead refer to the WITH  clause of the SELECT statement, you will probably know immediately what I mean as this term is more recognizable.  Both terms will be used in
this chapter.  
    你可能不熟悉术语“子查询分解”。在Oracle11gR2发行之前，Oracle官方文档很少提及它，只是提供了它使用的简要大纲 ，一些限制条件，和一个例子。如果我是指SELECT语句的WITH子句，你可能马上明白我的意思了，因为这个术语很熟悉。在本章中两个术语都用到了。
    With the release of Oracle 11gR2 (version 11.2), the WITH clause was enhanced with the ability to
recurse; that is, the factored subquery is allowed to call itself within some limitation.  The value of this
may not be readily apparent.  If you have used the  CONNECT BY clause to create hierarchical queries, you will appreciate that recursive subqueries allow the same functionality to be implemented in an ANSI
standard format.
    随着Oracle11gR2的发布(版本11.2)，WITH子句增强了递归的能力，就是，分解的子查询允许在某些限定内调用自身。这点的价值可能不是那么明显。如果你使用CONNECT BY子句创建等级查询，你就会赞赏递归子查询，允许用ANSI标准格式执行相同的功能。
    If the term subquery factoring  is not known to you, perhaps you have heard of the ANSI Standard
term  common table expression   (commonly called CTE).  Common table expressions were first specified
in the 1999 ANSI SQL Standard.  For some reason, Oracle has chosen to obfuscate this name.  Other
database vendors refer to common table expressions, so perhaps Oracle chose subquery factoring just to
be different. 
    如果数据子查询分解不为你熟知，可能你听说过ANSI标准数据公有表表达式 (一般称之为CTE)。公有表表达式首次在1999 ANSI SQL标准中规定。由于某些原因，Oracle选择混淆 这个名字。其他的数据厂商指公有表表达式，因此可能Oracle选择子查询分解只是为了有所区别。

 

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

PIVOT参考：http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html

http://www.cnblogs.com/tracy/archive/2011/08/24/2151613.html

Standard Usage   标准用法
    One of the most useful features of the WITH clause when it was first introduced was to cleanup complex SQL queries.  When a large number of tables and columns are involved in a query, it can become difficult to follow the flow of data through the query.  Via the use of subquery factoring, a query can be made more understandable by moving some of the complexity away from the main body of the query.  
    WITH子句最有用的特性之一就是它的引入首先清理复杂的SQL查询。当查询中涉及大量的表和列，把握查询的数据的流向就变得困难了。通过使用子查询分解，把一些复杂部分移出查询主体，使得查询变得更好理解。
    The query in Listing 10-1 generates a crosstab report using the PIVOT  operator.  The formatting helps
make the SQL somewhat readable, but there is quite a bit going on here. The innermost query is creating
a set of aggregates on key sales columns, while the next most outer query simply provides column names
that are presented to the  PIVOT  operator, where the final values of sales by channel and quarter for each product are generated.
    在列表10-1中的查询生成一交叉表报告使用PIVOT操作符。格式化有助于SQL更可读，但是还是很长。最里面的查询创建一组聚合在键sales 列上，而次外层查询简单的提供列名给PIVOT操作符，生成每款产品(product)按渠道(channel)和季度(quarter)分的销售 (sales)的最终值。
Listing 10-1. Crosstab without Subquery Factoring    没有子查询分解的交叉表
select *
from (
   select /*+ gather_plan_statistics */
      product
      , channel
      , quarter
      , country
      , quantity_sold
   from
   (
      select
         prod_name product
         , country_name country
         , channel_id channel
         , substr(calendar_quarter_desc, 6,2) quarter
         , sum(amount_sold) amount_sold
         , sum(quantity_sold) quantity_sold
      from
         sh.sales
         join sh.times on times.time_id = sales.time_id
         join sh.customers on customers.cust_id = sales.cust_id
         join sh.countries on countries.country_id = customers.country_id
         join sh.products on products.prod_id = sales.prod_id
      group by
        prod_name
        , country_name
        , channel_id
        , substr(calendar_quarter_desc, 6, 2)
   )
) PIVOT (
   sum(quantity_sold)
   FOR (channel, quarter) IN
   (
      (5, '02') AS CATALOG_Q2,
      (4, '01') AS INTERNET_Q1,
      (4, '04') AS INTERNET_Q4,
      (2, '02') AS PARTNERS_Q2,
      (9, '03') AS TELE_Q3
   )
)
order by product, country;
 
    Now let’s use the  WITH clause to break the query in byte-sized chunks that are easier to comprehend.  The SQL has been rewritten in Listing 10-2 using the WITH clause to create three subfactored queries, named sales_countries ,  top_sales , and  sales_rpt .  Notice that both the top_sales  and sales_rpt  subqueries are referring to other subqueries by name, as if they were a table or a view. By choosing names that make the of each subquery easy to follow, the readability of the SQL is improved.  For instance, the subquery name  sales_countries  refers to the countries in which the sales took place, top_sales  collects the sales data, and the  sales_rpt  subquery aggregates the data.  The results of the sales_rpt  subquery are used in the main query which answers the question, “What is the breakdown of sales by product and country per quarter?”    If you were not told the intent of the SQL in Listing 10-1, it would take some time to discern its purpose; on the other hand, the structure of the SQL in Listing 10-2 with subfactored queries makes it easier to understand the intent of the code.
    现在让我们使用WITH子句将查询分解成字节大小的块，容易理解。在列表10-2中SQL用WITH语句重写，创建了三个子查询，名为 sales_countries ,  top_sales , 和sales_rpt。注意子查询top_sales 和sales_rpt 两者都按名称引用了其它的子查询，好像它们是一个表或者视图。通过选择适当的名称使得每个子查询容易理解，SQL的可读性也提高了。例如，子查询名为 sales_countries 指的是销售发生的国家，top_sales收集销售额数据，而sales_rpt子查询聚集数据。子查询sales_rpt 的结果用于主查询回答问题，“每季度按产品和国家分的销售额明细是什么？” 如果你没有告知列表10-1中SQL的意图，将花费好些时间领悟它的意思；另一方面，在列表10-2中的SQL结构带有分解的子查询使得它的代码更容易被理解。

    In addition, the statements directly associated with the PIVOT  operator are in the same section of the SQL statement at the bottom, further enhancing readability.
   再者，语句直接关联了PIVOT操作符，在SQL语句的底部相同的段。进一步增加了可读性。
Listing 10-2.  Crosstab with Subquery Factoring    带子查询因子的交叉表
with  sales_countries as (
   select /*+ gather_plan_statistics */
      cu.cust_id
      , co.country_name
   from  sh.countries co, sh.customers cu
   where cu.country_id = co.country_id
),
top_sales  as (
   select
      p.prod_name
      , sc.country_name
      , s.channel_id
      , t.calendar_quarter_desc
      , s.amount_sold
      , s.quantity_sold
   from
      sh.sales s
      join sh.times t on t.time_id = s.time_id
      join sh.customers c on c.cust_id = s.cust_id
      join  sales_countries sc on sc.cust_id = c.cust_id
      join sh.products p on p.prod_id = s.prod_id
),
sales_rpt  as (
   select
      prod_name product
      , country_name country
      , channel_id channel
      , substr(calendar_quarter_desc, 6,2) quarter
      , sum(amount_sold) amount_sold
      , sum(quantity_sold) quantity_sold
   from top_sales 
   group by
      prod_name
      , country_name
      , channel_id
      , substr(calendar_quarter_desc, 6, 2)
)
select * from
(
  select product, channel, quarter, country, quantity_sold
  from  sales_rpt 
) pivot (
   sum(quantity_sold)
    for (channel, quarter) in
   (
      (5, '02') as catalog_q2,
      (4, '01') as internet_q1,
      (4, '04') as internet_q4,
      (2, '02') as partners_q2,
      (9, '03') as tele_q3
   )
)
order by product, country;
 
     While this is not an extremely complex SQL example, it does serve to illustrate the point of how the
WITH clause can be used to make a statement more readable and easier to maintain. Large complex
queries can be made more understandable by using this technique.
    虽然这不是一个极其复杂的SQL例子，但是它示例出WITH语句如何能够使得语句更可读和容易维护。大而复杂的查询可通过使用这种技术变得更为可理解。