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”列内存在重复行,就属于多对多连接,因而这种做法就不能再使用。这时,需要先把某张表聚合一下,是两张表变成一对多的关系。

 

  一对一或者一对多关系的两个组合,在进行连接操作后行数不会增加。这个技巧在需要使用连接和聚合来解决问题时非常有用。

posted @   Garcia11  阅读(462)  评论(0)    收藏  举报
努力加载评论中...
点击右上角即可分享
微信分享提示