
--1--
with tmp1 as(
select
cuid
from tb_cuid_1d
where event_day='20200401'
group by cuid
)
,tmp2 as (
select
cuid
from tb_cuid_1d
where event_day=date_add('20200401',1)
group by cuid
)
,tmp3 as (
select
cuid
from tb_cuid_1d
where event_day=date_add('20200401',7)
group by cuid
)
select
count(1) as uv,
count(if(tmp2.cuid is not null,1,null)) as `次日留存`,
count(if(tmp3.cuid is not null,1,null)) as `7日留存`
from tmp1 left join tmp2 on tmp1.cuid=tmp2.cuid
left join tmp3 on tmp1.cuid=tmp3.cuid
--2--
with tmp as (
select
explode(ext) as ext
from tb_cuid_1d
)
select ext.type as `type` from tmp
;
--3--
with tmp as (
select
cuid,
concat_ws('_',os,cast(rand()*1000%6 as int)) as os,
concat_ws('_',soft_version,cast(rand()*1000%6 as int)) as soft_version
from tb_cuid_1d
)
select
split(os,'_')[0] as os,
split(soft_version,'_')[0] as soft_version
sum(pv) as pv_amt,
count(distinct cuid) as uv_amt
from
(
select
cuid,os,soft_version,count(1) as pv
from tmp
group by cuid,os,soft_version
) o
group by split(os,'_')[0],split(soft_version,'_')[0]
--3.1--
-- 不能直接加和
-- 加入有一个用户,在os1,ver1上有记录,在os2,ver2上也有记录,直接加和则重复了
--3.2--
select
count(distinct cuid) as uv_amt,
count(1) as pv_amt
from tb_cuid_1d
group by os,soft_version
grouping sets((),(os,soft_version))
--4--
select
count(distinct cuid) as dau_amt,
count(distinct if(b.uid is not null,1,null)) as dau_login_amt
from tb_cuid_1d a left join tb_account_1d b
on a.event_day='20200401' and b.event_day='20200401' and a.cuid=b.cuid

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