mysql稠化报表_使用Partitioned Outer Join实现稠化报表

转载自：http://www.itpub.net/thread-1332059-1-1.html

背景介绍：在数据库表中，存储的数据经常是稀疏数据(sparse data)，而不是稠密数据(dense data)。先来了解一下什么是稀疏数据，比如一个产品销售情况表(比如有产品名、销售时间(精确到年月)、销售量3个列)，假设某个时间某些产品它没有销售，一般也不会将这些产品的销售量存储为0，而是不存储，这样在产品销售情况表中就会产生很多缺失的行(gap rows)，导致的结果就是特定产品销售数据按时间维度进行排序，是不连续的，或者说此产品销售在时间序列上是有缺失的。顾名思义，稠密数据是相对于稀疏数据来说的，还是用上面的假设说明，也就是说产品在某个时间没有销售，也必须存储此产品销售情况，销售量置0存储，这样对某个特定产品来说它在时间序列就是连续的，但是事实经常不是如此，所以才有将稀疏数据稠密化的过程，数据稠密化在数据仓库应用中很常见。 当然销售情况表只是一个典型的情况，在实际应用中，有各种各样的缺失数据情况。如果决策者看销售情况统计表，他可不希望有的产品按时间序列断断续续，而应该给他提供时间序列连续的分析报表，他可能需要看到每个产品每个时间的销售情况，就算在某个时间没有销售，也必须置0，这样的报表对决策者才有意义，而且可以进行更细粒度的分析，比如使用分析函数对每个产品按年月汇总计算销售偏移量，这样可以方便对比每个产品每个月的销售情况，从而为决策支持提供强大保障。 为了实现将稀疏数据转为稠密数据，提供了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  A