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

#方法一：使用的是union all 的方法
select  date_format(submit_time,"%Y%m") as submit_month ,
any_value(count(uid)) as month_q_cnt,
any_value(round(count(uid)/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(uid) as month_q_cnt,
round(count(uid)/31,3) as avg_day_q_cnt
from practice_record
where year(submit_time)='2021'
order by submit_month;#对月份进行升序排序
#day(last_day())返回当月的天数
#方法二：使用 with rollup;
#利用group by with rollup 替换union all进行简化
select coalesce(months,'2021汇总') as submit_month,
       count(question_id) as month_q_cnt,
       round(count(question_id)/max(days),3) as avg_day_cnt
from(select question_id,
            date_format(submit_time,'%Y%m') as months,
            day(last_day(submit_time)) as days
    from practice_record
    where year(submit_time)= '2021')  t1
group by months
with rollup;
#coalesce函数遇到非null即停止返回该值