Mysql计算n日留存率的实现

一、创建一张包含每个用户最早登入日期的表

?

1 2 3

select user_id, min ( date ) as first_day from a2_userbehavior_csv group by user_id

二、创建一张包含每个用户所有登入日期的表

实际上就是对用户和日期去重

?

1 2 3

select user_id, date from a2_userbehavior_csv group 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_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的数量）

?

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_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日留存率内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/weixin_44020827/article/details/122906062