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

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

with
    temp as (
        select
            DATE_FORMAT(submit_time, "%Y%m") as submit_month,
            count(question_id) as month_q_cnt,
            round(
                count(question_id) / day(last_day(max(submit_time))),
                3
            ) as avg_day_q_cnt
        from
            practice_record
        where
            year(submit_time) = 2021
        group by
            date_format(submit_time, "%Y%m")
        order by
            submit_month asc
    )
select
    *
from
    temp
union
select
    "2021汇总" as submit_month,
    sum(month_q_cnt) as month_q_cnt,
    round(sum(month_q_cnt) / 31, 3) as avg_day_q_cnt
from
    temp

SQL查询解析

这个SQL查询是用来分析2021年每月的问题提交情况，并在最后添加一个汇总行。

主要组成部分

1. ​CTE (Common Table Expression) 临时表 temp:

   • 从practice_record表中选择2021年的数据
   • 按月份分组(submit_month格式为YYYYMM)
   • 计算每月的总问题数(month_q_cnt)
   • 计算每日平均问题数(avg_day_q_cnt)，用月总问题数除以该月的天数

2. ​主查询:

   • 首先显示CTE中的所有月份数据
   • 然后通过UNION添加一个汇总行
   • 汇总行计算2021年全年的总问题数和日均问题数(按31天计算)

技术细节

• DATE_FORMAT(submit_time, "%Y%m") - 将日期格式化为YYYYMM格式
• last_day(max(submit_time)) - 获取该月最后一天的日期
• day(last_day(...)) - 获取该月的总天数
• round(count(question_id) / day(last_day(max(submit_time))), 3) - 计算日均问题数并保留3位小数
• "2021汇总" as submit_month - 为汇总行创建一个人工标识

查询结果

查询会返回一个表格，包含：

1. 2021年每个月的统计数据(月份、问题总数、日均问题数)
2. 最后一行是2021年全年的汇总数据