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

明确题意：

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

问题分解：

• 筛选2021年的刷题记录：where year(submit_time)='2021'
• 获取当月的天数：DAYOFMONTH(LAST_DAY(submit_time))
• 获取当月的年月：DATE_FORMAT(submit_time, "%Y%m") as y_m
• 统计每月的总刷题数和日均刷题数：
  • 先按年月分组：group by y_m
  • 统计月总刷题数：count(1)
  • 月均刷题数=月总刷题数÷该月天数：count(1) / max(days_of_month)
  • 汇总情况：with rollup

细节问题：

• 表头重命名：as
• 汇总情况的month列为null，需判断后重置：ifnull(y_m, '2021汇总')
• 保留3位小数：round(..., 3)

完整代码：

select ifnull(y_m, '2021汇总') as submit_month,
    count(1) as month_q_cnt,
    round(count(1) / max(days_of_month), 3) as avg_day_q_cnt
from (
    select question_id,
        DAYOFMONTH(LAST_DAY(submit_time)) as days_of_month,
        DATE_FORMAT(submit_time, "%Y%m") as y_m
    from practice_record
    where year(submit_time)='2021'
) as t_month_stat
group by y_m
with rollup