明确题意：

统计每份试卷每月作答数和截止当月的作答总数；

问题分解：

• 统计每份试卷每月被作答数，生成子表 t_exam_month_cnt:
  • 统计每份试卷被作答月份，生成子表t_exam_record_month:
    • 提取每次作答的月份：DATE_FORMAT(start_time, "%Y%m") as start_month
  • 按试卷ID和月份分组： GROUP BY exam_id, start_month
  • 统计每份试卷每月被作答次数：SELECT exam_id, start_month, count(1) as month_cnt
• 统计截止当月的作答总数：
  • 按试卷分区按月份排序累积求和：SUM(month_cnt) over(PARTITION BY exam_id ORDER BY start_month) as cum_exam_cnt

细节问题：

• 表头重命名：as

完整代码：

SELECT exam_id, start_month, month_cnt,
    SUM(month_cnt) over(PARTITION BY exam_id ORDER BY start_month) as cum_exam_cnt
FROM (
    SELECT exam_id, start_month, count(1) as month_cnt
    FROM (
        SELECT exam_id, DATE_FORMAT(start_time, "%Y%m") as start_month
        FROM exam_record
    ) as t_exam_record_month -- 每份试卷被作答月份
    GROUP BY exam_id, start_month
) as t_exam_month_cnt; -- 每份试卷每月被作答数