sql用户留存率细致讲解

以牛客面试题为例

SQL9 2021年11月每天新用户的次日留存率

• 用户行为日志表tb_user_log

• 问题：统计2021年11月每天新用户的次日留存率（保留2位小数）
  ！！注：
  次日留存率位当天新增的用户数中第二天又活跃了的用户数占比
  如果in_time（进入时间）和out_time （离开时间） 跨天了，在两天里都记为该用户活跃过，结果按日期升序。
  

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (103, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (101, 9002, '2021-11-02 10:00:09', '2021-11-02 10:00:28', 0),
  (103, 9002, '2021-11-02 10:00:51', '2021-11-02 10:00:59', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (101, 9003, '2021-11-03 11:00:55', '2021-11-03 11:01:24', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (101, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0);

分析

• 问题：统计2021年11月每天新用户的次日留存率（问题扩大为计算每天的留存率，也可以是三日留存率或七日留存率问题

• 次日留存率=第二天继续活跃的人数/当天新增的用户总数

• 最终思想：得到每个用户的注册日期和对应的在线日期（考虑到在线日期可能跨天，所有要将登陆日期和退出日期量表进行纵向连接，即可说明这两天均为在线日期）

字段分解

• 每个用户对应的注册日期

select uid,date(min(in_time)) 注册日期 
from tb_user_log

• 得到每个用户对应的在线日期

…这里将可能会跨天的登录日期和退出日期都算进来了

select  uid,date(in_time) 在线日期
from tb_user_log) t1
union 
select uid,date(out_time) 在线日期
from tb_user_log) t2

• 以dt作为分组，以uid 相等，和第二天的用户等于第一天的用户 为条件连接两表

select *  from (
select  uid,min(date(in_time)) dt from tb_user_log 
group by  uid) t1
left join 
select uid,date(in_time) dt from tb_user_log) t2
on t1.uid=t2.uid ;

bcb80e71450.png)
-t1表为第一天出现，t2表中如果继续出现才会有值

• 再根据次日留存率=第二天继续出现的人数/当天的新用户。

select 注册日期,
	count(distinct t1.uid) 新增用户数,
    sum(datediff(在线日期,注册日期)=1) 次日留存用户数,
--     sum(datediff(t2.dt,t1.dt)<=3) 三日留存用户数,
--     sum(datediff(t2.dt,t2.dt)<=7) 七日留存用户数
	sum(datediff(在线日期,注册日期)=1)/count(distinct t1.uid)  次日留存率
    from
(select uid,min(date(in_time)) 注册日期  #注册表
from tb_user_log  
group by uid) t1
left join 
#表2为，每个用户所有出现的日期

(select uid,date(in_time) 在线日期
from tb_user_log  		#登录表
union   
select uid,date(out_time) 在线日期 
from tb_user_log ) t2
on t1.uid=t2.uid 
where date_format(注册日期,'%Y-%m')='2021-11'
group by 注册日期
order by 注册日期;