将题目一步步拆解,题目要求我们:输出自从有用户作答记录以来,每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。
1、首先我们求出每月的月活用户数;
select date_format(start_time,'%Y%m') month,count(distinct uid) mau from exam_record group by month order by month2、求出每月的新增用户数,我们可以先把每个用户最早的登录月份找出来,再根据月份进行聚合,那么就求出了每月的新增用户数
select mdt,count(uid) month_add_uv from (select uid,min(date_format(start_time,'%Y%m')) mdt from exam_record group by uid ) t2 group by mdt order by mdt3、将1和2进行left join 连接,用case when函数将每月新增用户数为null改为0
select month,mau, case when month_add_uv is null then 0 else month_add_uv end as month_add_uv from (select date_format(start_time,'%Y%m') month,count(distinct uid) mau from exam_record group by month order by month ) t1 left join (select mdt,count(uid) month_add_uv from (select uid,min(date_format(start_time,'%Y%m')) mdt from exam_record group by uid ) t2 group by mdt order by mdt ) t3 on t1.month=t3.mdt4、截止当月单月最大的新增用户数,也就是说求截止到当月前,前面几个月里新增用户最多的人数,完整代码如下
select month,mau,month_add_uv, max(month_add_uv)over(order by month) max_month_add_uv, sum(month_add_uv)over(order by month) as cum_sum_uv from (select month,mau, case when month_add_uv is null then 0 else month_add_uv end as month_add_uv from (select date_format(start_time,'%Y%m') month,count(distinct uid) mau from exam_record group by month order by month ) t1 left join (select mdt,count(uid) month_add_uv from (select uid,min(date_format(start_time,'%Y%m')) mdt from exam_record group by uid ) t2 group by mdt order by mdt ) t3 on t1.month=t3.mdt ) t4