题目

image.png

建表语句

  1. drop table if exists practice_record;
  2. CREATE TABLE  practice_record (
  3.     id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
  4.     uid int NOT NULL COMMENT '用户ID',
  5.     question_id int NOT NULL COMMENT '题目ID',
  6.     submit_time datetime COMMENT '提交时间',
  7.     score tinyint COMMENT '得分'
  8. )CHARACTER SET utf8 COLLATE utf8_general_ci;
  10. INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
  11. (1001, 8001, '2021-08-02 11:41:01', 60),
  12. (1002, 8001, '2021-09-02 19:30:01', 50),
  13. (1002, 8001, '2021-09-02 19:20:01', 70),
  14. (1002, 8002, '2021-09-02 19:38:01', 70),
  15. (1003, 8002, '2021-08-01 19:38:01', 80);

解题思路

题意提炼：
统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt，以及该年的总体情况

问题拆解：
先分别取出每个月的天数和年份，
这里用到的是dayofmonth和date_format 然后分别统计每个月的刷题总数，和日均刷题数，
最后利用with rollup 进行统计，with rollup所在的行为’2021汇总’ 利用coalesce函数添加‘2021汇总’ 一般主流数据库系统都支持该coalesce函数 该函数主要用来进行空值处理

SQL代码

select coalesce(year_mon,'2021汇总') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id)/max(t.days_month),3) as avg_day_cnt
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;