使用Partitioned Outer Join实现稠化报表_PART2

使用PARTITIONED OUTER JOIN的过程如图所示：

图1-1

上面语句的实现的功能就是3个外连接的UNION ALL结果，其他复杂的数据稠密化以此类推。其实10g的MODEL也可以实现数据填充，但是MODEL语句比较复杂，比如上面可以用MODEL简单改写为：

--只考虑2008年前3月，如需详细学习MODEL子句，请参考相关文档，MODEL很强大，可以实现很多复杂功能
SELECT years,months,product_name,NVL(sales,0)
FROM t
MODEL
PARTITION BY (product_name)
DIMENSION BY (years,months)
MEASURES(sales)
RULES (sales[2008, FOR months IN (1,2,3)] = sales[2008,CV()])
ORDER BY 1,2,3;

如果是多维或其它复杂情况的改写，会很麻烦，对于数据稠化建议使用使用10g的PARTITIONED OUTER JOIN。

如果你不仔细地研究语法结构，那么可能写的语句不报错，但是结果却不是正确的，当然也有可能出错，比如上面的RIGHT JOIN改写为：

SELECT m.years,m.months,t.product_name,NVL(t.sales,0) sales
FROM t
RIGHT JOIN
(SELECT DISTINCT years,months FROM t) m
PARTITION BY (t.product_name)
ON t.years = m.years 
AND t.months = m.months
ORDER BY 1,2,3;

这个语句不会报错，但是结果：

YEARS MONTHS PRODUCT_NAME SALES
---------- ---------- -------------------- ----------
2008 1 A 1000
2008 1 B 1500
2008 2 A 2000
2008 2 B 3000
2008 2 C 1000
2008 3 A 3000
已选择6行。

将本来应该放在表t后面的PARTITION BY移到了m后面，没有实现填充缺失行的目的，原因是ORACLE对这种语法结构不会按照PARTITIONED OUTER JOIN实现填充行的目的进行支持，看下执行计划就明白了，计划如下：

执行计划
----------------------------------------------------------
Plan hash value: 3441530730
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 432 | 9 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 6 | 432 | 9 (34)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 6 | 432 | 8 (25)| 00:00:01 |
| 3 | VIEW | | 6 | 156 | 4 (25)| 00:00:01 |
| 4 | HASH UNIQUE | | 6 | 156 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T | 6 | 156 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T | 6 | 276 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."MONTHS"(+)="M"."MONTHS" AND
"T"."YEARS"(+)="M"."YEARS")

上面这个语句的计划和去掉PARTITION BY的语句计划完全一致，没有原来的第3步和第8步的PARTITION操作。为什么我这么强调PARTITION OUTER JOIN的语法结构呢?因为如果不理解这个语法结构，必然会导致不正确的结果，如果理解了这个语法结构，那么一切就变得很简单，其他改写的错误类似，还有一种错误是直接报错，如下:

DINGJUN123>SELECT m.years,m.months,t.product_name,NVL(t.sales,0) sales
2 FROM t
3 LEFT JOIN
4 (SELECT DISTINCT years,months FROM t) m
5 PARTITION BY (t.product_name)
6 ON t.years = m.years
7 AND t.months = m.months
8 ORDER BY 1,2,3;
PARTITION BY (t.product_name)
*
第 5 行出现错误:
ORA-00904: : 标识符无效

为什么会出错，因为这里是LEFT JOIN，那么基表是t，在m后面使用PARTITION BY并且引用了t的字段，那么是引用不到的，所以出错，如果是RIGHT JOIN，则正确，但是又不符合语法结构，导致PARTITION BY白写。只有前面说的两种正确写法才是对的，特别在多表连接以及多维度填充缺失行的时候一定要注意PARTITION BY的位置和其引用的字段有关，一定要放在紧跟要引用的对象后面，然后根据语法结构规则使用LEFT JOIN还是RIGHT JOIN，否则要么不正确，要么错误。


OK，现在已经实现了数据稠密化工作，那么稠密化工作的意义何在呢?比如要做按时间序列表示销售情况波动图，要求每个产品每个时间序列上都有数据，不产生gap值，是很有意义的，也可以进一步对数据进行明细分析，比如使用分析函数分析对比当月和上月的销售情况，决策人员看到所有产品所有时间点的数据，这样可以很好地做决策，如果你给他缺失行的分析报表，他怎么能看到某个时间点某个产品没有销售呢，如何分析造成此情况的原因呢？下面就做一个使用分析函数对比销售情况的报表：

--使用分析函数对比产品当前月销售与上月销售情况，计算递增量，可以对决策支持提供很清晰的报表
SELECT years,months,product_name,sales,
sales-NVL(LAG(sales) 
OVER(PARTITION BY product_name 
ORDER BY years,months)
,0) add_last_sales
FROM
(
SELECT m.years,m.months,t.product_name,NVL(t.sales,0) sales
FROM 
(SELECT DISTINCT years,months FROM t) m
LEFT JOIN
t PARTITION BY (t.product_name)
ON t.years = m.years 
AND t.months = m.months
);

结果如下：

PRODUCT_NAME DONE_DATE SALES RECENT_SALES
-------------------- ---------- ---------- ------------
A 2010-07-01 1000 1000
A 2010-07-02 1000
A 2010-07-03 1000
A 2010-07-04 1000
A 2010-07-05 2000 2000
B 2010-07-01
B 2010-07-02 3000 3000
B 2010-07-03 3000
B 2010-07-04 4000 4000
B 2010-07-05 4000
已选择10行。

现在实现了上述需求，还有一行recent_sales为空，因为它是产品B的第1个时间点的数据。使用LAST_VALUE+IGNORE NULLS结合PARTITIONED OUTER JOIN实现上述报表是很常见的，实现报表数据稠密化思想主要就是上面这些，当然MODEL语句也可以实现，但是没有PARTITIONED OUTER JOIN简单。

1.3 Partitioned Outer Join总结 
传统方法填补缺失数据，经常需要使用笛卡尔积构造中间结果，然后与原始数据外连接，往往性能不是很好，而且SQL比较复杂，10g提供的PARTITIONED OUTER JOIN的语法简单，可以高效地实现报表数据稠化，使用PARTITIONED OUTER JOIN一定要掌握语法结构中的2种结构：首先确定分区键，然后确定使用LEFT JOIN还是RIGHT JOIN，此语法结构对FULL JOIN不支持。另外MODEL等语法也可以实现类似的功能，但是与PARTITIONED OUTER JOIN相比，就复杂多了，为了很好地使用PARTITIONED OUTER JOIN实现数据稠化，一定要分析清楚需求，然后根据本部分说的使用步骤以及一些注意点，比如如何高效地使用DISTINCT构造结果集(常自己构造或从关联表获取)，这样才能正确高效地实现报表数据的稠化。