活跃用户数+grouping sets以及次日留存率问题

 表：tableA : ds(日期),device,user_id,is_active 20200301,ios,0001,0 20200301,ios,0002,1 20200301,android,0003,1 20200302,ios,0001,0 20200302,ios,0002,0 20200302,android,0003,1 需求1:
结果： ds,device,uv（活跃用户数） device的取值有三个：ios，android，all

 建表语句:

create table tableA(
    ds string,
    device string,
    user_id string,
    is_active int
);
insert into tableA values
('2020-03-01','ios','0001',0),
('2020-03-01','ios','0002',1),
('2020-03-01','ios','0004',1),
('2020-03-01','android','0003',1),
('2020-03-02','ios','0001',0),
('2020-03-02','ios','0002',0),
('2020-03-02','android','0003',1),
('2020-03-02','ios','0005',1) ,
('2020-03-02','ios','0004',1) ;

select * from tableA;

 SQL语句实现:

set spark.sql.shuffle.partitions=4;
select ds,
       --判断方式1，下面的grouping__id有2个下划线，没有写错
       if(grouping__id=1,'all',device) device,
       --判断方式2
       if(device is null,'all',device) device,
       count(distinct if(is_active=1,user_id,null)) uv
from tableA
group by ds,device
grouping sets ((ds,device),ds);
--方案2，尽早过滤数据
select ds,
       --下面的grouping__id有2个下划线，没有写错
       if(grouping__id=1,'all',device) device,
       count(distinct user_id) uv
from tableA
where is_active=1
group by ds,device
grouping sets ((ds,device),ds);

 最终结果:

需求2: 接着上面，20200301的ios设备用户活跃的次日留存率是多少？

select * from tableA;

 分析思路:先把3月1月的活跃人数算数出来再去考虑留存率问题

--分子/分母
--分母：20200301的ios设备用户活跃总人数
--分子是在分母的基础上，进而在20200302，仍然活跃的，也就是说，分子的人群是分母的人群的子集。
with t1 as (
    select user_id,
       --3、一个用户如果在2020-03-01活跃，则人数>0
       count(if(ds='2020-03-01',user_id,null)) cnt1,
       --4、一个用户如果在2020-03-02活跃，则人数>0
       count(if(ds='2020-03-02',user_id,null)) cnt2
from tableA
--1、预先筛选数据
where device='ios'
and is_active=1
and ds in ('2020-03-01' , '2020-03-02')
--2、按照用户分组
group by user_id
having cnt1>0
)
select sum(cnt1) sum1,--2020-03-01的活跃数
       count(if(cnt2>0,user_id,null)) sum2,--并且在次日依然会活跃的用户数
       count(if(cnt2>0,user_id,null)) /sum(cnt1) as rate, --次日留存率
       sum(cnt2) /sum(cnt1) as rate1 --次日留存率
from t1;

要求百分数的话就:

round(sum(cnt2) /sum(cnt1),4)*100 || '%' as rate1 --次日留存率