/*第一次的方法，有缺陷
select round(c_user_id/t_user_id,3) as q
from (select count(distinct user_id) as t_user_id
from login) as t,
(select  count(mmt.user_id) as c_user_id
from login as l
left outer join (select user_id, min(date)+1 as mt
from login
group by user_id) as mmt
on l.user_id = mmt.user_id
where date = mt) as c;
*/

-- 查找每天的新用户
select user_id, min(date) as f_d
from login
group by user_id;

-- 查找每天用户第二日留存
-- 注意如何进行日期时间之间的加减date_add(@dy, interval 1 day/week/month/year)
select user_id, date
from login
where (user_id, date) in
(select user_id, date_add(min(date), interval 1 day) as s_d from login group by user_id);

-- 最后求留存率
select round(count(distinct b.user_id)/count(distinct a.user_id),3) as p
from
(select user_id, date_add(min(date), interval 1 day) as s_d from login group by user_id) as a
left outer join
(select user_id, date
from login
where (user_id, date) in
(select user_id, date_add(min(date), interval 1 day) as s_d from login group by user_id)) as b
on a.user_id = b.user_id;

第一次的做法有缺陷，这一次再重做一次。

其中运用到where子句的双列过滤规则、round()函数的使用、date格式的加减。

  1. where (a_column, b_column) in (select c, d from table)两列同时满足过滤条件时的过滤规则，可用于多列；

  2. round(number, n)保留小数点后 n 位；

  3. date_add(@dt, interval n day) 日期增加 n 天，也可以使用周week，月month，季度quater，年year。