留存率的计算可以说是数据分析师, 或者数据开发必备的技能了, 很多公司都会用到这个指标, 该题还有进阶版的: 计算次日至7日内的新增用户留存率
- 思路: 根据uid分组, 取min(in_time)最小值, 得到用户的首次登录日期, 作为衍生表t1
select ul.uid,
min(date(ul.in_time)) min_date
from tb_user_log ul
group by ul.uid
- 因为题目要求, out_time跨天也算该日活跃, 那么第二个子查询, 只需要关键字, uid, in_time, out_time即可, 这里其实可以直接用衍生表t1的uid左连接tb_user_log, 这样能得到如下的格式数据(假设, id为1的用户, 在1-3号都有登录过, 则会出现3条数据
t1.uid |
t1.min_date |
ul.uid |
ul.in_time |
ul.out_time |
1 |
2022-11-01 |
1 |
2022-11-01 |
2022-11-01 |
1 |
2022-11-01 |
1 |
2022-11-02 |
2022-11-02 |
1 |
2022-11-01 |
1 |
2022-11-03 |
2022-11-03 |
- 如上表, 可以再根据, t1.min_date 进行分组, 对 t1.uid 进行去重, 则可得到该日的新增用户数, 而下一
天的用户数, 则可使用case when 对ul.in_time 和 out_time 进行判断, 这里我用的是count(distinct case when t2.l_in_time = date_add(t1.min_date, interval 1 day)
or t2.l_out_time = date_add(t1.min_date, interval 1 day) then t2.uid
else null end)
同理, 3-7日的留存率也可如此计算.优化点: 一个用户在一天可以登录多次
这样直接左关连tb_user_log(此表生产环境中一般都极大), 所以可以根据uid, date(ul.in_time), date(ul.out_time) 来缩小连表的数据量, 一个用户一天只需要保留一条数据即可
select t1.min_date as dt,
round(count(distinct case when t2.l_in_time = date_add(t1.min_date, interval 1 day)
or t2.l_out_time = date_add(t1.min_date, interval 1 day) then t2.uid
else null end)/count(distinct t1.uid), 2) as uv_left_rate
from (
select ul.uid,
min(date(ul.in_time)) min_date
from tb_user_log ul
group by ul.uid
) t1 left join (
select ul.uid,
date(ul.in_time) l_in_time,
date(ul.out_time) l_out_time
from tb_user_log ul
) t2 on t1.uid = t2.uid
where min_date between '2021-11-01' and '2021-12-01'
group by min_date
order by min_date