?SQL127 月總刷題數和日均刷題數

withtemp as (selectDATE_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_cntfrompractice_recordwhereyear(submit_time) = 2021group bydate_format(submit_time, "%Y%m")order bysubmit_month asc)
select*
fromtemp
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
fromtemp

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年全年的匯總數據