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

select
    date_format(submit_time,'%Y%m') submit_month,
    count(*) month_q_cnt,
    round(count(*) / max(day(last_day(submit_time))),3) avg_day_q_cnt   # 加max()是因为day()里面得到的是一组的数据
from practice_record
where year(submit_time)='2021'
group by date_format(submit_time,'%Y%m')
union
select 
    concat(date_format(submit_time,'%Y'),'汇总') as submit_month,
    count(*) month_q_cnt,
    round(count(*) / 31, 3) avg_day_q_cnt
from practice_record
where year(submit_time)='2021'
group by submit_month
order by submit_month