关于学习怎样查询次日留存

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

目录

前言

一,什么是留存率？

二,什么是次日留存率？

三、如何计算次日留存率

1,计算特定某一天的次日留存率

2,关于不限定日期的次日留存怎么写

四,如何计算7日留存率或者30日留存率

五,怎样用窗口函数优化代码

六,总结

---

前言

数据分析的岗位考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的次日留存率