mysql 如何计算某段时间内的n日留存率？

什么是留存率，留存率怎么计算？
数据表user_login长这样：

mysql> select * from user_login limit 5;
+--------+------------+
| userid | datetime   |
+--------+------------+
|   1003 | 2020-01-02 |
|   1001 | 2021-01-02 |
|   1002 | 2021-01-03 |
|   1003 | 2021-01-02 |
|   1004 | 2020-01-02 |
+--------+------------+
5 rows in set (0.05 sec)

设定变量@datetime1为新增用户数的日期，变量@days为第n天登陆的天数。
假设我们现在要求"2020-01-02"登陆的1天留存率。

set @datetime1 = "2020-01-02"; 
set @days = 1;

$$1天留存率=\frac{\text{第二天登录的当天新增用户数}}{当天新增用户数}$$

第一步，得到和注册日期相连的表，同时加上条件——注册日期在"2020-01-02"这一天的行。将这张表记为t1

select *
from user_login as A
	left join (
		select userid, min(datetime) as 注册日期
		from user_login
		group by userid
		) as B
	on A.userid = B.userid
where 注册日期 = @first_date;

/*显示前五行：*/
+--------+------------+--------+------------+
| userid | datetime   | userid | 注册日期   |
+--------+------------+--------+------------+
|   1003 | 2020-01-02 |   1003 | 2020-01-02 |
|   1003 | 2021-01-02 |   1003 | 2020-01-02 |
|   1004 | 2020-01-02 |   1004 | 2020-01-02 |
|   1006 | 2020-01-02 |   1006 | 2020-01-02 |
|   1003 | 2021-01-02 |   1003 | 2020-01-02 |
+--------+------------+--------+------------+
5 rows in set (0.01 sec)

第二步：分别统计这张表的去重userid个数和在指定约束(n天留存约束，即登录时间等于注册日期+n)下的userid去重个数。

select count(distinct case when datetime=date_add(注册日期, interval @days day) then userid end)
 						/ 
	   count(distinct userid) as 次日留存率
from t1

完整代码：

set @datetime1 = "2020-01-02"; 
set @days = 1;

select count(distinct case when datetime=date_add(注册日期, interval @days day) then A.userid end)
 						/ 
	   count(distinct A.userid) as 次日留存率
from user_login as A
	left join (
		select userid, min(datetime) as 注册日期
		from user_login
		group by userid
		) as B
	on A.userid = B.userid
where 注册日期 = @datetime1;

代码细节：记得在统计分子、分母个数时加上distinct。

代码精髓：对于既需要在整体的表进行聚合函数操作(如count)，也需要在加入约束下的表进行聚合函数操作的情况，可以用case when变相生成约束表。

拓展：如果是要求"2020-01-01"至"2020-02-01"时间内的7天留存率，只需要稍稍改一下条件即可。

set @datetime1 = "2020-01-01"; 
set @datetime2 = "2020-02-01"; 
set @days = 7;

select count(distinct case when datetime=date_add(注册日期, interval @days day) then A.userid end)
 						/ 
	   count(distinct A.userid) as 次日留存率
from user_login as A
	left join (
		select userid, min(datetime) as 注册日期
		from user_login
		group by userid
		) as B
	on A.userid = B.userid
where 注册日期 between @datetime1 and @datetime2;