SQL进阶教程1-5 外连接:在交叉表里制作嵌套式侧栏 、作为乘法运算的连接
在生成统计表的工作中,经常会有制作嵌套式表头和表侧栏的要求,如下所示Tblpop是一张按照县、年龄层级和性别统计的人口分布表,要求根据表Tblpop生成交叉表“包含嵌套式表侧栏的统计表”。
人口分布表:
要求生成包含嵌套式表侧栏的统计表:
这个问题的要点在于,虽然TblPop中没有一条年龄层级为2的数据,但是返回结果还是要包含这个年龄层级,固定输出6行。生成固定的表侧栏需要用到外连接,目标表的侧栏是年龄层级和性别,所以需要使用TblAge和TblSex作为主表。
观察侧边栏,是包含了age和sex的所有组合,所以选用主表时,应该使用TblAge和TblSex的交叉连接:
SELECT age_class, age_range, sex, sex_cd FROM TblAge CROSS JOIN TblSex
选定要外连接的副表,副表里面的人口数量应该按照性别和年龄进行加权。
SELECT age_class, sex_cd, SUM(CASE WHEN pref_name IN ('秋田','青森') THEN population ELSE 0 END) AS pop_tohokou, SUM(CASE WHEN pref_name IN ('东京','千叶') THEN population ELSE 0 END) AS pop_kandong FROM tblpop GROUP BY age_class, sex_cd
最终的SQL语句是:
SELECT MASTER.age_range AS '年龄层级', MASTER.sex AS '性别', DATA.pop_tohoku AS '东北', DATA.pop_kanto AS '关东' FROM (SELECT age_class, age_range, sex, sex_cd FROM TblAge CROSS JOIN TblSex ) MASTER LEFT JOIN (SELECT age_class, sex_cd, SUM(CASE WHEN pref_name IN ('秋田','青森') THEN population ELSE NULL END) AS pop_tohoku, SUM(CASE WHEN pref_name IN ('东京','千叶') THEN population ELSE NULL END) AS pop_kanto FROM tblpop GROUP BY age_class, sex_cd) DATA ON MASTER.age_class = DATA.age_class AND MASTER.sex_cd = DATA.sex_cd;
二、 作为乘法运算的连接
商品主表 newitems
商品销售历史表:
期待的结果表:
我的SQL
以newitem表作为主表,左外连接自己生成的视图。
SELECT newitem.item,sale.sum FROM newitem LEFT JOIN (SELECT item_no, SUM(quantity) AS sum FROM salesHistory GROUP BY item_no) sale ON newitem.item_no = sale.item_no GROUP BY newitem.item_no
缺点:从性能问题考虑,这条SQL是有些问题的:临时视图sum的数据需要临时存储在内存里,还有就是虽然通过聚合将item_no变成了主键,但是sum上却不存在主键所有因,因此无法利用索引优化查询。
要改善这个查询,关键在于导入“把连接看作乘法运算”这种观点,商品主表newitem与视图sum确实是一对一的关系。以item_no列的角度看,表newitem和表salesHistory是一对多的关系。但是其实从item_no列看,表Items和表SalesHistory是一对多的关系。
当连接操作的双方是一对多的关系时,结果的行数并不会增加,结果的行数为“多”的哪一方。这就像普通乘法里任意数乘以1后,结果不会变化一样。在二元运算中,如果某个值与其他任意值进行运算结果都不会改变,那么我们称这个值为单位元。例如与整数进行乘法运算时的1,以及加法运算时的0。从这一点来看,在SQL的连接运算中,具有单位元性质的是“只有一行数据的表”。因为数据行数为1的表和其他人意表进行交叉连接,结果行数与源表行数一样。
所以按照这种思想,改良过后的SQL语句如下所示:
SELECT I.item_no, SUM(SH.quantity) AS total_qty FROM newitem I LEFT OUTER JOIN SalesHistory SH ON I.item_no = SH.item_no GROUP BY I.item_no;
这种做法代码会更加简洁,而且没有使用临时视图,所以性能也会有所改善。
如果表newitem里的“items_no”列内存在重复行,就属于多对多连接,因而这种做法就不能再使用。这时,需要先把某张表聚合一下,是两张表变成一对多的关系。
一对一或者一对多关系的两个组合,在进行连接操作后行数不会增加。这个技巧在需要使用连接和聚合来解决问题时非常有用。