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

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 date_format(submit_time, '%Y') = '2021'
group by
    date_format (submit_time, '%Y%m')
union
select
    concat (submit_month, '汇总') as submit_month,
    month_q_cnt,
    avg_day_q_cnt
from
    (
        select
            date_format (submit_time, '%Y') 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 date_format(submit_time, '%Y') = '2021'
        group by
            date_format (submit_time, '%Y')
    ) as temp
order by submit_month

因为涉及到聚合函数，所以在实现汇总的时候，使用了子查询先把年度汇总的数据整合算好，然后再使用concat拼接一下