记录我的一次基于dataworks大数据平台的关于多栏位SUM求和的ODPS SQL优化

记录我的一次基于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 如有不足指出请指出，如有更好的解决办法欢迎补充，谢谢~