留存率的定义与SQL实现

1.什么是留存率

留存率是指在特定时间段内，仍然继续使用某项产品或服务的用户占用户总数的百分比。

通常，留存率会以日，周，或月为单位进行统计和分析。

2.SQL留存率常见问题

1.计算新用户登录的日期的次日留存率以及3日留存率

CREATE TABLE ods_user_login_log (
    user_id VARCHAR(255) NOT NULL,
    login_dt VARCHAR(255) NOT NULL
);
INSERT INTO ods_user_login_log VALUES 
('001','20240701'),
('001','20240701'),
('002','20240701'),
('003','20240701'),
('001','20240702'),
('002','20240702'),
('002','20240702'),
('001','20240703'),
('002','20240704'),
('004','20240704')
;

有一张用户登录日志表 ods_usr_login_log，包含 user_id（用户ID）和 login_dt（登录日期）。每个用户在同一天可能登录多次。

问题：计算有新用户登录的日期的次日留存率和3日留存率。

N日留存用户数：指某日活跃的用户在第N日再次活跃的用户数量。

本道题思路如下：

找出每个用户首次登录的日期
使用子查询 t1，通过 MIN(login_dt) 函数获取每个用户的首次登录日期，并将其转换为日期格式 date。
将所有登录记录转换为日期格式
子查询 t2 将 login_dt 字段转换为日期格式。
计算次日留存率和3日留存率
- 在最终的 SELECT 语句中，使用 DATEDIFF(t2.dt, t1.dt) 来计算用户登录日期与其首次登录日期的天数差。
- 对于次日留存率，当天数差为1时，使用 COUNT(DISTINCT t2.user_id) 统计次日再次登录的用户数量，并将其除以首次登录的用户数量 COUNT(DISTINCT t1.user_id)，得到次日留存率。
- 对于3日留存率，类似地，当天数差为3时，计算第3日再次登录的用户数，并进行相应的计算。
LEFT JOIN 进行多表联查

为什么使用LEFT JOIN?
LEFT JOIN 的作用是保留左表中的所有记录（即使在右表中没有匹配项）。在这个SQL语句中，左表是子查询 t1，它包含每个用户的首次登录日期。右表是 t2，它包含用户的所有登录记录。通过 LEFT JOIN，我们可以确保所有首次登录的用户都会出现在最终的结果中，即使这些用户在后续的指定日期（如次日或第3日）没有再次登录。这样可以保证在计算留存率时，所有首次登录的用户都被纳入分母，即使他们在后续日期没有登录，确保留存率计算准确无误。

一分钟搞明白Join、Left Join、Right Join的区别_join left join right join-CSDN博客

-- 计算用户首次登录的日期
with t1 as (
  select  
     user_id,
     cast(min(login_dt) as date) as dt
     from ods_user_login_log
  group by user_id, cast(login_dt as date)
),
-- 将数据转为date格式
t2 as (
  select 
    user_id, cast(login_dt as date) as dt
  from ods_user_login_log
  group by user_id, cast(login_dt as date) 
)

select 
  t1.dt
  -- 确保用户在同一天登录的数据只被计算一次
,count(distinct case when datediff(t2.dt, t1.dt) = 1 then t2.user_id else null end) 
  / count(distinct t1.user_id) as retain_1d_rate
,count(distinct case when datediff(t2.dt, t1.dt) = 3 then t2.user_id else null end) 
 / count(distinct t1.user_id) as retain_3d_rate
from t1
left join t2
on t1.user_id = t2.user_id
group by t1.dt
;

2.2021年11月每天新用户的次日留存率_牛客题霸_牛客网

在本道题中，我们同样是计算新用户的次日留存率。

但数据列却完全不同，第一道题中，我们的数据列就只有登录时间。

在本题中，我们既有in_time进入时间，又有离开时间out_time。

那么就会存在一个情况：当在一条记录中，如果in_time-进入时间和out_time-离开时间跨天了。

如：(101, 9002, '2021-11-04 11:00:55', '2021-11-05 11:00:59', 0);

这时我们也算该用户在两天里都活跃过

那么，我们该如果计算，才能实现该逻辑呢？

具体来说，就是将in_time和out_time合并为同一个数据列，记录每个用户在每天的活跃情况。

select uid, date(in_time) as active_dt from tb_user_log
   union all
select uid, date(out_time) as active_dt from tb_user_log

具体来说，我们就是把out_time也算作是活跃日。

在这里我们使用UNION ALL，是因为：

它可以保留所有的用户行为记录，包括重复的日期。这对于准确计算次日留存率很重要。
我们需要考虑每个用户可能在同一天多次访问的情况。使用 UNION ALL 确保了我们捕获到所有这些访问记录。（然后distinct去重）

剩下的操作就跟第一道题一样，计算用户首次登录的日期，然后通过计算datediff(end_time, start_time)来确定日期间隔。

with t1 as (
    select 
    uid,
    min(in_time) as dt
    from 
    tb_user_log
    group by uid
),
t2 as (
    select uid, date(in_time) as active_dt from tb_user_log
    union all
    select uid, date(out_time) as active_dt from tb_user_log
)
    select 
     date_format(t1.dt,'%Y-%m-%d') as dt,
     round(count(distinct case when datediff(t2.active_dt,t1.dt) = 1 then t2.uid else null end) /
     count(distinct t1.uid),2) as uv_left_rate
     from t1
     left join t2
     on t1.uid = t2.uid
     where date_format(t1.dt,'%Y-%m') = '2021-11'
     -- t1.dt BETWEEN '2021-11-01' AND '2021-11-30'
     group by date_format(t1.dt,'%Y-%m-%d')

优化小建议：

使用 DATE_FORMAT(t1.dt, '%Y-%m') = '2021-11' 进行筛选，这样的条件会在查询时对所有结果再次进行格式化，可能会影响性能。（格式化 + 筛选）

使用 t1.dt BETWEEN '2021-11-01' AND '2021-11-30' 进行时间范围过滤，这样的条件可以利用之前已经格式化好的数据，性能更好。（只有筛选）