# 每个新用户 留存与否 某天登录的后一天有没有登录 如果登录就1 否则 0
select
round(sum(t1)/count(t1),3)
from
(
    select
sum(t) t1
from
(
    select
*,
case when l.date = l.c_date then 1 else 0 end as t
from
(
    select *, date_add(min(date) over(partition by user_id),interval 1 day) as c_date
from login
) as l
) as a
group by user_id
) as m

虽然做出来了，但是写了很多个子查询

比较清晰的思路如下：

  • 总用户数量
  • select count(distinct user_id) from login
  • 每个用户第一天登陆的日子（即为新用户）
  • select user_id,min(date) from login group by user_id
  • 后一天登录还登录的新用户
  • SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1DAY) FROM login GROUP BY user_id
  • MySQL解法：
  • SELECT
  • ROUND(COUNT(DISTINCT user_id)*1.0/ (SELECT COUNT(DISTINCT user_id) FROM login), 3)
  • FROM login
  • WHERE (user_id, date)
  • IN
  • (SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1DAY) FROM login GROUP BY user