作者：丁俊(dingjun123)
背景介绍：在数据库表中，存储的数据经常是稀疏数据(sparse data)，而不是稠密数据(dense data)。
先来了解一下什么是稀疏数据，比如一个产品销售情况表(比如有产品名、销售时间(精确到年月)、销售量3个列)，假设某个时间某些产品它没有销售，一般也不会将这些产品的销售量存储为0，而是不存储，这样在产品销售情况表中就会产生很多缺失的行(gap rows)，导致的结果就是特定产品销售数据按时间维度进行排序，是不连续的，或者说此产品销售在时间序列上是有缺失的。顾名思义，稠密数据是相对于稀疏数据来说的，还是用上面的假设说明，也就是说产品在某个时间没有销售，也必须存储此产品销售情况，销售量置0存储，这样对某个特定产品来说它在时间序列就是连续的，但是事实经常不是如此，所以才有将稀疏数据稠密化的过程，数据稠密化在数据仓库应用中很常见。
当然销售情况表只是一个典型的情况，在实际应用中，有各种各样的缺失数据情况。如果决策者看销售情况统计表，他可不希望有的产品按时间序列断断续续，而应该给他提供时间序列连续的分析报表，他可能需要看到每个产品每个时间的销售情况，就算在某个时间没有销售，也必须置0，这样的报表对决策者才有意义，而且可以进行更细粒度的分析，比如使用分析函数对每个产品按年月汇总计算销售偏移量，这样可以方便对比每个产品每个月的销售情况，从而为决策支持提供强大保障。
为了实现将稀疏数据转为稠密数据，Oracle10g提供了Partitioned Outer Join语法，和一般的OUTER JOIN类似(但是不支持Full Outer Join，只支持Left和Right两种)，只不过增加了PARTITION BY的语法，根据PARTITION BY将表逻辑分区，然后对每个分区进行OUTER JOIN，这样就可以达到填补缺失行，实现数据稠密化的目的，也相当于对每个分区里的数据OUTER JOIN后进行UNION操作，理解这个很重要，否则经常不知道到底是哪个表哪些列该分区，不知道到底是用LEFT JOIN还是用RIGHT JOIN，在后面的例子会详细分析这个语法如何使用。
1.1 Partitioned Outer Join语法
Partitioned Outer Join语法如下：
|
SELECT .....
FROM table_reference
PARTITION BY (expr [, expr ]... )
RIGHT OUTER JOIN table_reference
SELECT .....
FROM table_reference
LEFT OUTER JOIN table_reference
PARTITION BY (expr [, expr ]... )
|
Partitioned Outer Join语法很简单，也就是在JOIN的表后面ON条件之前加入PARTITION BY语句即可。上面只列出了最简单的两表(内联视图，视图等其他结果集)连接，多个对象的连接类似，其他复杂的语法结构省略了，语法结构上PARTITION BY是可以放在任何合法连接对象后面的，而且和一般的PARTITION BY没有区别，可以有多个分区列(表达式)，然后用外连接，注意一定要搞清楚是用LEFT JOIN还是用RIGHT JOIN，比如第1个语法结构在JOIN之前的对象使用了PARTITION BY，那么就是对第1个对象填充缺失数据，所以必须用RIGHT JOIN，第2个语法结构类似。
当然也可以直接用JOIN，不用OUTER JOIN，但是这样无法填充缺失数据，没有意义，另外注意不能使用86的外连接语法+，这是不行的，必须使用92语法。一般来说，根据需求确定PARTITION BY的键值，PARTITION BY语句要紧跟需要分区的对象后面，然后根据PARTITION BY的位置决定用LEFT JOIN还是RIGHT JOIN，否则可能会出错或获得不正确的结果。
1.2 Partitioned Outer Join实例
本节主要从相关实例中研究Partitioned Outer Join的使用，主要实例有填充一维缺失数据、填充多维缺失数据、填充数据到清单表中等。例子中的建表等语句请参考代码poj.sql。
1)填充一维缺失数据
t表是一个产品销售情况表，数据如下：
DINGJUN123>SELECT * FROM t
2 ORDER BY years,months,product_name;
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行。
|
上面的表数据是很简单的，在实际应用中，这个数据可能是语句的中间结果。从结果上可以看到，有2008年1、2、3这3个月的销售数据，但是有些产品的销售数据在某些月份是缺失的，比如2008年1月产品C就没有数据。现在需要一个报表，能够填充所有产品对应2008年前3月缺失的数据，销售字段sales置0，要实现这样的报表，如何做呢?
先来看下传统做法：既然填充每个产品对应月份缺失的数据，那么肯定需要构造一个结果集存储了每个产品每个时间对应的数据，这样再与原始表外连接，则可以达到填充缺失数据的目的，为了实现这个目的，很容易想到需要将表中对应的时间year、month与产品做笛卡尔积(每个部分数据都是唯一的，是这样的数据做笛卡尔积)，生成每个产品每个时间的结果数据，然后与原始表外连接。下面用SQL实现：
--1.WITH子句将时间和product_name固定下来，然后在查询中生成笛卡尔积，这样就有每个产品的所有时间段数据，当然
--这里的WITH子句也可以直接生成笛卡尔积
--2.笛卡尔积和原始表t做外连接，从而实现数据稠密化过程，当然这里可以使用t RIGHT JOIN …这样就不需要写那么
--多+号了。
--这里的WITH子句从原始表中DISTINCT,如果原始表很大，效率不好，实际应用经常是从其他关联表获取或自己构造
WITH
year_month AS
(SELECT DISTINCT years,months FROM t),
product AS
(SELECT DISTINCT product_name FROM t)
SELECT m.years,m.months,m.product_name,NVL(t.sales,0) sales
FROM t,
(SELECT years,months,product_name
FROM year_month,product) m
WHERE t.years(+) = m.years
AND t.months(+) = m.months
AND t.product_name(+) = m.product_name
ORDER BY 1,2,3;
--按照上面说的改进的SQL，WITH直接生成笛卡尔积，然后使用SQL92新的外连接语法，省略了很多+号，更容易理解WITH
m AS
(SELECT years,months,product_name
FROM
(SELECT DISTINCT years,months FROM t),
(SELECT DISTINCT product_name FROM t)
)
SELECT m.years,m.months,m.product_name,NVL(t.sales,0) sales
FROM t
RIGHT JOIN
m
ON t.years = m.years
AND t.months = m.months
AND t.product_name = m.product_name
ORDER BY 1,2,3;
|
传统填充缺失数据，往往就要通过笛卡尔积构造完整数据集，然后与原始表外连接。根据上面的SQL，这个结果应该是生成所有产品所有年月的销售数据，如果原始表中没有，则对应缺失年月的数据为0，执行上面的SQL结果为：
YEARS MONTHS PRODUCT_NAME SALES
---------- ---------- -------------------- ----------
2008 1 A 1000
2008 1 B 1500
2008 1 C 0
2008 2 A 2000
2008 2 B 3000
2008 2 C 1000
2008 3 A 3000
2008 3 B 0
2008 3 C 0
已选择9行。
|
现在填充了3行缺失数据，实现了所有产品对应2008年前3月时间序列上的稠密化报表目的，你是否发现到传统做法比较复杂，这里是很简单的一维缺失数据的填充，如果是多维缺失数据填充呢?在实际应用中SQL经常很复杂，这个销售表t也许都是SQL的中间结果，那么这样的做法需要通过笛卡尔积生成所有组合情况，性能可能不好，而且SQL比较复杂。
下面看10g对填充数据专门做的改进，使用PARTITIONED OUTER JOIN实现数据稠密化工作，更加简单，而且往往性能往往要比传统做法要好。 通过前面对PARTITUONED OUTER JOIN的分析以及传统实现数据稠密化的方法，使用PARTITIONED OUTER JOIN只需要对产品进行分区然后和所有时间外连接，则可以补全缺失数据，如下：
--这里的m是从原始表中获取的，实际应用中一般自己构造或从其他关联表获取，从原始表获取DISTINCT,可能效率不好。
SELECT m.years,m.months,t.product_name,NVL(t.sales,0) sales
FROM t
PARTITION BY (t.product_name)
RIGHT JOIN
(SELECT DISTINCT years,months FROM t) m
ON t.years = m.years
AND t.months = m.months
ORDER BY 1,2,3;
|
一定要理解PARTITIONED OUTER JOIN的两种语法结构，这里的PARTITION BY是紧跟在表t后面的，相当于对每个按product_name分区的每个分区内的行和中间结果m外连接，这样就能补起数据了，相当于每个按product_name划分的行与m外连接的UNION ALL结果，通过这个例子，就可以很好地理解PARTITIONED OUTER JOIN的使用，这样你就能正确用多种方法进行改写了。这个语句的结果和上面的一致，不再列出。如果你理解了上面说的话，就可以使用LEFT JOIN来改写：
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
ORDER BY 1,2,3;
|
执行结果和上面的RIGHT JOIN完全一样的，为什么上面又变成LEFT JOIN了呢?原因是现在t PARTITION BY移到JOIN后面了，当然要左连接所有的时间才可以填充缺失数据，所以要使用第2种语法结构。下面看下此语句的执行计划：
执行计划
----------------------------------------------------------
Plan hash value: 1692607762
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 10 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 46 | 10 (40)| 00:00:01 |
| 2 | VIEW | | 1 | 46 | 9 (34)| 00:00:01 |
| 3 | MERGE JOIN PARTITION OUTER| | 1 | 72 | 9 (34)| 00:00:01 |
| 4 | SORT JOIN | | 6 | 156 | 5 (40)| 00:00:01 |
| 5 | VIEW | | 6 | 156 | 4 (25)| 00:00:01 |
| 6 | HASH UNIQUE | | 6 | 156 | 4 (25)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T | 6 | 156 | 3 (0)| 00:00:01 |
|* 8 | SORT PARTITION JOIN | | 6 | 276 | 4 (25)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T | 6 | 276 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("T"."YEARS"="M"."YEARS")
filter("T"."MONTHS"="M"."MONTHS" AND "T"."YEARS"="M"."YEARS")
|
PARTITIONED OUTER JOIN的效果体现在第3到第8步，其中第8步就是将数据排序然后放入分区内，第3步就是外连接产生填充后的结果集，当然这里的MERGE JOIN可以为NESTED LOOP JOIN,也可以使用hint,比如use_nl(m,t)来让它走NESTED LOOP PARTITION OUTER。这个执行计划会与后面改写的语句执行计划做对比，如果没有第3步和第8步，那么PARTITIONED OUTER JOIN是不起作用的。
