题解 | #对试卷得分做min-max归一化#

可以看出第四列数据等于第三列之前的数据和

比如

所以我们可以先求出前三列，最后用窗口函数实现上面的功能

根据试卷和月份分组，求出每月都有多少答题数，只要有答题记录就算所以不用管有没有做完

select 
        exam_id,
        date_format(start_time,'%Y%m') as start_month,
        count(*) as month_cnt
    from exam_record
    group by exam_id,date_format(start_time,'%Y%m')

可以得出前三列

然后根据窗口函数进行累加

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,
        date_format(start_time,'%Y%m') as start_month,
        count(*) as month_cnt
    from exam_record
    group by exam_id,date_format(start_time,'%Y%m')
)t