序言
对于常见的统计语句网上没什么文章,根本都不是很简洁或者一条条 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');
- 接下来就是关键步骤了,联表并解决数据,简略说说解决数据的思路:
- 艰深的说,留存的算法为 登录那天 – 注册那天 =(N)留 。比方: 次留 就是 登录那天 - 注册那天 = (1)留。
- 当咱们两张表中含有 注册工夫 跟登录工夫 时,最奢侈的想法应该是使用上述例 1 的公式间接统计出留存。
- 联表并以含有 注册工夫 的users表为主表,登录表为从表,合成一张大表数据后对注册工夫分组统计。
- 联表如下:(from users left join login_log on users.user_id = login_log.user_id)、分组如下:(group by date(users.reg_time))
- 应用
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_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。相比嵌套的子查问简洁了很多。