SQL18 月总刷题数和日均刷题数

题目主要信息：

• 统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况
• 最后输出是月份升序，保留三位小数

问题拆分：

• 找出每个月的刷题总数和日均刷题数：
  • 根据提交时间submit_time中年份为2021年筛选完成的题数。知识点：select...from...where...
  • 匹配月份，根据月份分组。知识点：date_format()、group by 通过date_format()函数匹配'%Y%m'年份和月份，按照不同的月份分组；
  • 分组统计每个月的得分数，按照拥有得分数量判定完成题目的数量。知识点：count()
  • 分组统计每个月的日均刷题数，每个月总体数用上述方法得到，需要除当月天数，先用last_day()求出当月最后一天日期，再用day()取是当月第几天。知识点：count()、last_day()、day()
• 汇总一年的情况：
  • 将月份信息的select结果和年汇总的select结果组合，列名以第一个为准。 知识点：union all
  • 筛选属于2021年的信息，以得分数统计刷题总数，将字符'2021汇总'作为submit_month、总数作为month_q_cnt、总数/31作为 avg_day_q_cnt。知识点：select ...as... from...where...、date_format()
• 上述计算都是保留三位小数。知识点：round(x,3)
• 按照月份升序排序。知识点：order by

代码：

select date_format(submit_time, '%Y%m') as submit_month,
       count(score) as month_q_cnt,
       round(count(score) / day(last_day(submit_time)), 3) as avg_day_q_cnt
from practice_record
where date_format(submit_time, '%Y') = 2021
group by submit_month

union all
(select '2021汇总' as submit_month,
        count(score) as month_q_cnt,
        round(count(score) / 31, 3) as avg_day_q_cnt
from practice_record
where date_format(submit_time, '%Y') = 2021)
order by submit_month