【统计每个日期新用户的次日留存率】可以分解为:次日留存率 = 某日新用户次日仍活跃 ÷ 某日新用户,使用case when可得到如下代码
select date, round(sum( case when (user_id,date) in (select user_id,min(date)from login group by user_id) and (user_id,date_add(date,interval 1 day)) in (select user_id,date from login) then 1 else 0 end )/sum( case when (user_id,date) in (select user_id,min(date)from login group by user_id) then 1 else 0 end ),3 )as p from login group by date order by date但运行后,实际输出和期望输出不符。观察发现是一些分母为0,(即某日没有新用户)的次日留存率计算要直接记为0
使用ifnull来判断是否空值,如果为空值置为0,最终代码如下:
select date, ifnull( round(sum( case when (user_id,date) in (select user_id,min(date)from login group by user_id) and (user_id,date_add(date,interval 1 day)) in (select user_id,date from login) then 1 else 0 end )/sum( case when (user_id,date) in (select user_id,min(date)from login group by user_id) then 1 else 0 end ),3),0 )as p from login group by date order by date