表：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)) uvfrom tableAgroup by ds,devicegrouping sets ((ds,device),ds);--方案2，尽早过滤数据select ds, --下面的grouping__id有2个下划线，没有写错 if(grouping__id=1,'all',device) device, count(distinct user_id) uvfrom tableAwhere is_active=1group by ds,devicegrouping 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)) cnt2from tableA--1、预先筛选数据where device='ios'and is_active=1and ds in ('2020-03-01' , '2020-03-02')--2、按照用户分组group by user_idhaving 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 --次日留存率