用一条SQL统计留存、LTV、付费留存 -----代码简洁主义

序言

关于常见的统计语句网上没什么文章，基本都不是很简洁或者一条条SQL的堆叠出统计语句，各种子查询嵌套严重。刚好最近又重写统计表，记录一下新方法的成果，顺便方便广大面向搜索编程的哥们有个思路参考，有不对的地方欢迎大神指正哈 !😘

留存统计

• 留存统计核心功能只需要两个表中的两个字段就能统计清楚，关键表及字段如下：

用户表users，包含user_id（用户ID）和reg_time（注册日期）的字段。

用户ID	注册时间
user_id	reg_time

登录表login_log,包含user_id（用户ID）和login_time（登录时间）

用户ID	登录时间
user_id	login_time

• 创建并添加表格测试数据，2张表的时间都是datetime类型 YYYY-MM-DD HH:MM:SS

-- 用户注册表
create table users(user_id varchar(10) primary key,reg_time datetime);

insert into users values
('u_01','2020-01-01 09:15:00'),
('u_02','2020-01-01 00:04:00'),
('u_03','2020-01-01 22:16:00'),
('u_04','2020-01-01 20:32:00'),
('u_05','2020-01-01 13:59:00'),
('u_06','2020-01-01 21:28:00'),
('u_07','2020-01-01 14:03:00'),
('u_08','2020-01-01 11:00:00'),
('u_09','2020-01-01 23:57:00'),
('u_10','2020-01-01 04:46:00'),
('u_11','2020-01-02 14:21:00'),
('u_12','2020-01-02 11:15:00'),
('u_13','2020-01-02 07:26:00'),
('u_14','2020-01-02 10:34:00'),
('u_15','2020-01-02 08:22:00'),
('u_16','2020-01-02 14:23:00'),
('u_17','2020-01-03 09:20:00'),
('u_18','2020-01-03 11:21:00'),
('u_19','2020-01-03 12:17:00'),
('u_20','2020-01-03 15:26:00');

-- 登陆日志表
create table login_log(user_id varchar(10),login_time datetime,primary key(user_id,login_time));

insert into login_log values
('u_02','2020-01-02 00:14:00'),
('u_10','2020-01-02 08:32:00'),
('u_03','2020-01-02 09:20:00'),
('u_08','2020-01-02 10:07:00'),
('u_04','2020-01-02 10:29:00'),
('u_09','2020-01-02 11:45:00'),
('u_05','2020-01-02 12:19:00'),
('u_01','2020-01-02 14:29:00'),
('u_15','2020-01-03 00:26:00'),
('u_14','2020-01-03 11:18:00'),
('u_11','2020-01-03 13:18:00'),
('u_16','2020-01-03 14:33:00'),
('u_06','2020-01-04 07:51:00'),
('u_18','2020-01-04 08:11:00'),
('u_07','2020-01-04 09:27:00'),
('u_10','2020-01-04 10:59:00'),
('u_20','2020-01-04 11:51:00'),
('u_03','2020-01-04 12:37:00'),
('u_17','2020-01-04 15:07:00'),
('u_08','2020-01-04 16:35:00'),
('u_01','2020-01-04 19:29:00'),
('u_14','2020-01-05 08:03:00'),
('u_12','2020-01-05 10:27:00'),
('u_15','2020-01-05 16:33:00'),
('u_19','2020-01-06 09:03:00'),
('u_20','2020-01-06 15:26:00'),
('u_04','2020-01-08 11:03:00'),
('u_05','2020-01-08 12:54:00'),
('u_06','2020-01-08 19:22:00'),
('u_13','2020-01-09 10:20:00'),
('u_15','2020-01-09 16:40:00'),
('u_18','2020-01-10 21:34:00');

• 接下来就是关键步骤了，将用户表和登录表联表并处理数据，简单说说处理数据的思路：

1. 通俗的说，留存的算法为 以user_id相连，count(或sum)计算登录那天人数 / 注册那天人数=登录那天（N）留率。 比如：三留 就是 sum(登录第三天的登录数) / 注册那天人数= (3)留。
2. 当我们两张表中含有注册时间跟登录时间时，最朴素的想法应该是运用上述例1的公式直接统计出留存。
3. 联表并以含有注册时间的users表为主表，登录表为从表，合成一张大表数据后对注册时间分组统计。
4. 联表如下：（from users left join login_log on users.user_id = login_log.user_id）、分组依据注册时间：（group by date(users.reg_time)）
5. 使用sum(datediff(login_time, reg_time) = 1) as ‘次日留存用户数’ 来分别求同一注册时间的其他几个纬度的用户留存情况

最终代码如下：~~~~

select 
    date(reg_time) dt,
    count(distinct users.user_id) 新增用户数,
    sum(datediff(login_time,reg_time)=1) 次日留存用户数,
    sum(datediff(login_time,reg_time)=2) 三日留存用户数,
    sum(datediff(login_time,reg_time)=6) 七日留存用户数,
    sum(datediff(login_time,reg_time)=1)/count(distinct users.user_id)*100 次日留存率,
    sum(datediff(login_time,reg_time)=2)/count(distinct users.user_id)*100 三日留存率,
    sum(datediff(login_time,reg_time)=8)/count(distinct users.user_id)*100 七日留存率
from users left join login_log on users.user_id=login_log.user_id
group by date(reg_time);

日期	新增用户数	次日留存用户数	四日留存用户数	八日留存用户数	次日留存率	四日留存率	八日留存率
2020-01-01	10	8	6	3	80.00%	60.00%	30.00%
2020-01-02	6	4	3	2	66.67%	50.00%	33.33%
2020-01-03	4	3	2	1	75.00%	50.00%	25.00%

得出的留存结果与数据是完全一致的😊，同理也能用这种方法得出付费留存和LTV。相比嵌套的子查询简洁了很多。