select 日期,新增用户数, (select count(*) from user where date(register_time)=date(a.register_time) and exists (select 1 from log_login where uid=user.uid and login_time>=date(register_time)+interval 1 day))/新增用户数 as 次日留存, (select count(*) from user where date(register_time)=date(a.register_time) and exists (select 1 from log_login where uid=user.uid and login_time>=date(register_time)+interval 2 day))/新增用户数 as `2日留存`, from ( select date(register_time) as 日期,count(*) as 新增用户数 from user group by date(register_time) ) a
1 PRIMARY <derived4> ALL 8 4 DERIVED user ALL 66358 Using where; Using temporary; Using filesort 2 DEPENDENT SUBQUERY user ALL 66358 Using where 3 DEPENDENT SUBQUERY log_login ALL 33809 Using where
1 PRIMARY <derived4> ALL 8 4 DERIVED user ALL 66358 Using where; Using temporary; Using filesort 2 DEPENDENT SUBQUERY user ALL 66358 Using where 3 DEPENDENT SUBQUERY log_login ALL 33809 Using where 给表字段增加索引就解决了
(select count(*) from user where date(register_time)=date(a.register_time) and exists (select 1 from log_login where uid=user.uid and login_time>=date(register_time)+interval 1 day))/新增用户数 as 次日留存,
(select count(*) from user where date(register_time)=date(a.register_time) and exists (select 1 from log_login where uid=user.uid and login_time>=date(register_time)+interval 2 day))/新增用户数 as `2日留存`,
from
(
select date(register_time) as 日期,count(*) as 新增用户数
from user
group by date(register_time)
) a
4 DERIVED user ALL 66358 Using where; Using temporary; Using filesort
2 DEPENDENT SUBQUERY user ALL 66358 Using where
3 DEPENDENT SUBQUERY log_login ALL 33809 Using where
4 DERIVED user ALL 66358 Using where; Using temporary; Using filesort
2 DEPENDENT SUBQUERY user ALL 66358 Using where
3 DEPENDENT SUBQUERY log_login ALL 33809 Using where
给表字段增加索引就解决了