在统计一些指标时,通常会有多个指标需要分组进行聚合,但是数据源的粒度可能并非是指标分组的粒度。举个例子,比如从访客表中提取访客的数据,每行数据有每个平台的首次访问时间;另外要做一个平台统计表,其中的一个指标统计的是各个平台近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
本文转载自: https://blog.csdn.net/qq_40772429/article/details/127088109
版权归原作者 老板,我要写光所有SQL 所有, 如有侵权,请联系我们删除。
版权归原作者 老板,我要写光所有SQL 所有, 如有侵权,请联系我们删除。