核心代码
第一步:
->user_id
count(if(日期=首日,1,null)) as cnt -- 这其实就是带条件的分组聚合统计
count(if(日期=1天后,1,null)) as cnt2
count(if(日期=7天后,1,null)) as cnt8
-> where 日期 in (首日,1天后,7天后) -- 提前过滤数据
-> group by user_id
->having cnt>0 -- 只筛选出首日登录过的所有用户,可能登录过多次,但只要时登录过就行
第二步:
->count(user_id) as 首日总数 -- 第一步已经有过group by user_id,故这里直接聚合就是首日登录过的用户总数
count(if(cnt2>0,1,null)) as 次日留存数 -- cnt2>0,说明用户一天后登录过,即使是登录过几次,都是记为一个用户一天后登录了,累加后就是一天后共有多少用户登录了
count(if(cnt8>0,1,null)) as 7日留存数 -- 同上
第三步:
->次日留存数/首日总数 as 次日留存率
7日留存数/首日总数 as 7日留存率
例题一:百度
create table if not exists tb_cuid_1d
(
cuid string comment '用户的唯一标识',
os string comment '平台',
soft_version string comment '版本',
event_day string comment '日期',
visit_time int comment '用户访问时间戳',
duration decimal comment '用户访问时长',
ext array<string> comment '扩展字段'
);
insert overwrite table tb_cuid_1d values
(1,'android',1,'2020-04-01',1234567,100,`array`('')),
(1,'android',1,'2020-04-02',1234567,100,`array`('')),
(1,'android',1,'2020-04-08',1234567,100,`array`('')),
(2,'android',1,'2020-04-01',1234567,100,`array`('')),
(3,'android',1,'2020-04-02',1234567,100,`array`(''));
select * from tb_cuid_1d;
写出用户表 tb_cuid_1d
的 20200401
的次日、次7
日留存的具体HQL
:
一条sql
统计出以下指标 (4.1
号uv
,4.1
号在4.2
号的留存uv
,4.1
号在4.8
号的留存uv
);
with t1 as (
select
cuid,
count(if(event_day = '2020-04-01',1,null)) as cnt, -- 因为按cuid分组了,所以统计的是每个cuid在2022-04-01的登录次数
count(if(event_day = '2020-04-02',1,null)) as cnt2,
count(if(event_day = '2020-04-08',1,null)) as cnt8
from tb_cuid_1d
where event_day in('2020-04-01','2020-04-02','2020-04-08') -- 提前做全局过滤
group by cuid
having cnt > 0 --只筛选'2020-04-01'活跃的用户,他在'2020-04-02'是否活跃,看cnt2=0则不活跃,>0则活跃
),
t2 as (
select
count(cuid) as uv, -- 首日登录的总用户数
count(if(cnt2 > 0,1,null)) as uv2, -- cnt2>0,说明次日登录过,算一个次日登录用户
count(if(cnt8 > 0,1,null)) as uv7 -- cnt8>0,说明七日后登录过,算一个七日后登录用户
from t1
)
select
*,
uv2/uv as `次日留存率`,
uv7/uv as `七日留存率`
from t2;
附:中途结果
select
cuid,
count(if(event_day = '2020-04-01',1,null)) as cnt, -- 因为按cuid分组了,所以统计的是每个cuid在2022-04-01的登录次数
count(if(event_day = '2020-04-02',1,null)) as cnt2,
count(if(event_day = '2020-04-08',1,null)) as cnt8
from tb_cuid_1d
where event_day in('2020-04-01','2020-04-02','2020-04-08') -- 提前做全局过滤
group by cuid
having cnt > 0 --只筛选'2020-04-01'活跃的用户,他在'2020-04-02'是否活跃,看cnt2=0则不活跃,>0则活跃
可以看到4.1
登录的用户是有1
号和2
号,且1
号在次日和七日后都登录过,2
号只在次日登录过了
例题二:腾讯
ds(日期) | device | user_id | is_active |
---|---|---|---|
2020-03-01 | ios | 0001 | 0 |
2020-03-01 | ios | 0002 | 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 |
20200301
的ios
设备用户活跃的次日留存率是多少?
create table tableA
(ds string comment '(日期)' ,device string,user_id string,is_active int) ;
insert overwrite table 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) ;
解题方法与例题一完全一致,过程见SQL
中的顺序编号
with t1 as (
select user_id,
--3-一个用户如果在'2020-03-01'活跃,则cnt1>0
count(if(ds = '2020-03-01', 1, null)) cnt1, -- 2020-03-01的is_active已经由where完成过滤了
--4-一个用户如果在'2020-03-02'活跃,则cnt2>0
count(if(ds = '2020-03-02' and is_active = 1, 1, null)) cnt2
from tableA
--1-预先全局过滤
where device = 'ios'
and ( (ds='2020-03-01' and is_active = 1) or ds='2020-03-02')
--2-按用户分组
group by user_id
--5-只筛选'2020-03-01'活跃的用户,他在'2020-03-02'是否活跃,看cnt2=0则不活跃,>0则活跃
having cnt1 > 0
)
select count(cnt1) sum1,--'2020-03-01'的活跃数
count(if(cnt2 > 0, user_id, null)) sum2,----并且在次日依然活跃的用户数
count(if(cnt2 > 0, user_id, null)) / count(cnt1) rate--次日留存率
from t1;
注:实际工作中,一般都会将首日、次日、七日后等写为变量,这样就可以很方便的获取指定天的次日,七日留存了