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

SELECT exam_id,start_month,month_cnt,
    sum(month_cnt) over (
        partition by
            exam_id
        order by start_month asc
	# 窗口函数over()中如果使用排序，则前面的聚合函数按照当前行为最后一行进行聚合
    ) as cum_exam_cnt
	
from
    (
        select
            exam_id,
            DATE_FORMAT(start_time, '%Y%m') as start_month,
            COUNT(start_time) as month_cnt
        from
            exam_record
        group by exam_id,start_month
        order by start_month
    ) as t1
group by exam_id,start_month
order by exam_id,start_month