题解 | 截至当月的练题情况

select device_id,ym,sum(cnt)over(partition by device_id order by ym) sum_cnt, 
round((cnt+lag(cnt,1,0)over(partition by device_id order by ym)+lag(cnt,2,0)over(partition by device_id order by ym))/(1+if(lag(cnt,1,0)over(partition by device_id order by ym)=0,0,1)+if(lag(cnt,2,0)over(partition by device_id order by ym)=0,0,1)),2) avg3_cnt,sum(cnt)over(order by ym) total_cnt
from
(select device_id,left(event_date,7) ym,count(question_id) cnt from question_practice_detail
group by device_id,left(event_date,7)) as newtable
order by device_id,left(ym,4),right(ym,2)

窗口函数参考：https://blog.csdn.net/qq_33218097/article/details/133147062?ops_request_misc=%257B%2522request%255Fid%2522%253A%25229cafcf17df5092f8a1e899238562a340%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=9cafcf17df5092f8a1e899238562a340&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-133147062-null-null.142^v102^pc_search_result_base8&utm_term=mysql%20%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0%E7%B4%AF%E5%8A%A0&spm=1018.2226.3001.4187

sum（）over( order by ) 可阶段性累加

lag向前取 lead向后取