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

一、知识点

窗口函数 sum(month_cnt) over(partition by exam_id rows between unbounded preceding and current row)

二、解题步骤

第一步

子查询1查出exam_id和每个试卷作答记录对应的月份 用到的函数是date_format

第二步

子查询2计算每份试卷的每月作答次数month_cnt

第三步

外层查询计算，使用聚合窗口函数，对第二步当中求出的month_cnt进行求和，范围是：前面的所有行到当前行，即rows between unbounded preceding and current row

总结

我这种做法，虽然有点繁琐，应该可以优化，但是好处是在每一步都非常清晰了然。

三、完整代码

select exam_id, 
start_month, 
month_cnt,
sum(month_cnt) over(partition by exam_id rows between unbounded preceding and current row) as cum_exam_cnt
from (
select exam_id, 
start_month,
count(start_month) as month_cnt
from(
    select exam_id,date_format(start_time, '%Y%m') as start_month
    from exam_record
    order by exam_id, start_month
) as t1
group by exam_id, start_month
order by exam_id, start_month
) as t2
order by exam_id, start_month