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

select DATE_FORMAT(submit_time,"%Y%m") as submit_month, count(DISTINCT uid,question_id,submit_time) as month_q_cnt, round(count(DISTINCT uid,question_id,submit_time)/ DAYOFMONTH(LAST_DAY(DATE_FORMAT(submit_time,"%Y%m%d"))) ,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 all SELECT '2021汇总' as submit_month, count(DISTINCT uid,question_id,submit_time) as month_q_cnt, round(count(DISTINCT uid,question_id,submit_time)/ DAYOFMONTH(LAST_DAY(DATE_FORMAT(submit_time,"%Y%m%d"))) ,3) as avg_day_q_cnt from practice_record where DATE_FORMAT(submit_time,"%Y") = 2021 group by DATE_FORMAT(submit_time,"%Y")