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

#不使用子查询，直接查
select device_id,
date_format(event_date,'%Y-%m') as ym,
sum(count(*) ) 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(*)) 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')