题解 | #两种做法与解析#

# # window出后一天的日期，之后if每行条件计数是否为日期差1
select visit_date,
count(distinct id) as dau,
sum(if(datediff(lead_date,visit_date)=1,1,0))/count(distinct b.id) as next_day_per
from 
(
select a.*,
lead(visit_date,1,null) over(partition by id order by visit_date) lead_date
from user_visit_log as a 
) as b
group by visit_date
order by visit_date;

# 通过表自交，直接获取后一天也活跃的日期，最后从交集表中计算两个日期相关的就可以
# select 
# visit_date, count(distinct id) as dau, count(distinct id,next_date) / count(distinct id) as next_day_per
# from
# (
#     select 
#     t1.* ,t2.visit_date as next_date
#     from 
#     user_visit_log as t1 left join user_visit_log as t2 
#     on 
#     t1.id = t2.id 
#     and
#     adddate(t1.visit_date,interval 1 day) = t2.visit_date

# ) as t3
# group by visit_date 
# order by visit_date asc;

此题我写了两种解法，其大致思路是一样的

思路：

1】我们给原始表新增一列，这一列要能够帮助我们有效识别，每一行的此次访问行为，其是否有后续访问

1.1】记住，原始表的数据我们要保持其不变，不缺，这时候易想到window函数的好处，它是不会改变原始数据的，只增列

1.2】翻译一下，何为存留，存留 = 同一用户的行为 + 下一天有访问记录

1.2.1】下一天有访问记录 = 按照日期排序时current-day首先存在下一行（lead能取到） + 下一行的日期必须为（current-day + 1 day）

2】我们如果在原表上增加了这一列辅助我们获知（每次操作后续的行为信息）时，那么就可以按照（用户ID）来groupby后计算

方法1: 自交表

1】方法的特点为一步到位，将【1.2.1】的两个限制在一个环节内达成，化复杂于过渡表的处理

2.1】想知道下一天是否发生，将表做self-join形成中间表，中间表 = 原数据 + 新增信息列

2.2】自交的时候，如何保持原数据？left-join，且限制能满足行数不增多（限制下面紧接着）

2.3】限制是什么，self左表t1，与self右表t2，我们去寻找对于每一个t1的date，是否存在t2的date刚好为 t1.date + 1 day

2.4】上述能满足了自交表t3 = t1全部 + t2.date， （t2.date要么存在 = 该行t1.date+1 ; 要么其为空值null ）

3】从t3中可以去通过groupby用户id后进行计算了，注意，只需要count(t3中来自t2.date）与 id的组合distinct值就可以得到次日 存留数

4】此题是不太严谨的，但是我们要有严谨的思维，甚至直接count(id）算用户数都能通过，实际上一定要考虑distinct

方法2：先window出过渡表

1】方法的特点为逻辑明了，而且不容易出错

2】还是同样的思路，略有不同，还是先去获取信息来帮助我们满足【1.2.1】的限制

2.1】如何window去求解呢？对于任意一行，我们求该用户（partition by id）的日期排序后（date）的区间中，下一行的date值

2.2】此处我们只完成了【1.2.1】的部分限制，即“下一行”确实有了，如代码里所写没有再去满足 date差1的限制

2.3】引申，此处大部分人如果对window函数不是非常了解，到此可以算出过渡表，把date差1的限制留到外部groupby时求

2.3.1】如果够熟练，那么其实【1.2.1】的限制在此处应该完全满足，通过 range between current row and 1 day following

3】如上述代码里，当时此方法也是借鉴别人评论，是在最后groupby时用if条件满足diff-date-1这个限制后转换01后计算sum

4】但是这其实也是不严谨的我代码里没有去改，因为还是需要组合id与date来计算distinct会更好