提供一种使用左连接特性的解法,思路十分十分容易懂
首先我们想到是查出所有第一次登录的人和他第一次登录的日期
( select user_id,min(date) as date from login group by user_id )t1
然后我们又应该想到的是,我们要看这些人中哪些次日登录了,这个通过连接直接解决,这里用的是datediff
left join login as t2 on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1
我们思考一下左连接的特性,左有的全都有,也就是说次日没登录的会在连接中以空值被连接进来,那么我们count t2中的userid
然后直接除以t1中的user_id就行了。(因为左连接会直接把t1有t2没有的t2填成空值的,空值不带入计算)
最后我们需要考虑两种特殊情况:
1:不一定每天都有新登录用户,例如想看10.1 10.2,但只要10.1有新登录用户,则记录只有10.2,这个比较容易直接查到原表的日期有哪些
然后我们右连接回去(也可以用当前日期不存在的union)
2:这些没有新登录用户的日期,会导致分母为0,即null值的计算,这个用ifnull。
综上整理一下,没有任何难度的代码就出来了,不涉及任何花里胡哨的代码和子查询:
select t0.date, ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0) from ( select date from login group by date ) t0 left join ( select user_id,min(date) as date from login group by user_id )t1 on t0.date=t1.date left join login as t2 on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1 group by t0.date