数据源：来自猴子老师的留存率分析教程
链家面试题：如何分析留存率？mp.weixin.qq.com【面试题】
手机中的相机是深受大家喜爱的应用之一，下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。
用户id：用户唯一标识；
应用名称：是手机中的某个应用，例如相机、微信、大众点评等。
启动时长：某一天中使用某应用多长时间（分钟）。启动次数：某一天中启动了某应用多少次。
登陆时间：使用手机的日期。例如2018-05-01。
现在该手机厂商想要分析手机中的应用（相机）的活跃情况，需统计如下数据：
某日活跃用户（用户id）在后续的一周内的留存情况（计算次日留存用户数，3日留存用户数，7日留存用户数）
指标定义：
某日活跃用户数，某日活跃的去重用户数。
N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。
N日活跃留存率，N日留存用户数/某日活跃用户数
例：登陆时间（20180501日）去重用户数10000，这批用户在20180503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%
求解过程：
第一步：通过数据自联结，然后找出数据a和数据b都是相机，且b的时间小于a的时间的数据（例如a中日期为2020-05-02，b中则为5月2日之后的数据）。
select a.用户id, a_t, b_t from
(select 用户id, 登陆时间 a_t from 留存率题目
where 应用名称 = '相机') a left join
(select 用户id, 登陆时间 b_t from 留存率题目
where 应用名称 = '相机') b
on a.用户id = b.用户id and a_t < b_t[1]
第二步：求出at和 b_t 的时间间隔，使用函数datediff[2]。
select *, datediff(b_t, a_t) as 时间间隔 from
(select a.用户id, a_t, b_t from
(select 用户id, 登陆时间 a_t from 留存率题目
where 应用名称 = '相机') a left join
(select 用户id, 登陆时间 b_t from 留存率题目
where 应用名称 = '相机') b
on a.用户id = b.用户id and a_t < b_t) c
第三步：使用case when分组计算
select a_t, count(distinct case when 时间间隔=1 then 用户id else null end) 次日留存数,
count(distinct case when 时间间隔=1 then 用户id else null end)/count(distinct 用户id) 次日留存率,
count(distinct case when 时间间隔=3 then 用户id else null end) 3日留存数,
count(distinct case when 时间间隔=3 then 用户id else null end)/count(distinct 用户id) 3日留存率,
count(distinct case when 时间间隔=7 then 用户id else null end) 7日留存数,
count(distinct case when 时间间隔=7 then 用户id else null end)/count(distinct 用户id) 7日留存率
from
(select *, datediff(b_t, a_t) as 时间间隔 from
(select a.用户id, a_t, b_t from
(select 用户id, 登陆时间 a_t from 留存率题目
where 应用名称 = '相机') a left join
(select 用户id, 登陆时间 b_t from 留存率题目
where 应用名称 = '相机') b
on a.用户id = b.用户id and a_t < b_t) c) d
group by a_t
总结：1、涉及到时间间隔，要考虑自连表的使用；
2、时间间隔的计算，datediff和timestampdiff的用法区别；
3、分组计算case when的用法。
参考
- ^注意datediff的格式为datediff（a,b），输出的是a-b的结果；此处也可以使用timestampdiff(day,a,b),输出结果为b-a。此外，datediff只用来计算以天为单位的时间间隔，timestampdiff则可以计算month/day/hour/second等时间间隔
版权声明：本文为weixin_39640909原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。