SQL实现一行数据分组后转多行多列

在统计一些指标时，通常会有多个指标需要分组进行聚合，但是数据源的粒度可能并非是指标分组的粒度。举个例子，比如从访客表中提取访客的数据，每行数据有每个平台的首次访问时间；另外要做一个平台统计表，其中的一个指标统计的是各个平台近1天、7天、30天的新访客。这里的数据源的粒度是一行一个访客，带个平台时间字段，但指标粒度是一行一个平台，两者不相同。

先加上标签，一行多列变一行一列

        由于数据源需要统计的字段是多个，因此也没有办法根据字段进行group by 聚合，只能普通的先count(if(时间筛选条件))，一次性将所有的1天、7天以及30天的各个平台新增访客统计出来于一行，并加上标签区分平台（时间不用区分，因为30天人数>7天>1天），可以外层套层sql用concat(人数,'-',平台)再collect_set()聚合到一个数组中，，我这里用的是odpsSQL的map()函数.这时候数据变成了一行一列

再将数据从一行一列变成一行多列

        这时我们只需要通过lateral view explode() 进行展开成一列多行。

拆解标签变成多行多列带分组标签字段

        再拆解出标签，通过split()将平台划分成单独的一个字段后，

将统计指标字段group by存入同一个标签分组中

        根据平台进行group by ，再将1天、7天、30天的数据collect_set()装入同一个数组中。

对数组中的数据进行处理得出字段

        将数组通过排序，通过数组下标直接获取对应时间的新增访客数。

总的代码如下：

select 
    platform,
    first_visit_count_list[0] as first_visit_count_1d,
    first_visit_count_list[1] as first_visit_count_7d ,
    first_visit_count_list[2] as first_visit_count_30d
from (
    select 
        platform,
        array_sort(collect_SET(first_visit_count),(l, r) -> CASE WHEN l < r THEN -1L WHEN l > r THEN 1L ELSE 0L END) as first_visit_count_list -- 30天>7天>1天，根据该规律排序数组即可
    from ( 
        select 
            map(    -- 存放在一个map里，便于展开
                -- 30天
                count(if(ssish_first_time>to_date(${bizdate-30},'yyyymmdd'),1,null)),'主站',
                count(if(mkf_first_time>to_date(${bizdate-30},'yyyymmdd'),1,null)),'my客蜂',
                count(if(cps_first_time>to_date(${bizdate-30},'yyyymmdd'),1,null)),'cps',
                count(if(wechatlink_first_time>to_date(${bizdate-30},'yyyymmdd'),1,null)),'微链',
                count(if(nyboss_first_time>to_date(${bizdate-30},'yyyymmdd'),1,null)),'保思',
                count(if(wxmall_first_time>to_date(${bizdate-30},'yyyymmdd'),1,null)),'微信商城',
            
                -- 7天
                count(if(ssish_first_time>to_date(${bizdate-7},'yyyymmdd'),1,null)),'主站',
                count(if(mkf_first_time>to_date(${bizdate-7},'yyyymmdd'),1,null)),'my客蜂',
                count(if(cps_first_time>to_date(${bizdate-7},'yyyymmdd'),1,null)),'cps',
                count(if(wechatlink_first_time>to_date(${bizdate-7},'yyyymmdd'),1,null)),'微链',
                count(if(nyboss_first_time>to_date(${bizdate-7},'yyyymmdd'),1,null)),'保思',
                count(if(wxmall_first_time>to_date(${bizdate-7},'yyyymmdd'),1,null)),'微信商城',

                -- 昨天
                count(if(ssish_first_time>to_date(${bizdate},'yyyymmdd'),1,null)),'主站',
                count(if(mkf_first_time>to_date(${bizdate},'yyyymmdd'),1,null)),'my客蜂',
                count(if(cps_first_time>to_date(${bizdate},'yyyymmdd'),1,null)),'cps',
                count(if(wechatlink_first_time>to_date(${bizdate},'yyyymmdd'),1,null)),'微链',
                count(if(nyboss_first_time>to_date(${bizdate},'yyyymmdd'),1,null)),'保思',
                count(if(wxmall_first_time>to_date(${bizdate},'yyyymmdd'),1,null)),'微信商城'
            )
            as platform_first_visit_count_list
        from nanyan_space.dim_visitors_info 
        where pt='${bizdate}'
    )a1
    lateral view explode(platform_first_visit_count_list) tmp as first_visit_count,platform
    group by platform
)a2