题解 | #截至当月的练题情况#
截至当月的练题情况
https://www.nowcoder.com/practice/80ed62a097564fad880b967678e460fc
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))然后正常分组求和即可