题解|累加的思路|每份试卷每月作答数和截止当月的作答总数#

### 两步走
### 先构建前三列形成一个新表，然后在第一步构建的表的基础上，再次生成最后一列，因为最后一列是按照第二列分组后，求和第三列？？？是不是这么理解的：按照start_month进行分组，比如到202002这个月份时，整个month_cnt列只有202002和202001两个月的计数数据，那么第三列就是把这两个月的计数求和，然后就得到了cum_exam_cnt

# 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

# SELECT *,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') AS start_month, COUNT(start_time) AS month_cnt
# FROM exam_record
# GROUP BY exam_id,start_month
# ) t1

# ### l两步走直接合并起来就是：
# SELECT exam_id, date_format(start_time,'%Y%m') AS start_month, COUNT(start_time) AS month_cnt,
# SUM(COUNT(start_time)) OVER (PARTITION BY exam_id ORDER BY date_format(start_time,'%Y%m')) cum_exam_cnt
# FROM exam_record
# GROUP BY exam_id,start_month



### 也可以直接使用两个窗口函数
SELECT DISTINCT exam_id, date_format(start_time,'%Y%m') AS start_month,
COUNT(start_time) OVER (PARTITION BY exam_id, date_format(start_time,'%Y%m')) AS month_cnt,
COUNT(start_time) OVER (PARTITION BY exam_id ORDER BY date_format(start_time,'%Y%m')) AS cum_exam_cnt
FROM exam_record