题解 | #每个6/7级用户活跃情况#

这道题前前后后用了我大半天时间，特此记录，用的都是很基础的方法，应该可以给大家一个较为清晰的思路，这里贴上每个步骤的拆解与最终代码。

#1.算出每个用户的活跃月份数：等于在试卷区活跃月份数+在答题区活跃月份数，注意：二者重复仅算一次
select uid,count(act_month_total) from(
select ui.uid uid,if(date_format(er.start_time,'%y%m') is not null ,date_format(er.start_time,'%y%m'),null) act_month_total
from exam_record er right  join user_info ui using (uid)
group by ui.uid,act_month_total
union
select ui.uid uid,if(date_format(pr.submit_time,'%y%m') is not null ,date_format(pr.submit_time,'%y%m'),null) result
from practice_record pr right  join user_info ui using (uid)
group by ui.uid,result) A1
group by A1.uid;

/*
每个用户的活跃月份数是由试卷区活跃月份数+在答题区活跃月份数组成的，但是会出现某个月在这两个区都有活跃的情况，此时应该只记录1次，可以使用union来去重
*/

#2.算出每位用户的总活跃天数、答卷活跃天数、答题活跃天数，注意：答卷活跃天数+答题活跃天数未必等于总活跃天数，二者可能有重复
#每位用户的总活跃天数
select uid,count(d1) act_month_total from(
select uid,if(date_format(er.start_time,'%y%m%d') is not null and year(er.start_time) = 2021,date_format(er.start_time,'%y%m%d'),null) d1
from user_info ui left join exam_record er using (uid)
where level in (6,7)
group by uid,d1
union
select uid,if(date_format(pr.submit_time,'%y%m%d') is not null and year(pr.submit_time) = 2021,date_format(pr.submit_time,'%y%m%d'),null) d2
from user_info ui left join practice_record pr using (uid)
where level in (6,7)
group by uid,d2) t1
group by uid;
/*
这里我是将第二步又拆分为2个小步，因为在统计总活跃天数的时候同样会出现如1中的重复问题，而在统计答卷活跃天数和答题活跃天数不需要注意重复问题，换句话说，总活跃天数不一定等于答卷活跃天数 + 答题活跃天数，应该是小于等于，这里题目给的示例数据都是等于，所以存在较大的迷惑性，可以看出在这里我在处理去重时依然使用的union，同时在这一步我实现了6、7等级的条件筛选，后续在进行多表联合时只需使用外连接即可。
*/
#答卷活跃天数、答题活跃天数
select uid,count(distinct if(year(er.start_time) = 2021,date_format(er.start_time,'%y%m%d'),null)) act_days_2021_exam,count(distinct if(year(pr.submit_time) = 2021,date_format(pr.submit_time,'%y%m%d'),null)) act_days_2021_question
from user_info ui left join exam_record er using (uid)
left join practice_record pr using (uid)
group by uid;
/*
这一步的重点是select语句的编写，count(distinct if(year(er.start_time) = 2021,date_format(er.start_time,'%y%m%d'),null)) act_days_2021_exam，这一句意思是筛选出是2021年的答卷记录时间，并且如果该时间有多个相同，需用distinct关键字去重只算一个，后面那个也是一样
*/
#最终组合
select uid,act_month_total,act_days_2021,act_days_2021_exam,act_days_2021_question
from (
select uid,count(act_month_total) act_month_total from(
select ui.uid uid,if(date_format(er.start_time,'%y%m') is not null ,date_format(er.start_time,'%y%m'),null) act_month_total
from exam_record er right  join user_info ui using (uid)
group by ui.uid,act_month_total
union
select ui.uid uid,if(date_format(pr.submit_time,'%y%m') is not null ,date_format(pr.submit_time,'%y%m'),null) result
from practice_record pr right  join user_info ui using (uid)
group by ui.uid,result) A1
group by A1.uid
     ) T1 right join (
select uid,count(d1) act_days_2021 from(
select uid,if(date_format(er.start_time,'%y%m%d') is not null and year(er.start_time) = 2021,date_format(er.start_time,'%y%m%d'),null) d1
from user_info ui left join exam_record er using (uid)
where level in (6,7)
group by uid,d1
union
select uid,if(date_format(pr.submit_time,'%y%m%d') is not null and year(pr.submit_time) = 2021,date_format(pr.submit_time,'%y%m%d'),null) d2
from user_info ui left join practice_record pr using (uid)
where level in (6,7)
group by uid,d2) t1
group by uid
) T2 using (uid) left join (
select uid,count(distinct if(year(er.start_time) = 2021,date_format(er.start_time,'%y%m%d'),null)) act_days_2021_exam,count(distinct if(year(pr.submit_time) = 2021,date_format(pr.submit_time,'%y%m%d'),null)) act_days_2021_question
from user_info ui left join exam_record er using (uid)
left join practice_record pr using (uid)
group by uid
) T3 using (uid)
order by act_month_total desc ,act_days_2021 desc
/*
最终组合只需要注意1、2表连接使用右外连接即可，因为要实现用户等级的筛选
*/

#虽说没看题解，还是本题对我来说还是有点难了，耗费的时间很长，但是确实学到了不少东西，下次继续加油！！！奥里给