留存率问题（一）
题目一：
用户行为日志表tb_user_log
|
id |
uid |
artical_id |
in_time |
out_time |
sign_cin |
|
1 |
101 |
0 |
2021-11-01 10:00:00 |
2021-11-01 10:00:42 |
1 |
|
2 |
102 |
9001 |
2021-11-01 10:00:00 |
2021-11-01 10:00:09 |
0 |
|
3 |
103 |
9001 |
2021-11-01 10:00:01 |
2021-11-01 10:01:50 |
0 |
|
4 |
101 |
9002 |
2021-11-02 10:00:09 |
2021-11-02 10:00:28 |
0 |
|
5 |
103 |
9002 |
2021-11-02 10:00:51 |
2021-11-02 10:00:59 |
0 |
|
6 |
104 |
9001 |
2021-11-02 11:00:28 |
2021-11-02 11:01:24 |
0 |
|
7 |
101 |
9003 |
2021-11-03 11:00:55 |
2021-11-03 11:01:24 |
0 |
|
8 |
104 |
9003 |
2021-11-03 11:00:45 |
2021-11-03 11:00:55 |
0 |
|
9 |
105 |
9003 |
2021-11-03 11:00:53 |
2021-11-03 11:00:59 |
0 |
|
10 |
101 |
9002 |
2021-11-04 11:00:55 |
2021-11-04 11:00:59 |
0 |
（uid-用户ID, artical_id-文章ID, in_time-进入时间, out_time-离开时间, sign_in-是否签到）
问题：统计2021年11月每天新用户的次日留存率（保留2位小数）
注：
- 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
- 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。
输出示例：
示例数据的输出结果如下
|
dt |
uv_left_rate |
|
2021-11-01 |
0.67 |
|
2021-11-02 |
1.00 |
|
2021-11-03 |
0.00 |
解释：
11.01有3个用户活跃101、102、103，均为新用户，在11.02只有101、103两个又活跃了，因此11.01的次日留存率为0.67；
11.02有104一位新用户，在11.03又活跃了，因此11.02的次日留存率为1.00；
11.03有105一位新用户，在11.04未活跃，因此11.03的次日留存率为0.00；
11.04没有新用户，不输出。
方法一：
#一般看留存率的题目都可以使用左连接，这样第二天的数据如果缺少就会是null值，非常的常用
select a.dt,round(count(distinct b.uid)/count(a.uid),2) as uv_left_rate from (
#distinct 排除一些同一天登录好几次的情况，count(a.uid)就代表新增用户数，应为是每个用户的最小日期
select uid,min(date(in_time)) as dt
from tb_user_log
group by uid ) a
#a表记录新增用户数，注意group by uid：要统计每一个用户的新增，所以用group by
left join
(select uid,date(in_time) as dt
from tb_user_log
union #union all不去重 而union则会自动过滤重复的行
select uid,date(out_time) as dt
from tb_user_log
) b
#b 表是对in_time,out-time进行全连接，表示用户的所有登录日期，注意不是用group by uid:要统计全部信息，不能进行分组,union 不是union all
on a.uid = b.uid and timestampdiff(day,a.dt,b.dt) = 1
#a,b进行左连接，日期相隔一天的满足条件，否则为null
where a.dt like '2021-11%' #日期为2021-11月
group by a.dt 要统计每位用户，所以连接表要用group by
order by dt
方法二：
select
a.dt,
ifnull(round(count(distinct b.uid)/count(a.uid),2),0) uv_left_rate
from
# 每个用户最初注册的日期
(SELECT uid,date(min(in_time)) dt
FROM tb_user_log
GROUP BY uid) a
# 把日期拆分成登陆和退出日期，再通过uid联立表
# 查找每个用户注册日期增加1天后的日期是否在登陆和退出日期当中
left join
(
SELECT uid,DATE(in_time) dt
FROM tb_user_log
union
SELECT uid,DATE(out_time) dt
FROM tb_user_log
) b
ON a.uid = b.uid
# 每个uid在初次登陆日期的第二天
and b.dt = DATE_ADD(a.dt,INTERVAL 1 DAY)
# WHERE的时机，在全部表联合完之后进行筛选
WHERE a.dt like "2021-11%"
GROUP BY a.dt
ORDER BY a.dt;
方法三：
先查询出每个用户第一次登陆时间（最小登陆时间）--每天新用户表
因为涉及到跨天活跃，所以要进行并集操作，将登录时间和登出时间取并集，这里union会去重--用户活跃表
将每天新用户表和用户活跃表左连接，只有是同一用户并且该用户第2天依旧登陆才会保留整个记录，否则右表记录为空
得到每天新用户第二天是否登陆表后,开始计算每天的次日留存率：根据日期分组计算，次日活跃用户个数/当天新用户个数
select t1.dt,round(count(t2.uid)/count(t1.uid),2) uv_rate
from (select uid
,min(date(in_time)) dt
from tb_user_log
group by uid) as t1 -- 每天新用户表
left join (select uid , date(in_time) dt
from tb_user_log
union
select uid , date(out_time)
from tb_user_log) as t2 -- 用户活跃表
on t1.uid=t2.uid
and t1.dt=date_sub(t2.dt,INTERVAL 1 day)
where date_format(t1.dt,'%Y-%m') = '2021-11'
group by t1.dt
order by t1.dt
题目二：与上题不同的是，没有新用户则输出0
牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:
|
id |
user_id |
client_id |
date |
|
1 |
2 |
1 |
2020-10-12 |
|
2 |
3 |
2 |
2020-10-12 |
|
3 |
1 |
2 |
2020-10-12 |
|
4 |
2 |
2 |
2020-10-13 |
|
5 |
1 |
2 |
2020-10-13 |
|
6 |
3 |
1 |
2020-10-14 |
|
7 |
4 |
1 |
2020-10-14 |
|
8 |
4 |
1 |
2020-10-15 |
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户
......
第4行表示user_id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户
......
最后1行表示user_id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:
|
date |
p |
|
2020-10-12 |
0.667 |
|
2020-10-13 |
0.000 |
|
2020-10-14 |
1.000 |
|
2020-10-15 |
0.000 |
查询结果表明:
2020-10-12登录了3个(user_id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(user_id为4)新用户，2020-10-15，user_id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;
2020-10-15没有新用户登录，输出0.000;
(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)
方法一：
SELECT a.date, ROUND(COUNT(b.user_id) * 1.0/COUNT(a.user_id), 3) AS p
FROM (
SELECT user_id, MIN(date) AS date
FROM login
GROUP BY user_id) a
LEFT JOIN login b
ON a.user_id = b.user_id
AND b.date = date(a.date, '+1 day')
GROUP BY a.date
UNION
SELECT date, 0.000 AS p
FROM login
WHERE date NOT IN (
SELECT MIN(date)
FROM login
GROUP BY user_id)
ORDER BY date;
方法二：
这里就是用的上面的那个思路，只是要注意的是，这里要求的是不管是否有新用户，每天的数据都要列出来，所以在计算出表中有数据的日期外，还要添加没有出现的日期的次日留存率，用union进行合并。这一种方法比较直观，同样还有一种通过case when来做的，思想是相同的，只是利用case when把统计的过程放在了一起。
select a.date,round(count(distinct login.user_id)/ count(a.user_id),3) as p
from (select user_id, min(date) as date from login group by user_id) as a
left join login 相当于题目一的b表，因为没有in_time和date_time,所以直接连接b表，不需要在b表内部进行union
on login.user_id=a.user_id and login.date=DATE_ADD(a.date,INTERVAL 1 DAY)
group by a.date
Union 注意：题目一没有新用户不输出，题目二没有新用户需要输出0，所以多了用union连接
select date,0.000 as p
from login
where date not in(select min(date) from login group by user_id)
order by date;
写SQL感觉就是在做指标拆解，对于不能直接获取的需求，都考虑通过其数学表达式进行拆分，如果仍不能直接获取就继续拆分，直到所有的需求都能用数据库的数据直接表达出来，然后对这些表达式进行组合优化，得到sql的最优解。
问题三：上面计算不同日期的次日留存，本题计算总次日留存率
题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。
根据示例，你的查询应返回以下结果：
这里没有限制日期，所以求得是所有用户的平均次日留存率，由于会存在同一用户在一天重复登录的情况，所以要对用户进行去重，这里要注意在引用表的时候首先进行去重，而不是在统计数量的时候去重，其他就是照着上面第二步，对数据进行一次左链接并计数。还有一点就是date数据的数量关系，如果更专业一点应该用日期函数date_sub、datediff进行加减。
select count(q2.device_id)/count(q1.device_id)
from (select distinct device_id,date from question_practice_detail) q1
left join (select distinct device_id,date from question_practice_detail) q2
on q1.device_id=q2.device_id and q2.date=q1.date+1
注意：因为计算的不是每位用户的，是平均即所有用户的所以不用分组
方法二：
|
SELECT COUNT(distinct q2.device_id,q2.date)/count(DISTINCT q1.device_id,q1.date) as avg_ret from question_practice_detail as q1 left outer join question_practice_detail as q2 on q1.device_id=q2.device_id and DATEDIFF(q2.date,q1.date)=1 |
思路是：
1. 需要知道两天都上线的人数
2. 需要知道第一天上线的人数
做法：
1. 用datediff区分第一天和第二天在线的device_id
2. 用left outer join做自表联结
3. 用distinct q2.device_id,q2.date做双重去重，找到符合条件的当天在线人数
问题四：
牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备.
有一个登录(login)记录表，简况如下:
|
id |
user_id |
client_id |
date |
|
1 2 3 4 |
2 3 2 3 |
1 2 2 2 |
2020-10-12 2020-10-12 2020-10-13 2020-10-13 |
第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
还有一个用户(user)表，简况如下:
|
id |
name |
|
1 2 3 |
tm fh wangchao |
还有一个客户端(client)表，简况如下:
|
id |
name |
|
1 |
pc |
|
2 |
ios |
|
3 |
anroid |
|
4 |
h5 |
请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:
|
u_n |
c_n |
date |
|
fh |
ios |
2020-10-13 |
|
wangchao |
ios |
2020-10-13 |
查询结果表明:
fh最近的登录日期在2020-10-13，而且是使用ios登录的
wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的
方法一：
select u.name as u_n,c.name as c_n,l.date as date
from login l,user u,client c
where l.user_id = u.id and
c.id = l.client_id and
(l.user_id,date) in (select user_id, max(date) from login group by user_id)
order by u.name
方法二：
先根据用户分组，查出每个用户登录的最新日期(一):
|
1 |
select user_id,max(date) from login group by login.user_id ; |
查找出来的应该这样的
然后查找出所有用户的名字，所有的登录设备，所有的登录日期(二)：
|
1 2 3 4 5 |
select user.name as u_n, client.name as c_n, login.date from login join user on login.user_id=user.id join client on login.client_id=client.id |
查找的结果应该是:
那么再根据用户id和最新的登录日期(一)，可以在所有的数据（二)里面，从而确定唯一一组数据，最后再按照名字排序(三)：
|
1 2 3 4 5 6 7 8 |
select user.name as u_n, client.name as c_n, login.date from login join user on login.user_id=user.id join client on login.client_id=client.id where (login.user_id,login.date) in (select user_id,max(date) from login group by login.user_id ) order by user.name; |
注意：
SELECT u.name AS u_n, c.name AS c_n, MAX(l.date) AS date
FROM login l LEFT JOIN user u ON l.user_id = u.id
LEFT JOIN client c ON l.client_id = c.id
GROUP BY u.name
ORDER BY u_n ASC;
这种经典错误之处在于，MAX(date)是随机的，并不是和其他数据一一对应的，所以老老实实用子查询找到最大日期；
