题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
select date_format(submit_time,'%Y%m') as submit_month, count(submit_time) as month_q_cnt, round(count(submit_time)/max(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 select '2021汇总' as submit_month, count(submit_time) as month_q_cnt, round(count(submit_time)/31,3) as avg_day_q_cnt from practice_record WHERE YEAR(submit_time) = 2021 order by submit_month asc
简化：
1. coalesce是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。可用于将空值替换成其他值（本题）/返回第一个非空值
2. with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息，WITH ROLLUP 是对group by分组后的数据进行汇总统计，必须和group by一起使用。
投递腾讯等公司10个岗位