题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
select date_format(submit_time,'%Y%m') as submit_month, count(*) as month_q_cnt, any_value(round(count(*)/day(last_day(submit_time)),3)) as avg_day_q_cnt from practice_record where year(submit_time)=2021 group by submit_month union 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 group by date_format(submit_time,'%Y') order by submit_month;
使用any_value()函数可以解决ONLY_FULL_GROUP_BY报错。
不使用该函数可以对day(last_day(submit_time))求avg或max,min
原因是day(last_day(submit_time))返回的是一个序列,而不是一个数
select date_format(submit_time,'%Y%m') as submit_month, count(*) as month_q_cnt, round(count(*)/avg(day(last_day(submit_time))),3) as avg_day_q_cnt from practice_record where year(submit_time)=2021 group by submit_month union 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 group by date_format(submit_time,'%Y') order by submit_month;