题解 | #每份试卷每月作答数和截止当月的作答总数。#

select
exam_id,
start_month,
month_cnt,
sum(month_cnt) over (partition by exam_id order by start_month ) cum_exam_cnt
from 
(
    select
    exam_id,
    DATE_FORMAT(start_time,'%Y%m') start_month,
    count(exam_id) month_cnt
    from exam_record
    group by exam_id,DATE_FORMAT(start_time,'%Y%m')
) a
order by exam_id,start_month

窗口函数基础用法，

sum(month_cnt) over (partition by exam_id order by start_month ) cum_exam_cnt

表示按照exam_id为维度的滑动窗口计算month_cnt的总值