SQL中UNION ALL与WITH ROLLUP的作用与区别
SQL中UNION ALL与WITH ROLLUP的作用与区别
前言:博主从事BA数据分析师的工作。目前的工作内容主要是指标的理解和使用SQL将代码写出来。经常使用UNION ALL和ROLL UP对数据进行合并和汇总。下面直接通过实例进行解释,防止大家进入误区。
一、查看原表
原表1字段分别为:订单id(ord_id),顾客id(cus_id),交易额(revenue),商品件数(trans),新老订单(cus_flag)
二、工作需求:
要求:求每个顾客的新老订单的交易情况和商品件数,并求所有顾客总的订单情况。
要求结果表格如下表 2、3,形式如下。其中TTL为小计或总和项
表格所有字段查询语句如下:
SELECT * FROM CUS;
三、使用UNION ALL连接合并表格(求总计)
SELECTcus_id,cus_flag,SUM(revenue),SUM(trans)FROM CUSGROUP BY cus_id,cus_flagUNION ALLSELECT 'TTL' AS cus_id,'TTL' AS cus_flag,SUM(revenue),SUM(trans)FROM CUS;
SELECT 'TTL' AS cus_id,'TTL' AS cus_flag,SUM(revenue),SUM(trans)FROM CUS;
这里,不分组,则求总。添加 ‘TTL’ AS cus_id,‘TTL’ AS cus_flag是为了与上述表格形式一致。才可以使用UNION ALL。
UNION ALL:将两个字段一致的表在竖直方向上合并。
这里大家可以去查一下:UNION与UNION ALL的区别。
注:使用UNION ALL 合并表格时,两个表格的字段必须完全一致。
三、使用WITH ROLLUP对分组求和(小计)
SELECT COALESCE(cus_id,'TTL') AS cus_id,COALESCE(cus_flag, 'TTL') AS cus_flag,SUM(revenue),SUM(trans) FROM CUS GROUP BY cus_id,cus_flagWITH ROLLUP;
WITH ROLLUP:对每个分组的项进行求和。即达到小计、小计… … 总计的作用。这样就不需要UNION进行表格的合并了。
注:上述使用了COALESCE函数。该函数的通俗定义为:
返回第一个非空值,(也就是说,如果要呈现的该值为NULL,那么使用后面指定的值代替)。与IFNULL函数作用相似。
COALESCE(cus_id,'TTL') AS cus_id
这里即指,若cus_id中的项为NULL,则使用TTL代替。
最后一点:如果大家以后想从事数据分析相关工作,那么在平时的SQL练习过程中尽量注意代码书写规范。