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

SELECT exam_id, DATE_FORMAT(start_time,'%Y%m') start_month, 
COUNT(DATE_FORMAT(start_time,'%Y%m')) month_cnt, 
SUM(COUNT(DATE_FORMAT(start_time,'%Y%m'))) over (partition by exam_id order by DATE_FORMAT(start_time,'%Y%m'))
FROM exam_record
GROUP BY exam_id, start_month
ORDER BY exam_id, start_month;

这里要mark的是SUM(xx) over (partition by ... order by ...)这种求累计截止当前数量的方式。“截止当前”主要体现在order by。没有order by只是partition by xxx 的话就是一般的分类统计，与group by的区别是窗口函数不会减少原表的行数。