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

select date_format(submit_time,'%Y%m') submit_month,count(submit_time),round(count(submit_time)/avg(datediff(last_day(date_format((submit_time),'%Y%m%d')),date_sub(date_format(submit_time,'%Y-%m-%d'),interval day(submit_time)-1 day))+1),3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by submit_month
union
select concat(date_format(submit_time,'%Y'),'汇总') submit_month,count(submit_time),round(count(submit_time)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by submit_month
order by submit_month