sql 怎么统计一段时间的用户数_SQL 计算留存率-链家面试题-CSDN博客

本文介绍了如何使用SQL来统计一段时间内的活跃用户数和计算留存率，重点在于自联结表格和CASE WHEN语句的运用。通过案例分析了手机厂商如何分析相机应用的用户行为，提供了具体的SQL解决方案。

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

感谢猴子的SQL面试系列课程

免费教程《图解SQL面试题》mp.weixin.qq.com

将数据导入navicat

手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图

现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：

需要获得的数据的格式如下：

计算时间间隔类问题，使用自联结

首先计算活跃用户数

select 登陆时间 ,count(distinct 用户id) as 活跃用户数 
from 登录信息
where 应用名称 ='相机' 
group by 登陆时间

将表进行自连接，得到如下形式的格式对于计算留存率十分关键

select a.`用户`,a.登陆时间 as a_t ,b.登陆时间 as b_t
from 登录信息 as a  
left join 登录信息 as b
on a.`用户`=b.`用户`
where a.应用名称= '相机' AND b.应用名称='相机';

将上表存成视图C

再计算时间间隔

select *,timestampdiff(day,a_t,b_t) 
         as 时间间隔
from C

使用case when 计算次日留存

select d.a_t,count(distinct case when d.时间间隔=1 then d.用户id     
               else null
               end) as  次日留存数 
from
(select *,timestampdiff(day,a_t,b_t) as 时间间隔
from c) as d
group by d.a_t;

select d.a_t,count(distinct case when d.时间间隔=1 then d.用户id     
               else null
               end) as  次日留存数, 
count(distinct case when 时间间隔=1 then d.用户id
               else null
               end) /count(distinct d.用户id) as 次日留存率,
count(distinct case when d.时间间隔=3 then d.用户id     
               else null
               end) as  3日留存数 ,
count(distinct case when 时间间隔=3 then d.用户id
               else null
               end) /count(distinct d.用户id) as 3日留存率,
count(distinct case when d.时间间隔=7 then d.用户id     
               else null
               end) as  7日留存数 ,
count(distinct case when 时间间隔=7 then d.用户id
               else null
               end) /count(distinct d.用户id) as 7日留存率

from
(select *,timestampdiff(day,a_t,b_t) as 时间间隔
from c) as d
group by d.a_t;

再整合起来

select d.a_t,count(distinct case when d.时间间隔=1 then d.用户id     
               else null
               end) as  次日留存数, 
count(distinct case when 时间间隔=1 then d.用户id
               else null
               end) /count(distinct d.用户id) as 次日留存率,
count(distinct case when d.时间间隔=3 then d.用户id     
               else null
               end) as  3日留存数 ,
count(distinct case when 时间间隔=3 then d.用户id
               else null
               end) /count(distinct d.用户id) as 3日留存率,
count(distinct case when d.时间间隔=7 then d.用户id     
               else null
               end) as  7日留存数 ,
count(distinct case when 时间间隔=7 then d.用户id
               else null
               end) /count(distinct d.用户id) as 7日留存率

from
(select *,timestampdiff(day,a_t,b_t) as 时间间隔
from (select a.`用户id`,a.登陆时间 as a_t ,b.登陆时间 as b_t
from 登录信息 as a  
left join 登录信息 as b
on a.`用户id`=b.`用户id`
where a.应用名称= '相机' AND b.应用名称='相机') as c) as d
group by d.a_t;

留存率类问题关键点：

得到自联结的表十分重要，是后面计算时间间隔的关键

分组统计一定要使用 case when 语句

再次感谢猴子老师的练习数据和参考思路。