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

select date_format(submit_time,'%Y%m') as submit_month,
any_value(count(submit_time)) as month_q_cnt,
any_value(round(count(submit_time)/day(last_day(submit_time)),3)) as avg_day_q_cnt
from practice_record
where year(submit_time)=2021
group by date_format(submit_time,'%Y%m')

union all

select '2021汇总' as submit_month , any_value(count(submit_time)) as month_q_cnt,any_value(round(count(submit_time)/31,3)) as avg_day_q_cnt
from practice_record where score is not null
and year(submit_time)=2021

order by submit_month

求一个月有多少天：day(last_day())

select检索group by list 之外的列需要加 any_value()