?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语句如何能够使得语句更可读和容易维护。大而复杂的查询可通过使用这种技术变得更为可理解。
?
?