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

select device_id,
date_format(event_date,'%Y-%m') as ym,
sum(count(1))over(partition by device_id order by date_format(event_date,'%Y-%m')) as sum_cnt,
round(avg(count(*))over(partition by device_id order by date_format(event_date,'%Y-%m') rows 2 preceding),2) as avg3_cnt,
sum(count(1))over(order by date_format(event_date,'%Y-%m')) as total_cnt
from question_practice_detail
group by device_id,date_format(event_date,'%Y-%m')
order by device_id,date_format(event_date,'%Y-%m')

窗口函数的综合应用，求三个月的平均数要用到rows 2 preceding，累加求和使用sum(count(1))然后正常分组求和即可