Mysql-计算n日留存率

最新推荐文章于 2025-06-24 07:51:05 发布

Caspian�

最新推荐文章于 2025-06-24 07:51:05 发布

阅读量1.6k

点赞数 4

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql 数据库 database

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

数据库专栏收录该内容

4 篇文章

订阅专栏

该博客介绍了如何通过SQL查询计算用户留存率，包括获取每个用户的最早登录日期、所有登录日期，然后通过日期差计算留存，并给出了次日、三日和七日留存率的计算方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

三、将两个表按照user_id拼接，并且计算日期时间差

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