报表查询太慢？那是你不懂稠化报表！

你还在用传统方法痛苦地填补缺失数据？赶紧点开本文查看吧。。。...





在数据库表中，存储的数据经常是稀疏数据(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，在后面的例子会详细分析这个语法如何使用。

目录

• Partitioned Outer Join语法

• Partitioned Outer Join实例
• Partitioned Outer Join总结

1

Partitioned Outer Join语法



Partitioned Outer Join语法如下：



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，否则可能会出错或获得不正确的结果，如果要起到分区外连接的效果，必须牢牢按照上面两种写法来（PARTITION端要紧跟对应表，并且是非基表），后面会详细分析其他写法的问题。

2

Partitioned Outer Join实例



本节主要从相关实例中研究Partitioned Outer Join的使用，主要实例有填充一维缺失数据、填充多维缺失数据、填充数据到清单表中等。例子中的建表等语句请参考代码poj.sql。

poj.sql

1) 填充一维缺失数据

t表是一个产品销售情况表，数据如下：



上面的表数据是很简单的，在实际应用中，这个数据可能是语句的中间结果。从结果上可以看到，有2008年1、2、3这3个月的销售数据，但是有些产品的销售数据在某些月份是缺失的，比如2008年1月产品C就没有数据。现在需要一个报表，能够填充所有产品对应2008年前3月缺失的数据，销售字段sales置0，要实现这样的报表，如何做呢?

先来看下传统做法：既然填充每个产品对应月份缺失的数据，那么肯定需要构造一个结果集存储了每个产品每个时间对应的数据，这样再与原始表外连接，则可以达到填充缺失数据的目的，为了实现这个目的，很容易想到需要将表中对应的时间year、month与产品做笛卡尔积(每个部分数据都是唯一的，是这样的数据做笛卡尔积)，生成每个产品每个时间的结果数据，然后与原始表外连接。下面用SQL实现：





传统填充缺失数据，往往就要通过笛卡尔积构造完整数据集，然后与原始表外连接。根据上面的SQL，这个结果应该是生成所有产品所有年月的销售数据，如果原始表中没有，则对应缺失年月的数据为0，执行上面的SQL结果为：



现在填充了3行缺失数据，实现了所有产品对应2008年前3月时间序列上的稠密化报表目的，你是否发现到传统做法比较复杂，这里是很简单的一维缺失数据的填充，如果是多维缺失数据填充呢?在实际应用中SQL经常很复杂，这个销售表t也许都是SQL的中间结果，那么这样的做法需要通过笛卡尔积生成所有组合情况，性能可能不好，而且SQL比较复杂。

下面看10g对填充数据专门做的改进，使用PARTITIONED OUTER JOIN实现数据稠密化工作，更加简单，而且往往性能往往要比传统做法要好。通过前面对PARTITUONED OUTER JOIN的分析以及传统实现数据稠密化的方法，使用PARTITIONED OUTER JOIN只需要对产品进行分区然后和所有时间外连接，则可以补全缺失数据，如下：



一定要理解PARTITIONED OUTER JOIN的两种语法结构，这里的PARTITION BY是紧跟在表t后面的，相当于对每个按product_name分区的每个分区内的行和中间结果m外连接，这样就能补起数据了，相当于每个按product_name划分的行与m外连接的UNION ALL结果，通过这个例子，就可以很好地理解PARTITIONED OUTER JOIN的使用，这样你就能正确用多种方法进行改写了。这个语句的结果和上面的一致，不再列出。如果你理解了上面说的话，就可以使用LEFT JOIN来改写：



执行结果和上面的RIGHT JOIN完全一样的，为什么上面又变成LEFT JOIN了呢?原因是现在t PARTITION BY移到JOIN后面了，当然要左连接所有的时间才可以填充缺失数据，所以要使用第2种语法结构。下面看下此语句的执行计划：



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是不起作用的。使用PARTITIONED OUTER JOIN的过程如图所示：



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



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

如果你的分区外连接写法不遵守两种语法结构，那么可能写的语句不报错，但是结果却不是正确的（起不到分区效果，计划里没有PARTITION OUTER），当然也有可能出错，比如上面的RIGHT JOIN改写为：



上面语句不会报错，但是结果：



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



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



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

这种语法很奇怪，LEFT JOIN不可以将PARTITION BY放在非基表端，并且PARTITION BY里引用基表列（上面情况），但是可以把PARTITION BY放在基表端，引用非基表列。但是也起不到填充效果，RIFHT JOIN相反。



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



结果如下：



现在这个报表是不是很有意义了呢!决策者可以专门对add_last_sales

    关注 DBAplus社群