MySQL之留存问题

概念：

        在很多业务中需要计算用户留存情况，在时间维度上可以分为用户次日、多日后的留存情况，用留存率表示。留存率计算方式如下：

次日留存率 = 当日新用户在次日登录的数量 / 当日新用户注册数量

三日留存率 = 当日新用户在第四天登录的数量 / 当日新用户注册数量

七日留存率 = 当日新用户在第八天登录的数量 / 当日新用户注册数量

现在我们有两张表：一张展示用户的注册时间，一张展示用户的登陆时间

首先将两张表进行关联，使用uid，再使用datediff()函数，计算出登陆时间的差值

select su.uid,seven.creat_time,create_time,
       datediff(create_time,seven.creat_time)diff
from seven join seven_use su on seven.uid = su.uid
order by uid

 

出现负数是因为我用的数据有问题，这个关系不大

 接下来使用CTE语法，找出比如说三日留存，使用diff等于三进行过滤，计算他的留存率

with tb1 as (
select su.uid,seven.creat_time,create_time,
       datediff(seven.creat_time,create_time)diff
from seven join seven_use su on seven.uid = su.uid
order by uid)
select
    count(distinct uid) new_uid,# 统计出所有的新增用户
    count(distinct case when diff=3 then uid end)/count(distinct uid)三日留存 # 使用case when判断uid是否为3，是返回uid，不是则返回空
    from tb1;

最终的计算结果，因为有四个人，第三天有一个用户登录了两次，则留存率为0.25

其他日期留存一次计算即可。