提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
目录
前言
数据分析的岗位考sql题的时候，查询用户留存率出现的频率应该是蛮高的。
参考b站董旭阳TonyDong董老师的SQL面试题：用户留存率分析这期视频，加入了自己的一些理解和思路整理。
一,什么是留存率？
某一天新增用户在之后的第n天仍然登录的比率，称为留存率
二,什么是次日留存率？
某一天新增用户在之后的第1天仍然登录的比率，称为次日留存率
举例：某一天2022/11/17同一批用户新注册=全部登录，注册后一天2022/11/18这批用户中有多少还在登录
扩展：跟计算连续登陆的平均概率的题也会有点像，比如2022/11/13某一天同一批用户新注册了，过了一段时间，2022/11/17这批用户有多少还在登录，后一天2022/11/18这批用户中有多少还在登录,
不管是算次日留存还是连续登录，重点是同一批用户，相邻的两天的这批用户中登陆的数量
三、如何计算次日留存率
1,计算特定某一天的次日留存率
因为是分析某一天（注册时间）新增用户的留存，所以先要规定着某一天（注册时间）是哪一天。
where tu.register_time between ‘2022-11-13 00:00:00’ and ‘2022-11-13 23:59:59’
#或者register_time只有日期
where tu.register_time =‘2022-11-13’b.获取登录行为，跟用户登录表（t_use_login)链接
连表时有两点要注意：
1，因为2022-11-13注册的用户在2022-11-14不一定登录，所以为了防止过滤掉2022-11-14没登录的用户，用left join 将用户注册表（t_user tu）和用户登录表（t_use_login)相连。若2022-11-14没登录，则显示null值
2，用户除了次日登陆以外，之后的时间也可能会有很多登录情况，为了避免只通过tu.id=tul.id链接，会有多条数据，增加链接删选条件，通过date（）提取日期
and date(tul.login_time)=date(tu.register_time)+interval 1 day
或者datediff=(tul.login_time,u.register_time)=1
select ...
from t_user tu
left join t_user_login tul
on tu.id=tul.id and date(tul.login_time)=date(tu.register_time)+interval 1 day
#datediff=(tul.login_time,u.register_time)=1
where tu.register_time =‘2022-11-13’c.由于多次登录行为，需要进行去重操作。
一个用户可能在同一天（2022-11-14这一天）登录多次，所以左链接后一个id会有多条数据。
去重1：所以计算2022-11-13注册的用户的时候需要distinct对用户注册表的id（tu.id)去重。
去重2:并且2022-11-14没有登录的用户为空，但没有关系，count计数是忽略null值的，但是还是因为多次登录行为，也需要用distinct对用户登录表的id（tul.id)去重
select count(distinct tul.id)/count(distinct tu.id) rrl
from t_user tu
left join t_user_login tul
on tu.id=tul.id and date(tul.login_time)=date(tu.register_time)+interval 1 day
#datediff=(tul.login_time,u.register_time)=1
where tu.register_time =‘2022-11-13’rrl就是2022-11-13的次日留存率
2,关于不限定日期的次日留存怎么写
用group by 进行分组分析，把register_time进行分组
2022-11-13注册的为一个区，2022-11-14注册的为一个区，2022-11-15注册的为一个区
每个区于此链接的登录用户也是这个区做代表注册时间的后一天，比如以上的这段代码就表示一个区，后面还会有好多这样的区。
select count(distinct tul.id)/count(distinct tu.id) rrl
from t_user tu
left join t_user_login tul
on tu.id=tul.id and date(tul.login_time)=date(tu.register_time)+interval 1 day
#datediff=(tul.login_time,u.register_time)=1
group by register_time四,如何计算7日留存率或者30日留存率
跟上述的方式一样，只需要改动interval后的时间，然后继续跟用户注册表左链接就ok了，连接键的写法虽然跟次日留存一样。
但是链接的时候链接键依然是多出来的字段（t1.id,t2.id,t3,id)，所以用户登录表的别名要更改成t1,t2,t3。
并且用户注册表上面的id（tu.id)会根据不断左链接，记录会不断增多的同时，（t1.id,t2.id,t3,id)三个字段的中的记录是不一样的，因为interval的时间间隔不同，存在的null值也不同
select count(distinct t1.id)/count(distinct tu.id) rrl
count(distinct t2.id)/count(distinct tu.id) rr7
count(distinct t3.id)/count(distinct tu.id) rr30
from t_user tu
left join t_user_login t1
on tu.id=tul.id and date(t1.login_time)=date(tu.register_time)+interval 1 day
left join t_user_login t2
on tu.id=tul.id and date(t2.login_time)=date(tu.register_time)+interval 7 day
left join t_user_login t3
on tu.id=tul.id and date(t3.login_time)=date(tu.register_time)+interval 30 day
group by register_time五,怎样用窗口函数优化代码
如果只是算次日留存代码就一次左链接就可以了，不用优化也可以。
那么如何用窗口函数优化代码呢？
1,首先将三个left join 简单化一下。原理是除了链接键这个条件以外的date条件用or相连，简化如下：
select ...
from t_user tu
left join t_user_login t1
on tu.id=tul.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
group by register_time窗口函数计数原理：之前使用count进行计数，这里用窗口函数计数的原理是排序，排序的最后一个序号就是数量。
注意：
1,这里不能用row_number()进行排序。因为还是存在同一天多次登录行为，链接后的用户注册表中的同一个tu.id会有多条数据，用row_number()进行排序后就是1,2,3,4...所有的行数没有去重。
2,这里不能用rank()排序。因为,rank()是1,2,2,4....这样，虽然并列保存但是并列后的下一个还是按照个数算。
所以要用desc_rank()进行排序，desc_rank()是1,2,2,3,3,4...这样，会达到去重的效果
可以理解为desc_rank()是去重的排序
2,分区reg_date，计数每天的分别有多少用户注册
并且需要按照注册时间(reg_date)分区，因为我们需要得到每天的注册用户一共有多少，然后按照用户id进行区内的排序，一个用户有多条记录也都是一个序号，下一个用户也是紧接着的序号。形成daily_reg这个排序的字段。
select tu.id,tul.id,date(tu.register_time) reg_date,date(t1.login_time) login_date
desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg
from t_user tu
left join t_user_login t1
on tu.id=tul.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
group by register_time3,分区login_date，计数每天的分别有多少用户在注册后相隔1天，7天，30天的日期登录
这里计算登录用户的个数稍稍有点不同。分区条件有两个，date(tu.register_time) reg_date,date(t1.login_time) login_date。因为同一个注册时间的区域里面对应的登录时间有相隔相隔1天，7天，30天的，所以对注册时间也分区后，就形成了同一天注册的用户在1天后登录的一个区，在7天后登陆的一个区，在30天登陆后的一个区。
每一行数据的含义：
区一：id1的用户2022-11-13注册了，2022-11-14登陆了
id2的用户2022-11-13注册了，2022-11-14登陆了
区二： id1的用户2022-11-13注册了，2022-11-20登陆了
..........
select tu.id,tul.id,date(tu.register_time) reg_date,date(t1.login_time) login_date
desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg
desc_rank()over(partition by date(tu.register_time),date(t1.login_time)order by tul.id ) daily_log
from t_user tu
left join t_user_login t1
on tu.id=tul.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
这里先扩展下with....as的用法：
发现MySQL8.0有个类似SQLserver中的表变量的语句，那就是with as，也叫做子查询部分subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。
with (子查询因子名字,如t1,t2) as (具体的子查询因子代码)
后面的所有主查询里面，这个t1,t2的这个子查询可以直接拿来用
例子引用CSDN博主「一只努力xx的程序媛」的原创文章
with temp_a as(select id,name from user where name='wangwang'),
temp_b as(select id,name from user where name='zhangzhang')
select (case when (SELECT count(*) FROM temp_a)>0 then -- 使用case when判断
(select id from temp_a order by id desc limit 1)
else
(select id from temp_b order by id desc limit 1)
end
) id
————————————————
版权声明：本文为CSDN博主「一只努力xx的程序媛」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_23888451/article/details/102686280
介绍完with..as..的用法，我们继续优化代码
4，算出这daily_reg，daily_log两个字段的最大值（最大的序号）
也就是就是每天注册的人数和，每天登录的人数。
我们已经写出能求出了含有每天注册的人的序号排序的字段daily_reg和每天登陆的人的序号排序的字段daily_log的sql语句，接下来我们可以把这个语句当作是一个子查询的部分用来继续算出这两个字段的最大值（最大的序号）就是每天注册的人数和，每天登录的人数。
with tt as(
select tu.id,tul.id,date(tu.register_time) reg_date,date(t1.login_time) login_date
desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg
desc_rank()over(partition by date(tu.register_time),date(t1.login_time)order by tul.id ) daily_log
from t_user tu
left join t_user_login t1
on tu.id=tul.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
)
select ....
from tt
两个窗口函数使每一条记录都会对应两个序号，只不过序号的1,2,2,2,3,3...这样的一组数据存在于不同的分区，每一个分区都会从1开始，到最后那个序号就是这个分区的注册人数或者是登录人数
但是daily_reg和daily_log的分区是不一样的，daily_reg需要group by一个字段，daily_log需要group by两个字段，所以没办法一步到位，需要两步计算然后链表。
with tt as(
select tu.id,t1.id,date(tu.register_time) reg_date,date(t1.login_time) login_date
desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg
desc_rank()over(partition by date(tu.register_time),date(t1.login_time)order by tul.id ) daily_log
from t_user tu
left join t_user_login t1
on tu.id=t1.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
),
select tu.id,t1.id,max(daily_reg)/max(daily_reg)
from
(select tu.id,t1.id,date(tu.register_time) reg_date,date(t1.login_time) login_date,max(daily_reg)
from tt
group by tu.register_time) t2
left join
(select tu.id,tul.id,date(tu.register_time) reg_date,date(t1.login_time) login_date,max(daily_reg)
from tt
group by t1.login_time) t3
on tu.id=t1.id
)t4以上是用一个with..as，然后链表
也可用用tt这个字查询因子，算出另一个含有max值的字查询因子tt2（注释部分很重要帮助理解）
with tt as(
select tu.id,t1.id,date(tu.register_time) reg_date,date(t1.login_time) login_date
desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg
desc_rank()over(partition by date(tu.register_time),date(t1.login_time)order by tul.id ) daily_log
from t_user tu
left join t_user_login t1
on tu.id=t1.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
),
#tt表示每一个id对应着注册时间和登录时间，没有登陆的算null，每一个注册时间的一个区里面的每一条记录都对应了不同的daily_reg序号，和每一个注册时间的区里面的每个登录时间的区里面的每一条记录都对应了不同的daily_log序号
tt2 as
(select date(tu.register_time) reg_date,date(t1.login_time) login_date,max(daily_reg),max(daily_log)
from tt
group by reg_date,login_date),
#tt2表必须group by一下两个tt1里面标序号的字段reg_date和login_dat2，利于算出分别是什么区的最大值。表示得到每一个注册时间的一个区里面的每一条记录都对应了同一个max(daily_reg)，和每一个注册时间的区里面的每个登录时间的区里面的每一条记录都对应了同一个max(daily_log)，因为group by的原因，注册时间就一个
5，条件分支log_date所对应的max(daily_log)
有了tt2这个字查询因子后，可以用tt2这个字查询因子，把max(daily_log)这个字段里面的记录进行条件分支成1天后登陆和7天后登陆和30天后登陆的三个字段。
或者像下面一样，每个条件对应该条件所在的reg_date区里面daily_log的最大值
with tt as(
select tu.id,t1.id,date(tu.register_time) reg_date,date(t1.login_time) login_date
desc_rank()over(partition by date(tu.register_time) order by tu.id) daily_reg
desc_rank()over(partition by date(tu.register_time),date(t1.login_time)order by tul.id ) daily_log
from t_user tu
left join t_user_login t1
on tu.id=t1.id
and date(t1.login_time)=date(tu.register_time)+interval 1 day
or date(t1.login_time)=date(tu.register_time)+interval 7 day
or date(t1.login_time)=date(tu.register_time)+interval 30 day
),
tt2 as
(select reg_date,
login_date,
max(daily_reg),max(daily_log)
from tt
group by reg_date,login_date
),
select reg_date,
max(case when login_date=reg_date+interval 1 day then daily_log end)/max(daily_reg)'rr1'
max(case when login_date=reg_date+interval 7 day then daily_log end)/ max(daily_reg) 'rr7'
max(case when login_date=reg_date+interval 7 day then daily_log end)/ max(daily_reg) 'rr30'
from tt2
group by reg_date
#此处记得group by reg_date，因为tt2表里有对reg_date分区过，这样max(daily_reg)对应的值也是所在那个区，这样计算rr1，rr7，rr30的时候，分母max(daily_reg)就刚好是对应的那个区
六,总结
计算留存率有两个方法：多表连接和窗口函数。
写代码的时候要注意，group by的成分，和链表的逻辑（链表后有什么记录是多条的，有什么记录是显示null值，有利于大家理解什么时候需要去重，每个区里面有怎样的记录。）
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
