记录我的一次基于dataworks大数据平台的关于多栏位SUM求和的ODPS SQL优化
背景
在我所需要做的需求中,有这样一个场景,简化描述即是,基于阿里云dataworks平台用ODPS SQL脚本统计出商品订单表(order_info)的每种商品在每个月的销量情况。
订单表主要字段(订单编号、商品编号、销售月份,每笔订单销售多少件)如下,每个分区订单表数据为2.5亿条:
需要统计呈现的report效果如下:
问题描述
前提:这个需求本身比这样的描述要复杂的多,需要进行多重f(x)计算,因与此次优化关系不大,遂省去,且仅基于其中一个分区进行操作,简化抽离以方便阅读,但是以下时间结果皆是基于需求本身得到的,而非简化后执行得到的。
一开始,我觉得这样的需求很简单,直接按商品进行group by分组,再用case判断是哪个月份,最后对满足条件的数据进行sum求和不就好啦?所以根据这样的思路,我写下了以下sql
SELECT goods_id
,SUM(CASE WHEN sale_month=7 THEN num END ) AS month7
,SUM(CASE WHEN sale_month=8 THEN num END ) AS month8
,SUM(CASE WHEN sale_month=9 THEN num END ) AS month9
FROM order_info
GROUP BY goods_id where goods_id='A'
;
为了便于验证,我在筛选条件上加上了商品条件为A的条件限制(where goods_id=‘A’),针对我原本的需求,这样的sql code 执行了约3分钟左右,对此结果,我认为是正常。于是我放开了goods_id='A’的条件限制,让其直接执行所有数据
SELECT goods_id
,SUM(CASE WHEN sale_month=7 THEN num END ) AS month7
,SUM(CASE WHEN sale_month=8 THEN num END ) AS month8
,SUM(CASE WHEN sale_month=9 THEN num END ) AS month9
FROM order_info
GROUP BY goods_id
;
于是,悲剧的事情发生了,竟然跑了2个小时都没有跑完,这不科学!
这里我解释一下,在原有需求里,由于我有***数十甚至上百个***SUM求和函数,还有其他函数,加上数据本身也是多表关联而来,并且数据量有上亿之多,所以执行的非常慢,logview显示经过2个小时才执行了60%。经过logview和explain分析,最终发现执行较慢的原因就在sum求和上。
解决方案
由于SUM函数较多,而SUM函数的求和模式又有规律可循,所以,最终解决方案定为,将【每月卖出件数】作为一个栏位字段来进行统一求和,再将这一列数据转化为行。
思路如下:
第一步,统计出每个商品每个月一共销售多少件,作为临时表1
第二步,在临时表1的基础上,将销售月份字段转成行形式,存为临时表2,即
第三步,在临时表2的基础上进行按goods id分组统计,对每月销量进行求和(此处根据我的业务场景,我使用的是求和函数,但不限于求和,可根据自身的业务场景,使用判空或者取MAX都可),最终即得到了我们想要的report
实行如下:
有了这样的思路,我们就赶紧实行起来吧~话不多说,优化后的sql如下
SELECT goods_id
,SUM(month7) AS month7 ---此处不限于sum函数
,SUM(month8) AS month8 ---此处不限于sum函数
,SUM(month9) AS month9 ---此处不限于sum函数
FROM (
SELECT goods_id
,(CASE WHEN sale_month=7 THEN MONTH_NUM END) AS month7
,(CASE WHEN sale_month=8 THEN MONTH_NUM END) AS month8
,(CASE WHEN sale_month=9 THEN MONTH_NUM END) AS month9
FROM (
SELECT goods_id
,sale_month
,SUM(num) AS MONTH_NUM
FROM order_info
GROUP BY goods_id
,sale_month
) temp1
) temp2
GROUP BY goods_id
;
经过这样的优化后,原需求sql执行时间缩短为30分钟。
小结
A 代码并不是越短越好,以代码精简程度评判优劣的形式不可取;
B 如在大数据开发中遇到类似需要多栏位求和十分耗时,而求和栏位又有共性的问题,不妨尝试将多栏位先转化成列,再转化为行,即用 行列转化 的思想解决来这样的问题。
C 如有不足指出请指出,如有更好的解决办法欢迎补充,谢谢~