SQL留存率问题

留存率是衡量用户对产品持续使用的重要指标,通常以百分比表示。文章提供了计算不同时间段留存率的SQL查询方法,包括次日、三日和七日留存率,通过左连接操作和CASE语句来确定用户在后续天数是否活跃。

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

在这里插入图片描述

什么是留存率!

-留存率(retention_ rate)通常用来衡量用户或客户的忠诚度和粘性。
-留存率指的是在特定时间段内,有多少人保持了对某个产品、服务、平台或应用程序的使用并继续付费或进行其他有价值的操作。
-通常情况下,留存率会作为一个百分比表示,并根据不同的时间段进行计算。

  • 1.直接从数据出发

fwe wef
指标定义

某日活跃用户数:某日活跃的去重用户数。
N日留存用户数:某日活跃的用户在之后的第N日活跃用户数。
N日活跃留存率:N日留存用户数/某日活跃用户数

  • 举例:1月1日活跃用户数为100人,这批用户中仍有50人在3日登录了,所以1月1日的三日留存率是50/100=50%
select
    dt               ,
    count(distinct id) as  活跃用户数
    coun
from t
group by dt
order by dt desc

在这里插入图片描述


select
       t1.id,
       t1.dt,
       t2.ID,
       t2.dt
from t as t1 left join t as t2
       on t1.ID=t2.ID
       and  date(t1.dt)-date(t2.dt)=1
order by dt desc

  1. 可以看到,用两个日期之差等于1来进行连接,空值代表该ID在第二天没有进行登录,用此方法更改两者之差的天数可以求出任意留存的用户数,但是太麻烦了。

在这里插入图片描述


select
       t1.dt,
       count(distinct t1.id) as "活跃用户数",
       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "次日留存用户数"  ,
       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率'
from t as t1 left join t as t2
       on t1.ID=t2.ID
group by t1.dt
order by dt desc

在这里插入图片描述

  • 以此类推,我们可以求出解决任意留存率问题
    完全代码
select
       t1.dt,
       count(distinct t1.id) as "活跃用户数",
       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "次日留存用户数" ,
       count(distinct case when date(t1.dt)-date(t2.dt)=2  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "三日日留存用户数" ,
       count(distinct case when date(t1.dt)-date(t2.dt)=6  #相当于上面的 自连接条件
              then t2.id  else null end )                              as  "七日留存用户数" ,


       count(distinct case when date(t1.dt)-date(t2.dt)=1  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率',
       count(distinct case when date(t1.dt)-date(t2.dt)=2  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率',
       count(distinct case when date(t1.dt)-date(t2.dt)=6  #相当于上面的 自连接条件
              then t2.id  else null end )/count(distinct t1.id)       as '次日留存率'


from t as t1 left join t as t2
       on t1.ID=t2.ID
group by t1.dt
order by dt desc

在这里插入图片描述

### 如何用SQL计算用户留存率 在数据分析领域,尤其是针对To C企业,用户留存率是一个重要的业务指标。它反映了企业在一定时间范围内能够留住新用户的程度。以下是通过SQL实现用户留存率的具体方法。 #### 数据准备 假设有一个表 `user_log` 记录了用户的登录行为,其结构如下: | 字段名 | 描述 | |--------------|----------------------| | role_id | 用户唯一标识 | | login_date | 登录日期 | 为了计算不同时间段内的留存情况,通常会先构建一张临时表来记录每个用户的首次登录时间和每次登录的时间差。可以通过以下 SQL 创建这样的中间表: ```sql WITH temp_1 AS ( SELECT role_id, MIN(login_date) OVER (PARTITION BY role_id) AS create_date, -- 首次登录日期 DATEDIFF(login_date, MIN(login_date) OVER (PARTITION BY role_id)) AS day_diff -- 时间间隔 FROM user_log ) ``` 此部分逻辑用于标记每位用户的首次登录日期以及之后每一天相对于首次登录的天数差异[^2]。 #### 留存率计算 基于上述中间表 `temp_1`,可以进一步统计每日新增用户及其对应的 N 日留存数量。具体 SQL 实现如下所示: ```sql SELECT create_date, COUNT(CASE WHEN day_diff = 0 THEN role_id END) AS 新增用户数, COUNT(CASE WHEN day_diff = 1 THEN role_id END) AS 次日留存, COUNT(CASE WHEN day_diff = 2 THEN role_id END) AS 三日留存, COUNT(CASE WHEN day_diff = 7 THEN role_id END) AS 七日留存 FROM temp_1 GROUP BY create_date; ``` 该语句按照每天的新注册用户分组,并分别统计当天新增用户数、次日留存用户数、第三日留存用户数和第七日留存用户数。 #### 结果解释 以上查询的结果将返回每一批新增用户的留存状况。例如,在某一天新增了100位用户,则可以根据后续几天的实际登录人数得出相应的百分比作为留存率。比如如果第二天有60人再次访问应用,则次日留存率为60%;同理可得其他周期的数值[^3]。 #### 注意事项 - **数据清洗**:确保原始数据质量良好,无重复记录或异常值干扰分析结果。 - **性能优化**:当处理大规模历史数据时需考虑索引设计及分区策略以提升执行效率。 --- ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值