- 论坛徽章:
- 0
|
改写FULL OUTER JOIN
说起数据库的表JOIN，一直是SQL语句开发中最为关注的重点，特别是在OLAP的DW领域。
关注JOIN主要关注JOIN的性能，一般来说数据库都会支持为不同场合不同的代价实现MERGE、HASH以及NESTED-LOOP等JOIN的实现方式。这个非常重要，但这个不是本文主题，以后我想好好的阐述这方面的内在以及优化，不过可能需要根据数据库的不同而分开描述，在此不必熬述。
今天要想说的是JOIN的类型。根据需求，一般来说JOIN有如下几种：
INNER JOIN、
LEFT OUTER JOIN、
RIGHT OUTER JOIN、
FULL OUTER JOIN、
CROSS JOIN。
首先第一种直译为内关联，需要匹配条件完全匹配，这个是最普通的关联，一般来说用的都是这个。对于优化来说，基本上是无法改写的。
第二是做文字直译为左外关联，一般也会说是左关联，如果是常用ORALCE的同志，你们看到的A.COLUMN1= B.COLUMN2(+)描述的就是左关联，当然这个不是标准SQL，是ORACLE PL/SQL自己的语法。它的含义是以左表为主表，匹配条件当右表没有匹配值时会以数据库的NULL值代替，这个实现的功能为主从表的关联。当两表之间存在外键约束但非主键约束时（亦即非强制依赖约束）时，应采用这种关联方式。
第三种直译当然是右关联，说白了，就是右表为主表，其他和左关联同，他的存在和左关联是同一价值，在非必须的情况，右关联尽量用左关联替代。因为右关联会让你的SQL读起来很难受，我们接受的先入为主，先左后右的表达方式，而且很多数据库对复杂关联时，据说对右关联的优化能力较差。
第四种直译为全关联。就是左右两表都是主表，非主从关系，这个时候匹配条件中存在左表有右表无，也同时存在右表有左表无的情况。这个时候你需要进行的就是全关联了。当然这个性能是比较差的，但当我们的需求的确如此的时候，我们也是必须去面对，本文想说的就是替代这种全关联的一种脚本改写，这种写法会对性能上有一定提升，当然是在某些情况和场合下。
第五种是交叉关联，也就是所谓的笛卡尔积。当你的关联不写条件就会出现这种情况，比如你要画方格呵呵，通过横、纵左表描述整个围棋棋盘的时候，你得到的数据行是你的左右两表行数的乘积。当然这种情况一般情况是不需要的，因为关联不用条件基本上意义不大，而且这种是比较恐怖，积数的数据扩充，一般你会受不了的，你受得了你的机器也受不了，当你发现你一个应该可以很快完成关联却怎么也出不来的时候，你要检查你的匹配条件是不是没有写或者错了产生笛卡尔积了。经常有人写条件A.COLUMN1 = A.COLUMN1来作A、B两表的关联条件，这个条件是无效的，是CROSS JOIN。值得注意哦，这种脚本如果出现了，要判编码者死刑的呵呵。
引申了不少，进入主题，来说改写FULL OUTER JOIN。首先我要申明的是我的改写是基于两表或者多表数据量差不多情况下，且为统计所用时，而且表越多，越能显示这种改写的优势。针对不同等量级的表之间的FULL OUTER JOIN，使用这种方式可能会让你的SQL变得晦涩而且性能没有得到很大提高，那就不值得了。
我使用的SQL为“UNION ALL + GROUP BY”的组合。如果我把这个SQL称之为精妙的时候，请同志们不要笑话我的自我满足呵呵。
我们来举例说明，在OLAP系统中，做报表经常会出现这样的一组需要你完成的指标：本期指标、上期指标、去年同期指标。我们今天就来用此例来说明，说明这一改写的精妙。
一般来说，我们会已经形成了如下列明细表格，表一：
月份 分公司 商品 销售额
200504 南京分公司 IBM笔记本 1,002,000
200504 合肥分公司 IBM笔记本 888,887
200504 南京分公司 HP笔记本 1,399,250
200504 合肥分公司 HP笔记本 1,010,222
…… …… …… ……
…… …… …… ……
200606 南京分公司 DELL笔记本 1,999,250
200606 合肥分公司 DELL笔记本 2,010,222
但我们最终想要的表格如下的表二：
制表月份：200606
分公司 商品 销售额
本月 上月 上年同期
南京分公司 IBM笔记本
南京分公司 HP笔记本
南京分公司 DELL笔记本
…… …… …… …… ……
合肥分公司 神州笔记本
可能在想，很简单啊，把表一中的数据跟月份条件分成三个子查询或者导出表（当月指标表、上月指标表、上年同期指标表），然后来进行一次关联就好了啊。
对，这个想法，完全正确。重要的是你选择什么样的关联方式呢？
当然先说一下关联条件，当时每个表的【分公司】以及【商品】了。
内关联，可能会对，但大部分情况是不对的，比如〖南京分公司〗这个月份销售了〖IBM笔记本〗，但也可能去年这个时候没有销售该笔记本呢，那内关联就会损失〖南京分公司〗销售〖IBM笔记本〗的信息，因为你是内关联，需要完全匹配。
那简单选择外关联吧，左关联或者有关联，那问题又来了，到底选择那个表为主表呢？今年某个公司销售了某个产品，可能去年同期没有销售，今年没有销售某个产品，可能去年又销售了。无法确定主表，外关联也不能满足需求。
当然交叉关联更加是不可以选择，那就只能选择全关联了。一般来说，这种需求即需要进行全关联，但两个表关联会简单，三个表呢？三个表你必须先将两个表关联后生成导出表或者子查询再和第三个表关联，否则你的关联条件可能又会让你的数据不满足你的需求，这个时候你在想？怎么会呢？我这样写：
FROM 当月指标表 A
FULL OUTER JOIN 上月指标表 B
ON B.分公司 = A.分公司 AND B.商品 = A.商品
FULL OUTER JOIN 上年同期指标表C
ON C.分公司 = A.分公司 AND C.商品 = A.商品
细看了，这个结果是是B和A外关联，C和A外关联。所以如果有组条件（比如〖上海分公司〗＋〖联想笔记本〗）A表没有，而B表和C表都有，这样的结果是是什么呢？会出现这组条件会有两条记录，第一条：A表指标空、B表指标有、C表指标空，第二条：A表指标空、B表指标空、C表指标有。显然这个也不是我们需要的结果，我们要的结果应该是：A表指标空、B表指标有、C表指标有。所以你如果需要使用全关联，你需要做的事情是要先将两表做全关联然后作为子查询或者导出表再和另外一个表关联。这是三个表的情况，当你要做N个表关联的时候，你就要作N－1个子查询，做N－1次全关联。
听起来就能了解这个效率很有问题。也是麻烦至极的。
所以遇到这个情况，我们不能这么去干，那是莽夫所为，也是愚公所为。愚公精神值得赞扬，但还是需要智叟来解决问题的。现在看智叟的。
从上面的尝试的过程中我们来分析，我们对于三个表，如果是两两关联出来的结果出现场重复记录，如上所属的〖上海分公司〗＋〖联想笔记本〗。这个时候你会想，将结果GROUP BY一下不就可以得到正确的结果了吗？答对了，完全正确，对最终结果做一次GROUP BY绝对可以将重复记录合并。这样当然能解决刚才的问题。
也显然，你会发现当出现这种情况后，GROUP BY基本上再所难免了，当然GROUP BY也是个耗时耗力的操作。那我在想，是否可以不需要前面的FULL OUTER JOIN。而直接GROUP BY呢？答案是显然的，是可以的。这个就是我改写FULL OUTER JOIN的方案：UNION ALL + GROUP BY。
我的做法是将三个导出表的数据项进行扩充成最后需要的表结构，对于未有的只采取默认为0（当然现在说的是数据统计，0可以解决问题，对于其他可以采取其他特殊默认值进行替代，比如空等）。就上理来说，对于本期导出表来说，上期指标填０，上年同期指标也填0；针对上期的导出表，将销售指标赋值给上期指标，本期指标以及上年同期指标均为0；同样去年同期导出表的本期和上期为0，将销售指标赋值给上年同期指标。通过如下格式的SQL即可完成。
SELECT 分公司, 商品, 指标 AS 本期指标, 0 AS 上期指标, 0 AS 上年同期指标
FROM 销售指标表 WHERE 月份＝本期月份；
SELECT 分公司, 商品, 0 AS 本期指标, 指标 AS 上期指标, 0 AS 上年同期指标
FROM 销售指标表 WHERE 月份＝上期月份；
SELECT 分公司, 商品, 0 AS 本期指标, 0 AS 上期指标, AS 上年同期指标
FROM 销售指标表 WHERE 月份＝本期月份。
现在大家看出来我要干什么了吧？然后将这三个子查询UNION ALL起来合并成一个大的数据集，然后在根据【分公司】、【商品】进行GROUP BY，对其他指标进行SUM即可搞定了。
最终的SQL是：
SELECT 分公司, 商品, SUM(本期指标), SUM(上期指标), SUM(上年同期指标)
FROM
(
SELECT 分公司, 商品, 指标 AS 本期指标, 0 AS 上期指标, 0 AS上年同期指标
FROM 销售指标表 WHERE 月份＝本期月份
UNION ALL
SELECT 分公司, 商品, 0 AS 本期指标, 指标 AS 上期指标, 0 AS上年同期指标
FROM 销售指标表 WHERE 月份＝上期月份
UNION ALL
SELECT 分公司, 商品, 0 AS 本期指标, 0 AS 上期指标, 指标 AS上年同期指标
FROM 销售指标表 WHERE 月份＝本期月份
)
GROUP BY 分公司, 商品
搞定，经典的精妙的SQL出现了，赶快去做个例子去尝试一下吧，你会感觉到精妙的。
这样解决了丢失数据的问题，也解决了重复记录的问题。
效率的问题也解决了，因为所有的操作就GROUP BY是个比较耗时耗力的操作，没有了关联，UNION ALL只是将记录合并，速度很快，而且UNION ALL各部分是互相不干扰的，在多处理器可并行处理的数据库系统中，UNION ALL一般都会被优化为并行处理，即各个部分并行查询将结果集合并而已。所以效率绝对大幅提高。
还有，这样的SQL很好扩种，而且结构清晰，将来对更多的表以及更多的信息扩充起来都要清晰、简单以及明朗的多。
当然我也提过，这种情况针对各个子查询的数据量相当的时候会是非常有效的。至于他的有效性，它的可推广的范围，你可能怀疑，这里只解决了指标计算的问题，通过SUM实现了。你想啊，分组函数还有很多啊，比如MAX、MIN、AVG、COUNT等，这些都可以解决很多问题的，在结合分组GROUP BY可以使用OLAP函数的PARITION BY代替，实际上这个组合可以解决很多关联的问题，针对关联的优化，这种改造不妨是个优秀的选择。结合实际的需求情况，你们可以发现他可以解决很多很多的关联问题。
除了这种统计表格之外，我曾在数据仓库中通过这个SQL组合实现了慢速变化存储（注、慢速变化即为将仓库中按时间存储的数据根据全集连续且无交叉的有效时间短来存储）的批量处理。所以，SQL精妙无穷，本文抛砖引玉，有待大家共同发掘。
[url=http://spaces.msn.com/tophilxx/blog/cns!98B961A385806771!261.entry[/url]
[url=http://spaces.msn.com/tophilxx/blog/cns!98B961A385806771!257.entry[/url]
[url=http://spaces.msn.com/tophilxx/blog/cns!98B961A385806771!256.entry[/url]
[url=http://spaces.msn.com/tophilxx/blog/cns!98B961A385806771!255.entry[/url]
[ 本帖最后由 tophi 于 2006-4-28 20:18 编辑 ] |
|
免费注册
发表于 2006-04-28 20:15
