关于mysql:代码简洁主义用一条SQL统计留存LTV付费留存

序言

对于常见的统计语句网上没什么文章，根本都不是很简洁或者一条条SQL的重叠出统计语句，各种子查问嵌套重大。刚好最近又重写统计表，记录一下新办法的成绩，顺便不便宽广面向搜寻编程的哥们有个思路参考，有不对的中央欢送大神斧正哈 !

留存统计

• 留存统计外围性能只须要两个表中的两个字段就能统计分明，要害表及字段如下：

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

登录表login_log,蕴含user_id（用户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');

• 接下来就是关键步骤了，联表并解决数据，简略说说解决数据的思路：
• 艰深的说，留存的算法为登录那天 - 注册那天=（N）留。比方：次留就是 登录那天-注册那天= (1)留。

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)=3) 四日留存用户数,    sum(datediff(login_time,reg_time)=7) 八日留存用户数,    sum(datediff(login_time,reg_time)=1)/count(distinct users.user_id)*100 次日留存率,    sum(datediff(login_time,reg_time)=3)/count(distinct users.user_id)*100 四日留存率,    sum(datediff(login_time,reg_time)=7)/count(distinct users.user_id)*100 八日留存率from users left join login_log on users.user_id=login_log.user_idgroup by date(reg_time);

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