3.N日留存率

该博客介绍了如何利用HQL(Hive Query Language)来计算用户留存率,包括次日留存和7日留存。通过示例展示了从数据表中提取用户活动信息,进行条件分组和聚合统计,最终计算出不同时间点的用户留存情况。适用于大数据分析和用户行为研究。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

核心代码

第一步:
->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_1d20200401 的次日、次7日留存的具体HQL

一条sql统计出以下指标 (4.1uv4.1号在4.2号的留存uv4.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(日期)deviceuser_idis_active
2020-03-01ios00010
2020-03-01ios00021
2020-03-01android00031
2020-03-02ios00010
2020-03-02ios00020
2020-03-02android00031

20200301ios设备用户活跃的次日留存率是多少?

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;

注:实际工作中,一般都会将首日、次日、七日后等写为变量,这样就可以很方便的获取指定天的次日,七日留存了

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值