用sql写用户留存和留存率

统计留存数量和留存率
用mysql写的脚本

下面是登录日志表，包含两列数据，一列是客户号，一列是登录时间。
现在需要统计1-7日的留存和留存率，之前见过用自连接方式，如果7日就需要链接7次，效率偏低。如果我们自己左连接一次，使得user_id相等，那会出现，左表日期是全部日期，而他们的右表会连接到所有日期，我们条件中再加上左表的日期要小于右表的日期，那么剩下来的日期都是某一个客户留存的日期了。最后用右表日期减去左表日期，等于1，意味着一天后留存，2，代表两天后留存，3,4。。。以此类推。

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

insert into login_log valuseres
(‘user_02’,‘2022-07-02 00:14:00’),
(‘user_10’,‘2022-07-02 08:32:00’),
(‘user_03’,‘2022-07-02 09:20:00’),
(‘user_08’,‘2022-07-02 10:07:00’),
(‘user_04’,‘2022-07-02 10:29:00’),
(‘user_09’,‘2022-07-02 11:45:00’),
(‘user_05’,‘2022-07-02 12:19:00’),
(‘user_01’,‘2022-07-02 14:29:00’),
(‘user_15’,‘2022-07-03 00:26:00’),
(‘user_14’,‘2022-07-03 11:18:00’),
(‘user_11’,‘2022-07-03 13:18:00’),
(‘user_16’,‘2022-07-03 14:33:00’),
(‘user_06’,‘2022-07-04 07:51:00’),
(‘user_18’,‘2022-07-04 08:11:00’),
(‘user_07’,‘2022-07-04 09:27:00’),
(‘user_10’,‘2022-07-04 10:59:00’),
(‘user_20’,‘2022-07-04 11:51:00’),
(‘user_03’,‘2022-07-04 12:37:00’),
(‘user_17’,‘2022-07-04 15:07:00’),
(‘user_08’,‘2022-07-04 16:35:00’),
(‘user_01’,‘2022-07-04 19:29:00’),
(‘user_14’,‘2022-07-05 08:03:00’),
(‘user_12’,‘2022-07-05 10:27:00’),
(‘user_15’,‘2022-07-05 16:33:00’),
(‘user_19’,‘2022-07-06 09:03:00’),
(‘user_20’,‘2022-07-06 15:26:00’),
(‘user_04’,‘2022-07-08 11:03:00’),
(‘user_05’,‘2022-07-08 12:54:00’),
(‘user_06’,‘2022-07-08 19:22:00’),
(‘user_13’,‘2022-07-09 10:20:00’),
(‘user_15’,‘2022-07-09 16:40:00’),
(‘user_18’,‘2022-07-10 21:34:00’);

脚本如下：

SELECT
date(a.login_time),
COUNT(DISTINCT a.user_id) 第一天客户数,
sum(DATEDIFF(b.login_time,a.login_time)=1) 1留存数,
sum(DATEDIFF(b.login_time,a.login_time)=2) 2留存数,
sum(DATEDIFF(b.login_time,a.login_time)=3) 3留存数,
sum(DATEDIFF(b.login_time,a.login_time)=4) 4留存数,
sum(DATEDIFF(b.login_time,a.login_time)=5) 5留存数,
sum(DATEDIFF(b.login_time,a.login_time)=6) 6留存数,
sum(DATEDIFF(b.login_time,a.login_time)=7) 7留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=1) 1内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=2) 2内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=3) 3内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=4) 4内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=5) 5内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=6) 6内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=7) 7内留存数,
sum(DATEDIFF(b.login_time,a.login_time)<=1)/count(DISTINCT a.user_id) 1内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=2)/count(DISTINCT a.user_id) 2内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=3)/count(DISTINCT a.user_id) 3内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=4)/count(DISTINCT a.user_id) 4内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=5)/count(DISTINCT a.user_id) 5内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=6)/count(DISTINCT a.user_id) 6内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=7)/count(DISTINCT a.user_id) 7内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=8)/count(DISTINCT a.user_id) 8内留存率,
sum(DATEDIFF(b.login_time,a.login_time)<=9)/count(DISTINCT a.user_id) 9内留存率

from login_log a
left join login_log b
on a.user_id=b.user_id and a.login_time<b.login_time
GROuP BY date(a.login_time)
order by date(a.login_time);