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

/*这种解法是很简洁的，但是在计算活跃总月数时，可能试卷和练习的月份有相同的，但是因为分开计算了，所以会出现重复计算的问题。解决方法：给试卷和练习加上标签
select u.uid
        count(distinct date_format(er.start_time,'%Y%m'))+count(distinct date_format(pr.submit_time,'%Y%m')) AS act_month_total
        ,count(distinct case when year(er.start_time)=2021 then date_format(er.start_time,'%Y%m%d') else null end) + count(distinct case when year(pr.submit_time)=2021 then date_format(pr.submit_time,'%Y%m%d') else null end) AS act_days_2021
        ,count(distinct case when year(er.start_time)=2021 then date_format(er.start_time,'%Y%m%d') else null end) AS act_days_2021_exam
        ,count(distinct case when year(pr.submit_time)=2021 then date_format(pr.submit_time,'%Y%m%d') else null end) AS act_days_2021_question
from users u
left join exam_record er on u.uid = er.uid
left join practice_record pr on u.uid = pr.uid
where level = 6 or level = 7
group by uid
order by act_month_total desc
        ,act_days_2021 desc
*/
select u.uid
        ,count(distinct act_month) AS act_month_total
        ,count(distinct case when year(act_time)=2021 then act_day else null end) AS act_days_2021
        ,count(distinct case when year(act_time)=2021 and tag = 'exam' then act_day else null end) AS act_days_2021_exam
        ,count(distinct case when year(act_time)=2021 and tag='question' then act_day else null end) AS act_days_2021_question
from user_info u
left join
(select uid
        ,start_time AS act_time
        ,date_format(start_time,'%Y%m') AS act_month
        ,date_format(start_time,'%Y%m%d') AS act_day
        ,'exam' AS tag
from exam_record
union all
select uid
        ,submit_time AS act_time
        ,date_format(submit_time,'%Y%m') AS act_month
        ,date_format(submit_time,'%Y%m%d') AS act_day
        ,'question' AS tag
from practice_record
)t1
on u.uid = t1.uid
where level = 6 or level = 7
group by uid
order by act_month_total desc
        ,act_days_2021 desc

1. 试卷和练习联合成新的表，目的是为了加上一个标签，在统计总的活跃月份和天数是才可以避免重复计算，否则如果是count(distinct date_format(er.start_time,'%Y%m'))+count(distinct date_format(pr.submit_time,'%Y%m'))，就可能出同一个人试卷和题目的活跃月份是同一天，但是被计算了两次，所以要把试卷和题目的活跃时间放到同个表的同一列，才能对并集进行去重。
2. 如果不计算总的活跃月份数和天数，只需要计算在试卷和题目上的活跃情况，那么使用第一种代码即可。