用户留存率是什么？如何用sql求出次日留存率？

最新推荐文章于 2025-04-29 09:30:35 发布

BeautyBit

最新推荐文章于 2025-04-29 09:30:35 发布

阅读量2.2k

点赞数 2

CC 4.0 BY-SA版权

文章标签： mysql 数据分析 hive

本文链接：https://blog.youkuaiyun.com/FionaAI/article/details/119921792

本文通过SQL语句展示了如何创建临时表并提取用户登录数据，进而计算次日留存用户、留存率，并详细解析了每日次日留存率的计算过程。关键步骤包括筛选次日登录用户、使用DATEDIFF函数比较日期，以及计算百分比。

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

建表sql语句：

drop table if exists liucunlv;
create table liucunlv as select * from ( 
select 'a' as user_name, '2021-03-01'as logindate
 union all 
select 'a' as user_name, '2021-03-02'as logindate
 union all 
select 'a' as user_name, '2021-03-03'as logindate
 union all 
select 'a' as user_name, '2021-03-05'as logindate
 union all 
select 'b' as user_name, '2021-03-02'as logindate
 union all 
select 'b' as user_name, '2021-03-03'as logindate
 union all 
select 'c' as user_name, '2021-03-02'as logindate
 union all 
select 'c' as user_name, '2021-03-04'as logindate
 union all 
select 'c' as user_name, '2021-03-05'as logindate
 union all 
select 'c' as user_name, '2021-03-06'as logindate
 union all 
select 'c' as user_name, '2021-03-07'as logindate
 union all 
select 'd' as user_name, '2021-03-07'as logindate) t

SELECT * FROM liucunlv

在这里插入图片描述
一：现在要求出哪些用户才是次日留存的用户

SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name

在这里插入图片描述
如果logindate和newdate相差一天，那么就让if_ciriliucun=1否则if_ciriliucun=0

SELECT t1.user_name,CASE WHEN DATEDIFF(logindate,newdate)=1 THEN 1 ELSE 0 END AS if_ciriliucun
FROM 
(
SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name
)t1
JOIN liucunlv t2 ON t1.user_name=t2.user_name

在这里插入图片描述
之后再做一个groupby user_name,就能选出是否是次日留存的用户了

SELECT t3.user_name,MAX(if_ciriliucun) FROM
(
SELECT t1.user_name,CASE WHEN DATEDIFF(logindate,newdate)=1 THEN 1 ELSE 0 END AS if_ciriliucun
FROM 
(
SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name
)t1
JOIN liucunlv t2 ON t1.user_name=t2.user_name)t3 GROUP BY t3.user_name

在这里插入图片描述
可以看到，a和b是次日留存的用户

二：求每日的次日留存率
留存率解释：
在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。

SELECT t1.logindate AS 日期,
       COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END) AS 次日留存用户数,
       COUNT(DISTINCT t2.user_name) AS 新增用户数,
       CONCAT(round(COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END)*100/COUNT(DISTINCT t2.user_name),2),'%') AS 次日留存率
FROM
  (SELECT DISTINCT logindate
   FROM liucunlv) t1
LEFT JOIN
  (SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name) t2 ON t2.newdate = t1.logindate
LEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name
GROUP BY t1.logindate

在这里插入图片描述

求每日的次日留存率的整体代码如果读起来有困难，可以尝试接着往下读：
将求每日的次日留存率的整体代码拆解如下：：
t1:

SELECT DISTINCT logindate
   FROM liucunlv

在这里插入图片描述
t2:

SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name

在这里插入图片描述
t3:

在这里插入图片描述
将t1,t2,t3合起来得到一个表a

SELECT *
FROM
  (SELECT DISTINCT logindate
   FROM liucunlv) t1
LEFT JOIN
  (SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name) t2 ON t2.newdate = t1.logindate
LEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name

在这里插入图片描述
再将表a group by t1.logindate (表a的第一列)，做一些聚合运算，便得到

SELECT t1.logindate AS 日期,
       COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END) AS 次日留存用户数,
       COUNT(DISTINCT t2.user_name) AS 新增用户数,
       CONCAT(round(COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END)*100/COUNT(DISTINCT t2.user_name),2),'%') AS 次日留存率
FROM
  (SELECT DISTINCT logindate
   FROM liucunlv) t1
LEFT JOIN
  (SELECT user_name,
          MIN(logindate) newdate
   FROM liucunlv
   GROUP BY user_name) t2 ON t2.newdate = t1.logindate
LEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name
GROUP BY t1.logindate

得到我们想要的次日留存率：
在这里插入图片描述

引用：
https://blog.youkuaiyun.com/weixin_38617657/article/details/116697972