我的稀碎答案（也就是新手易错点）：

①初级

select date_format(submit_time,'%Y%m') as submit_month,
count(question_id) as month_q_cnt,
count(question_id)/ daynumber
from practice_record
where year(submit_time)=2021
group by submit_month
order by submit_month

②进阶版

```select coalesce(date_format(submit_time,'%Y%m'),'2021汇总') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id)/ max(day(last_day(submit_time))),3) 
as avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by submit_month
with rollup
order by submit_month
  • 此处问题在于，with rollup的是包含2021汇总在内的， group by面对的对象不是n个，而是n+1个； 所以需要新建一个表，先将形如'202108'的定义为一个量， 再用coalesce()定义另一个量。

待解决的问题：

  • 如何在各行格式不一致的情况下合并为一张表格 ①Union ②COALESCE ( expression,value1,value2……,valuen) 将会返回包括expression在内的所有参数中的第一个非空表达式

  • 如何把每月的日期数分别对应上去（30/31） ①day(last_day())——指定日期所在月份的总天数 ②dayofmonth()——指定日期是该月的第几天，等同于day()

参考答案：

select date_format(submit_time,'%Y%m') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id)/ max(day(last_day(submit_time))),3) as avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by submit_month
union all
select '2021汇总' as submit_month,
count(*) as month_q_cnt,
round(count(*)/31,3) as avg_day_q_cnt
from practice_record
where year(submit_time)=2021
order by submit_month
  • 由于第二、三个量都是以submit_time的月份为分组标准进行计算的，所以标红处必须有avg(),min()或max()这样的聚合函数，不然会有如下报错：

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'practice_record.submit_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

  • （但不能是sum，如202109对应三条记录，那么它们的所在月天数都是30，用min()或max得到的都是30，但用sum()得到的是90）

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
  • 两份代码的区别在于，②用了coalesce()函数直接对形如202108这样的行与2021汇总行合并，而①则是应用union all命令把两表合并为一表

涉及函数解析：

  • coalesce()函数用于取括号内的首个非空值,当取完了所有submit_time的月份后，最后一行所得结果就是'2021汇总'了；

  • group by后面的with rollup用以统计所分小组的总体情况，例如 ①对于sum，所得结果中，前面是每组总和，最后一条数据是所有数据总和； ②对于avg，所得结果中，前面是各组分别的平均值，最后一条是所有数据平均值； ③对于min或max，所得结果中，前面是各组最值，最后一条是所有数据中的最值。

  • 综上，函数组合应为【coalesce()+with rollup】/【union all】. 具体举例参见 http://t.csdn.cn/lgn41