使用Partitioned Outer Join实现稠化报表.docxVIP

使用Partitioned Outer Join实现稠化报表 ? 作者：丁俊(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_referencePARTITION BY (expr [, expr ]... )RIGHT OUTER JOIN table_referenceSELECT .....FROM table_referenceLEFT OUTER JOIN table_referencePARTITION 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表是一个产品销售情况表，数据如下：DINGJUN123SELECT * FROM t?2? ?? ???ORDER BY years,months,product_name;?YEARS? ???MONTHS PRODUCT_NAME? ?? ?? ?? ???SALES---------- ---------- -------