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

1、这道题卡住我的点是，如何在submit_month加入“2021汇总”，这里可以用ifull函数或者coalesce函数，为什么一直不能运行，试验了总结下：这两个函数后面接的表达式应该是直接可以用的表达式，也就是数据源已生成的列，这就需要我们在数据源就计算好date_format(submit_time,'%Y%m')作为一列。
2、第二难的点是，MAX(DAY(last_day(submit_time)这里
（1）用max，其实就是为了去除重复值，用min，avg函数均可以。
（2）last_day(submit_time) 是为了计算对应日期最后一天的日期
（3）day是为了计算最后一天的日期对应的天数（所在月）
DAY(）可以用dayofmonth 函数代替
3、WITH ROLLUP就是为了计算得到group by的汇总信息，也就是“2021汇总”列的求和数


方法一（为了试验ifull函数或者coalesce函数后面是不是得接数据源的列而建）
select 
ifnull(D,"2021汇总") submit_month,
count(submit_time) month_q_cnt,
ROUND(COUNT(submit_time)/MAX(DAY(last_day(submit_time))),3) AS avg_day_q_cnt
from practice_record p1
join 
(select id,
date_format(submit_time,'%Y%m') D from practice_record where year(submit_time)=2021) p2
on p1.id=p2.id
group by D WITH ROLLUP

方法二：嵌套函数（最佳）
select ifnull(a.ymd, '2021汇总'), count(1), round(count(1)/max(da),3)
from (
    select DATE_FORMAT(submit_time, '%Y%m') ymd, 
    DAYOFMONTH(last_day(submit_time)) da
    from practice_record 
    where year(submit_time) = '2021' ) a
group by a.ymd
with rollup

方法三：union函数
SELECT
    DATE_FORMAT(submit_time, '%Y%m') submit_month,
    count(1),
    ROUND(count(1)/DAY(LAST_DAY(DATE_FORMAT(max( submit_time),'%Y%m%d' ))),3)
FROM
    practice_record
WHERE
    YEAR (submit_time) = '2021'
GROUP BY
    DATE_FORMAT(submit_time, '%Y%m')
UNION ALL
SELECT
    '2021汇总' submit_month,
    count(1),
    ROUND(count(1)/31, 3)
FROM
    practice_record
WHERE
    YEAR (submit_time) = '2021'
ORDER BY
    submit_month