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

1. 使用sum(month_cnt) 的一个窗口函数来计算每月份的累计作答次数。


# 每份试卷每月作答数和截止当月的作答数 。 


select A.exam_id,
       A.start_month,
       A.month_cnt,
       sum(A.month_cnt) over(partition by exam_id order by start_month) cum_exam_cnt 
  from (
      select ER.exam_id ,
             date_format(start_time,'%Y%m') start_month,
             count(start_time) month_cnt 
        from exam_record ER 
       group by ER.exam_id ,date_format(start_time,'%Y%m') 
    ) A 
    order by exam_id,start_month ;