一、创建一张包含每个用户最早登入日期的表
|
1
2
3
|
select user_id,min(date) as first_dayfrom a2_userbehavior_csvgroup by user_id |
二、创建一张包含每个用户所有登入日期的表
实际上就是对用户和日期去重
|
1
2
3
|
select user_id,datefrom a2_userbehavior_csvgroup by user_id,date |
三、将两个表按照user_id拼接，并且计算日期时间差
|
1
2
3
4
5
6
7
8
9
10
|
select t1.*,t2.date,datediff(t2.date,t1.first_day) as day_difffrom(select user_id,min(date) as first_dayfrom a2_userbehavior_csvgroup by user_id) as t1left join(select user_id,datefrom a2_userbehavior_csvgroup by user_id,date) as t2on t1.user_id=t2.user_id |
得到结果如下：
得到了每个用户每个登入日期距离其最早登入日期的天数。
四、计算各种留存率
现在思路就明朗了。
次日留存率=（day_diff=1的数量）/（day_diff=0的数量）
三日留存率=（day_diff=3的数量）/（day_diff=0的数量）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
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_difffrom(select user_id,min(date) as first_dayfrom a2_userbehavior_csvgroup by user_id) as t1left join(select user_id,datefrom a2_userbehavior_csvgroup by user_id,date) as t2on t1.user_id=t2.user_id) as t3group by first_day order by first_day |
到此这篇关于Mysql计算n日留存率的实现的文章就介绍到这了,更多相关Mysql n日留存率内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/weixin_44020827/article/details/122906062
