題解 | #月總刷題數(shù)和日均刷題數(shù)#
月總刷題數(shù)和日均刷題數(shù)
http://www.fangfengwang8.cn/practice/f6b4770f453d4163acc419e3d19e6746
我就要搞不一樣的
with t1 as ( select '01' month, 31 days from dual UNION select '02' month, 28 days from dual UNION select '03' month, 31 days from dual UNION select '04' month, 30 days from dual UNION select '05' month, 31 days from dual UNION select '06' month, 30 days from dual UNION select '07' month, 31 days from dual UNION select '08' month, 31 days from dual UNION select '09' month, 30 days from dual UNION select '10' month, 31 days from dual UNION select '11' month, 30 days from dual UNION select '12' month, 31 days from dual ), t2 as ( select uid, DATE_FORMAT(submit_time, '%Y%m') submit_month, DATE_FORMAT(submit_time, '%m') month from practice_record where DATE_FORMAT(submit_time, '%Y')=2021 ), t3 as ( select t2.submit_month, count(t2.uid) month_q_cnt, round(count(t2.uid)/t1.days,3) avg_day_q_cnt from t1 JOIN t2 on t1.month = t2.month group by t2.submit_month, t1.days order by submit_month ), t4 as ( select "2021匯總" submit_month, count(uid) month_q_cnt, round(count(uid)/31,3) avg_day_q_cnt from t2 ), t5 as ( select * from t3 UNION select * from t4 ) select* from t5
