sql 7日留存问题
前言：在实际业务中，经常会遇到分析7日留存问题，以下为分析思路及常用写法，以mysql为例。
一、测试数据
-- 创建订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
order_date TIMESTAMP NOT NULL,
is_paid BOOLEAN NOT NULL, -- TRUE表示付费产品，FALSE表示免费产品
amount DECIMAL(10, 2)
);
-- 创建登录记录表
CREATE TABLE user_logins (
login_id INT PRIMARY KEY,
user_id INT NOT NULL,
login_time TIMESTAMP NOT NULL
);
-- 创建索引以提高查询性能
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
CREATE INDEX idx_logins_user_date ON user_logins(user_id, login_time);
-- 插入订单数据 (2025年4月)
INSERT INTO orders VALUES
-- 4月1日首次购买免费产品的用户
(1, 101, '2025-04-01 10:00:00', FALSE, 0.00), -- 用户101首次免费
(2, 101, '2025-04-02 11:00:00', TRUE, 9.99), -- 次日转为付费
(3, 101, '2025-04-04 12:00:00', TRUE, 19.99), -- 第3天没有，第4天有付费
(4, 102, '2025-04-01 09:30:00', FALSE, 0.00), -- 用户102首次免费
(5, 102, '2025-04-03 14:00:00', TRUE, 4.99), -- 第2天没有，第3天转为付费
(6, 103, '2025-04-01 15:45:00', FALSE, 0.00), -- 用户103首次免费
-- 没有后续付费订单
(7, 104, '2025-04-02 08:20:00', FALSE, 0.00), -- 用户104首次免费 (不在4月1日，不应计入)
(8, 104, '2025-04-03 09:30:00', TRUE, 14.99), -- 转为付费
-- 4月10日首次购买免费产品的用户
(9, 105, '2025-04-10 13:00:00', FALSE, 0.00), -- 用户105首次免费
(10, 105, '2025-04-11 10:30:00', TRUE, 7.99), -- 次日转为付费
(11, 105, '2025-04-12 11:15:00', FALSE, 0.00), -- 第3天也有活动(免费)
(12, 105, '2025-04-13 16:20:00', TRUE, 12.99),-- 第4天付费
(13, 106, '2025-04-10 09:10:00', FALSE, 0.00), -- 用户106首次免费
-- 没有后续付费订单
-- 其他干扰数据
(14, 107, '2025-04-05 11:00:00', TRUE, 5.99), -- 直接付费用户
(15, 108, '2025-03-31 14:00:00', FALSE, 0.00), -- 3月用户
(16, 108, '2025-04-02 10:00:00', TRUE, 8.99); -- 3月用户的后续付费
-- 插入登录记录
INSERT INTO user_logins VALUES
-- 用户101的登录记录
(1, 101, '2025-04-01 09:50:00'), -- 首次购买前登录
(2, 101, '2025-04-02 10:30:00'), -- 次日登录
(3, 101, '2025-04-03 11:20:00'), -- 第3天登录
(4, 101, '2025-04-05 15:00:00'), -- 第5天登录
(5, 101, '2025-04-07 16:30:00'), -- 第7天登录
-- 用户102的登录记录
(6, 102, '2025-04-01 09:00:00'),
(7, 102, '2025-04-02 08:30:00'), -- 次日登录
(8, 102, '2025-04-04 10:00:00'), -- 第4天登录
(9, 102, '2025-04-06 14:00:00'), -- 第6天登录
-- 用户103的登录记录
(10, 103, '2025-04-01 15:30:00'),
(11, 103, '2025-04-03 16:00:00'), -- 第3天登录
(12, 103, '2025-04-05 17:00:00'), -- 第5天登录
-- 用户105的登录记录
(13, 105, '2025-04-10 12:30:00'),
(14, 105, '2025-04-11 09:00:00'), -- 次日登录
(15, 105, '2025-04-12 10:00:00'), -- 第3天登录
(16, 105, '2025-04-13 15:00:00'), -- 第4天登录
(17, 105, '2025-04-14 11:00:00'), -- 第5天登录
(18, 105, '2025-04-16 14:00:00'), -- 第7天登录
-- 用户106的登录记录
(19, 106, '2025-04-10 08:45:00'),
(20, 106, '2025-04-11 10:00:00'), -- 次日登录
(21, 106, '2025-04-13 11:00:00'), -- 第4天登录
-- 其他用户登录记录
(22, 104, '2025-04-02 07:30:00'),
(23, 107, '2025-04-05 10:00:00'),
(24, 108, '2025-03-31 13:00:00');
-- 继续添加订单数据 (2025年4月)
INSERT INTO orders VALUES
-- 4月1日首次购买免费产品的用户
(17, 201, '2025-04-01 11:20:00', FALSE, 0.00),
(18, 201, '2025-04-03 14:30:00', TRUE, 12.99), -- 第3天付费
(19, 201, '2025-04-05 16:45:00', TRUE, 8.99), -- 第5天付费
(20, 202, '2025-04-01 09:15:00', FALSE, 0.00),
(21, 202, '2025-04-02 10:45:00', TRUE, 6.99), -- 次日付费
(22, 202, '2025-04-04 13:20:00', TRUE, 11.99), -- 第4天付费
(23, 203, '2025-04-01 14:30:00', FALSE, 0.00),
(24, 203, '2025-04-07 15:10:00', TRUE, 9.99), -- 第7天付费
(25, 204, '2025-04-01 16:50:00', FALSE, 0.00),
-- 无后续付费订单
(26, 205, '2025-04-01 10:10:00', FALSE, 0.00),
(27, 205, '2025-04-02 11:30:00', FALSE, 0.00), -- 次日免费
(28, 205, '2025-04-03 12:40:00', TRUE, 7.99), -- 第3天付费
-- 4月5日首次购买免费产品的用户
(29, 206, '2025-04-05 13:25:00', FALSE, 0.00),
(30, 206, '2025-04-06 14:35:00', TRUE, 5.99), -- 次日付费
(31, 206, '2025-04-08 15:45:00', TRUE, 14.99), -- 第4天付费
(32, 207, '2025-04-05 09:40:00', FALSE, 0.00),
(33, 207, '2025-04-07 10:50:00', TRUE, 10.99), -- 第3天付费
(34, 208, '2025-04-05 11:55:00', FALSE, 0.00),
-- 无后续付费订单
-- 4月10日首次购买免费产品的用户(补充)
(35, 209, '2025-04-10 12:05:00', FALSE, 0.00),
(36, 209, '2025-04-11 13:15:00', TRUE, 8.99), -- 次日付费
(37, 209, '2025-04-12 14:25:00', TRUE, 6.99), -- 第3天付费
(38, 210, '2025-04-10 15:35:00', FALSE, 0.00),
(39, 210, '2025-04-12 16:45:00', TRUE, 12.99), -- 第3天付费
-- 4月15日首次购买免费产品的用户
(40, 211, '2025-04-15 10:20:00', FALSE, 0.00),
(41, 211, '2025-04-16 11:30:00', TRUE, 9.99), -- 次日付费
(42, 211, '2025-04-18 12:40:00', TRUE, 7.99), -- 第4天付费
(43, 212, '2025-04-15 13:50:00', FALSE, 0.00),
-- 无后续付费订单
(44, 213, '2025-04-15 14:00:00', FALSE, 0.00),
(45, 213, '2025-04-17 15:10:00', TRUE, 11.99), -- 第3天付费
-- 4月20日首次购买免费产品的用户
(46, 214, '2025-04-20 09:30:00', FALSE, 0.00),
(47, 214, '2025-04-21 10:40:00', TRUE, 6.99), -- 次日付费
(48, 215, '2025-04-20 11:50:00', FALSE, 0.00),
(49, 215, '2025-04-22 12:00:00', TRUE, 8.99), -- 第3天付费
-- 其他干扰数据
(50, 216, '2025-04-03 13:10:00', TRUE, 4.99), -- 直接付费用户
(51, 217, '2025-04-08 14:20:00', FALSE, 0.00),
(52, 217, '2025-04-09 15:30:00', TRUE, 10.99); -- 次日付费
二、实现方法
法一：写法更简洁
思路：先计算出4月订购免费版用户的首次订购时间，根据首次订购时间计算出该时间后的7天时间，然后关联订单表和登录表（订购时间与登录时间在这7天范围内），分别拿到在这个时间段的所有订单和登录信息，根据首次订购时间与订单的订购时间和登陆时间以及是否付费计算7日留存。
select a.date_desc
,user_free_cnt
,day1_login_cnt
,day1_free2pay
,day2_login_cnt
,day2_free2pay
,day3_login_cnt
,day3_free2pay
,day4_login_cnt
,day4_free2pay
,day5_login_cnt
,day5_free2pay
,day6_login_cnt
,day6_free2pay
,day7_login_cnt
,day7_free2pay
from (
-- 查出首次订购免费版的用户在后7天内的所有订单进行处理
select b.frt_order_date date_desc
,count(distinct a.user_id) user_free_cnt -- 当日订购免费版用户
,count(distinct if(is_paid=1 and date(order_date)=frt_order_date,a.user_id,null)) day1_free2pay -- 当日免转付用户数
,count(distinct if(is_paid=1 and date(order_date)=date_add(frt_order_date,interval 1 day),a.user_id,null)) day2_free2pay -- day2免转付用户数
,count(distinct if(is_paid=1 and date(order_date)=date_add(frt_order_date,interval 2 day),a.user_id,null)) day3_free2pay -- day3免转付用户数
,count(distinct if(is_paid=1 and date(order_date)=date_add(frt_order_date,interval 3 day),a.user_id,null)) day4_free2pay -- day4免转付用户数
,count(distinct if(is_paid=1 and date(order_date)=date_add(frt_order_date,interval 4 day),a.user_id,null)) day5_free2pay -- day5免转付用户数
,count(distinct if(is_paid=1 and date(order_date)=date_add(frt_order_date,interval 5 day),a.user_id,null)) day6_free2pay -- day6免转付用户数
,count(distinct if(is_paid=1 and date(order_date)=date_add(frt_order_date,interval 6 day),a.user_id,null)) day7_free2pay -- day7免转付用户数
from orders a
inner join (
-- 首次订购免费版用户
select user_id
,date(min(order_date)) frt_order_date -- 首次订购日期
,date(date_add(min(order_date),interval 7 day)) order_date_7 -- 首次订购后7天日期
from orders
where is_paid = 0
and date_format(order_date,'%Y%m') = '202504'
group by user_id
) b on a.user_id = b.user_id and date(a.order_date) between b.frt_order_date and b.order_date_7
group by b.frt_order_date
) a
left join (
-- 7日登录数据
select b.frt_order_date date_desc
,count(distinct if(date(login_time)=frt_order_date,a.user_id,null)) day1_login_cnt -- 当日登录用户数
,count(distinct if(date(login_time)=date_add(frt_order_date,interval 1 day),a.user_id,null)) day2_login_cnt -- day2登录用户数
,count(distinct if(date(login_time)=date_add(frt_order_date,interval 2 day),a.user_id,null)) day3_login_cnt -- day3登录用户数
,count(distinct if(date(login_time)=date_add(frt_order_date,interval 3 day),a.user_id,null)) day4_login_cnt -- day4登录用户数
,count(distinct if(date(login_time)=date_add(frt_order_date,interval 4 day),a.user_id,null)) day5_login_cnt -- day5登录用户数
,count(distinct if(date(login_time)=date_add(frt_order_date,interval 5 day),a.user_id,null)) day6_login_cnt -- day6登录用户数
,count(distinct if(date(login_time)=date_add(frt_order_date,interval 6 day),a.user_id,null)) day7_login_cnt -- day7登录用户数
from user_logins a
inner join (
-- 首次订购免费版用户
select user_id
,date(min(order_date)) frt_order_date -- 首次订购日期
,date(date_add(min(order_date),interval 7 day)) order_date_7 -- 首次订购后7天日期
from orders
where is_paid = 0
and date_format(order_date,'%Y%m') = '202504'
group by user_id
) b on a.user_id = b.user_id and date(a.login_time) between b.frt_order_date and b.order_date_7
group by b.frt_order_date
) b on a.date_desc = b.date_desc
结果：
法2：易于理解，但是代码稍有冗余
思路：先计算出4月订购免费版用户的首次订购时间，根据首次订购时间计算出该时间后的7天时间，然后关联订单表和登录表（订购时间与登录时间在这7天范围内），分别拿到在这个时间段的所有订单和登录信息，使用case when 根据首次订购时间与订单的订购时间和登陆时间差标记出属于7天中的哪一天，然后再根据是否付费计算7日留存。
与法三类似。
select a.date_desc
,user_free_cnt
,day1_login_cnt
,day1_free2pay
,day2_login_cnt
,day2_free2pay
,day3_login_cnt
,day3_free2pay
,day4_login_cnt
,day4_free2pay
,day5_login_cnt
,day5_free2pay
,day6_login_cnt
,day6_free2pay
,day7_login_cnt
,day7_free2pay
from (
-- 查出首次订购免费版的用户在后7天内的所有订单进行处理
select frt_order_date date_desc
,count(distinct user_id) user_free_cnt
,count(if(day_1=1 and is_paid=1,user_id,null)) day1_free2pay
,count(if(day_2=1 and is_paid=1,user_id,null)) day2_free2pay
,count(if(day_3=1 and is_paid=1,user_id,null)) day3_free2pay
,count(if(day_4=1 and is_paid=1,user_id,null)) day4_free2pay
,count(if(day_5=1 and is_paid=1,user_id,null)) day5_free2pay
,count(if(day_6=1 and is_paid=1,user_id,null)) day6_free2pay
,count(if(day_7=1 and is_paid=1,user_id,null)) day7_free2pay
from (
select a.*
,b.frt_order_date
,case when datediff(date(a.order_date),b.frt_order_date)=0 then 1 end day_1
,case when datediff(date(a.order_date),b.frt_order_date)=1 then 1 end day_2
,case when datediff(date(a.order_date),b.frt_order_date)=2 then 1 end day_3
,case when datediff(date(a.order_date),b.frt_order_date)=3 then 1 end day_4
,case when datediff(date(a.order_date),b.frt_order_date)=4 then 1 end day_5
,case when datediff(date(a.order_date),b.frt_order_date)=5 then 1 end day_6
,case when datediff(date(a.order_date),b.frt_order_date)=6 then 1 end day_7
from orders a
inner join (
-- 首次订购免费版用户
select user_id
,date(min(order_date)) frt_order_date -- 首次订购日期
,date(date_add(min(order_date),interval 7 day)) order_date_7 -- 首次订购后7天日期
from orders
where is_paid = 0
and date_format(order_date,'%Y%m') = '202504'
group by user_id
) b on a.user_id = b.user_id and date(a.order_date) between b.frt_order_date and b.order_date_7
) a group by frt_order_date
) a
-- 7日登录数据
left join (
select frt_order_date date_desc
,count(if(day_1=1,user_id,null)) day1_login_cnt
,count(if(day_2=1,user_id,null)) day2_login_cnt
,count(if(day_3=1,user_id,null)) day3_login_cnt
,count(if(day_4=1,user_id,null)) day4_login_cnt
,count(if(day_5=1,user_id,null)) day5_login_cnt
,count(if(day_6=1,user_id,null)) day6_login_cnt
,count(if(day_7=1,user_id,null)) day7_login_cnt
from (
select a.*
,b.frt_order_date
,case when datediff(date(a.login_time),b.frt_order_date)=0 then 1 end day_1
,case when datediff(date(a.login_time),b.frt_order_date)=1 then 1 end day_2
,case when datediff(date(a.login_time),b.frt_order_date)=2 then 1 end day_3
,case when datediff(date(a.login_time),b.frt_order_date)=3 then 1 end day_4
,case when datediff(date(a.login_time),b.frt_order_date)=4 then 1 end day_5
,case when datediff(date(a.login_time),b.frt_order_date)=5 then 1 end day_6
,case when datediff(date(a.login_time),b.frt_order_date)=6 then 1 end day_7
from user_logins a
inner join (
-- 首次订购免费版用户
select user_id
,date(min(order_date)) frt_order_date -- 首次订购日期
,date(date_add(min(order_date),interval 7 day)) order_date_7 -- 首次订购后7天日期
from orders
where is_paid = 0
and date_format(order_date,'%Y%m') = '202504'
group by user_id
) b on a.user_id = b.user_id and date(a.login_time) between b.frt_order_date and b.order_date_7
) a group by frt_order_date
) b on a.date_desc = b.date_desc
结果：
法三：更灵活
思路：先计算出4月订购免费版用户的首次订购时间，然后关联订单表和登录表，分别拿到在这个时间及以后的所有订单和登录信息，计算首次订购时间与之后的订购和登录时间差值，差值为n代表该用户在n天后再次订购或者登录，根据此逻辑来计算7日留存。
此方法相对于法一法二的好处在于时间更灵活，不只局限7天范围，甚至可以计算n日留存。在计算任务中底表写入的时候优势会大大显现。
select x.date_desc
,user_free_cnt
,day1_login_cnt
,day1_free2pay
,day2_login_cnt
,day2_free2pay
,day3_login_cnt
,day3_free2pay
,day4_login_cnt
,day4_free2pay
,day5_login_cnt
,day5_free2pay
,day6_login_cnt
,day6_free2pay
,day7_login_cnt
,day7_free2pay
from (
-- 7日免转付
select frt_order_date date_desc
,count(distinct user_id) user_free_cnt -- 当日订购免费版用户
,count(distinct if(date_diff=0 and pre_order_is_paid=1,user_id,null)) day1_free2pay -- day1免转付用户数
,count(distinct if(date_diff=1 and pre_order_is_paid=1,user_id,null)) day2_free2pay -- day2免转付用户数
,count(distinct if(date_diff=2 and pre_order_is_paid=1,user_id,null)) day3_free2pay -- day3免转付用户数
,count(distinct if(date_diff=3 and pre_order_is_paid=1,user_id,null)) day4_free2pay -- day4免转付用户数
,count(distinct if(date_diff=4 and pre_order_is_paid=1,user_id,null)) day5_free2pay -- day5免转付用户数
,count(distinct if(date_diff=5 and pre_order_is_paid=1,user_id,null)) day6_free2pay -- day6免转付用户数
,count(distinct if(date_diff=6 and pre_order_is_paid=1,user_id,null)) day7_free2pay -- day7免转付用户数
from (
-- 获取4月订购免费版的用户 在首次订购之后的所有订单(包含首次订购订单),并计算日期差
select a.*
,date(b.order_date) pre_order_date -- 之后订单订购日期
,is_paid pre_order_is_paid -- 之后订单是否付费
,datediff(date(b.order_date),a.frt_order_date) date_diff -- 首次订购与之后订购日期差值
from (
-- 4月首次订购免费版用户
select user_id
,date(min(order_date)) frt_order_date -- 首次订购日期
-- ,date(date_add(min(order_date),interval 7 day)) order_date_7 -- 首次订购后7天日期
from orders
where is_paid = 0
and date_format(order_date,'%Y%m') = '202504'
group by user_id
) a
left join orders b on a.user_id = b.user_id and a.frt_order_date<=date(b.order_date)
) a
group by frt_order_date
) x
left join (
-- 7日登录
select frt_order_date date_desc
,count(distinct if(date_diff=0,user_id,null)) day1_login_cnt -- day1登录用户数
,count(distinct if(date_diff=1,user_id,null)) day2_login_cnt -- day2登录用户数
,count(distinct if(date_diff=2,user_id,null)) day3_login_cnt -- day3登录用户数
,count(distinct if(date_diff=3,user_id,null)) day4_login_cnt -- day4登录用户数
,count(distinct if(date_diff=4,user_id,null)) day5_login_cnt -- day5登录用户数
,count(distinct if(date_diff=5,user_id,null)) day6_login_cnt -- day6登录用户数
,count(distinct if(date_diff=6,user_id,null)) day7_login_cnt -- day7登录用户数
from (
-- 获取4月订购免费版的用户 在首次订购之后的所有登录信息(包含首次订购登录),并计算日期差
select a.*
,date(b.login_time) pre_login_date -- 之后订单订购日期
,datediff(date(b.login_time),a.frt_order_date) date_diff -- 首次订购与之后登录日期差值
from (
-- 4月首次订购免费版用户
select user_id
,date(min(order_date)) frt_order_date -- 首次订购日期
-- ,date(date_add(min(order_date),interval 7 day)) order_date_7 -- 首次订购后7天日期
from orders
where is_paid = 0
and date_format(order_date,'%Y%m') = '202504'
group by user_id
) a
left join user_logins b on a.user_id = b.user_id and a.frt_order_date<=date(b.login_time)
) a
group by frt_order_date
) y on x.date_desc = y.date_desc
结果：
浙公网安备 33010602011771号