题解 | #月总刷题数和日均刷题数#

select date_format(submit_time,'%Y%m') submit_month,
count(*) month_q_cnt,
round(count(*)/day(last_day(max(submit_time))),3) avg_day_q_cnt   --因为8月为一组，但是submit_time是每次答题都有的，8月有多次答题就会有多个答题时间，因此将多个变成一个用max()函数，保证8月分母只有一个31
from practice_record
where score is not null 
and year(submit_time) = '2021'
group by submit_month
union all
select '2021汇总' as submit_month,
count(*) month_q_cnt,
round(count(*)/31,3) avg_day_q_cnt
from practice_record
where score is not null
and year(submit_time) = '2021'
order by submit_month;

select coalesce(year_mon,'2021汇总') as submit_month,  --因为 with rollup 的存在，出现了一行null值，因此将year_mon为null时改成'2021汇总'即可
count(question_id) as month_q_cnt,
round(count(question_id)/max(t.days_month),3) as avg_day_cnt --max()函数用法与上面相同， group by 之后保证一个组只有一个30或者31作除数
from
(select question_id,
dayofmonth(last_day(submit_time)) as days_month,
date_format(submit_time,'%Y%m') as year_mon
from practice_record
where year(submit_time)=2021) as t
group by t.year_mon with rollup;  --with rollup的作用是在各分组的最后加一行数据记录所有汇总数据，组名为null值