SQL实现一行数据分组后转多行多列-Toy模板网

这篇具有很好参考价值的文章主要介绍了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()装入同一个数组中。

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

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

总的代码如下：文章来源地址https://www.toymoban.com/news/detail-621315.html

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

到了这里，关于SQL实现一行数据分组后转多行多列的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！