看起来你的进步分数是最后一个减第一个。如果总是有三个分数，可以使用如下条件聚合：

select college_id, student_id,
       sum(case when f1 = 3 then score
                when f1 = 1 then - score
           end) as progress
from t
group by college_id, student_id;

如果不总是有三个分数，你可以概括如下：

select college_id, student_id,
       sum(case when f1 = cnt then score
                when f1 = 1 then - score
           end) as progress
from (select t.*,
             count(*) over (partition by college_id, student_id) as cnt
      from t
     ) t
group by college_id, student_id;