
问1:
select
event_day,osname,soft_version,count(cuid)
from tb_tmp_1d
group by event_day,osname,soft_version
grouping sets((event_day),(event_day,os_name),(event_day,soft_version))
问2:
with tmp1 as (select cuid from tb_tmp_1d where event_day='20190101')
,tmp2 as (select cuid from tb_tmp_1d where event_day=date_add('20190101',1))
,tmp3 as (select cuid from tb_tmp_1d where event_day=date_add('20190101',7))
--次日留存
select
'20190101' as newday,
date_add('20190101',1) as rt1_day,
cuid
from tmp1 join tmp2 on tmp1.cuid=tmp2.cuid
--次7日留存
select
'20190101' as newday,
date_add('20190101',7) as rt7_day,
cuid
from tmp1 join tmp2 on tmp1.cuid=tmp2.cuid
问3:
select
os_name,
count(distinct a.cuid) as act_ucnts,
count(distinct b.cuid) as act_lg_ucnts
from tb_cuid_1d a join tb_accounts_df b
on a.cuid=b.cuid and a.event_day='20200327'
group by os_name
问4:
select
cuid,
avg(duration) as avg_duration
from tb_cuid_1d
group by cuid
;
问5:
with tmp as (
select
cuid,
count(1) as act_days
from tb_cuid_1d
where event_day between '20200320' and '20200326'
group by cuid
)
select
avg(act_days) as avg_act_days
from tmp

关注 易学在线 公众号
每日更新大量优质技术文档
第一时间获知最新上架课程
与众多大数据猿分享与交流
DOIT.EDU 资料来自网络收集整理,如有侵权请告知