select user_id,min(date) as first_day from a2_userbehavior_csv group by user_id
实际上就是对用户和日期去重
select user_id,date from a2_userbehavior_csv group by user_id,date
select t1.*,t2.date,datediff(t2.date,t1.first_day) as day_diff from (select user_id,min(date) as first_day from a2_userbehavior_csv group by user_id) as t1 left join (select user_id,date from a2_userbehavior_csv group by user_id,date) as t2 on t1.user_id=t2.user_id
得到结果如下：
得到了每个用户每个登入日期距离其最早登入日期的天数。
现在思路就明朗了。
次日留存率=（day_diff=1的数量）/（day_diff=0的数量）
三日留存率=（day_diff=3的数量）/（day_diff=0的数量）
select first_day as dt, concat(round(100*count(case when day_diff=1 then user_id end)/count(case when day_diff=0 then user_id end),2),"%") as '次日留存率', concat(round(100*count(case when day_diff=3 then user_id end)/count(case when day_diff=0 then user_id end),2),"%") as '三日留存率', concat(round(100*count(case when day_diff=7 then user_id end)/count(case when day_diff=0 then user_id end),2),"%") as '七日留存率' from (select t1.*,t2.date,datediff(t2.date,t1.first_day) as day_diff from (select user_id,min(date) as first_day from a2_userbehavior_csv group by user_id) as t1 left join (select user_id,date from a2_userbehavior_csv group by user_id,date) as t2 on t1.user_id=t2.user_id) as t3 group by first_day order by first_day
到此这篇关于Mysql计算n日留存率的实现的文章就介绍到这了,更多相关Mysql n日留存率内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
编程 | 2023-02-24 21:36
编程 | 2023-02-21 12:51
编程 | 2023-02-21 12:47
编程 | 2023-02-21 00:15
编程 | 2023-02-21 00:08
编程 | 2023-02-20 21:46
编程 | 2023-02-20 21:42
编程 | 2023-02-20 21:36
编程 | 2023-02-20 21:32
编程 | 2023-02-20 18:12
Python之列表推导式最全汇总(上篇)
加载中,请稍侯......
网友评论