0


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
标签: 数据库 hive odps

本文转载自: https://blog.csdn.net/qq_40772429/article/details/127088109
版权归原作者 老板,我要写光所有SQL 所有, 如有侵权,请联系我们删除。

“SQL实现一行数据分组后转多行多列”的评论:

还没有评论