SQL进阶技巧：如何将同一组内稀疏字段用有值的数据填充完整【数据清洗技巧】？

0 需求

1 需求分析

需求分析：需求中需要求出分组中按成绩排名取倒数第二的值作为新字段，且分组内没有倒数第二条的时候取当前值。

如果本题只是求分组内排序后倒数第二，则很简单，使用row_number()函数即可求出，但是本题问题点在于没有倒数第二时候需要保留当前值，如何优雅求出呢？

使用row_number()函数得到如下结果

with data as
         (select 111 as stu_id, 'class1' as class_name, 69 as score
          union all
          select 113 as stu_id, 'class1' as class_name, 74 as score
          union all
          select 112 as stu_id, 'class1' as class_name, 80 as score
          union all
          select 115 as stu_id, 'class1' as class_name, 93 as score
          union all
          select 114 as stu_id, 'class1' as class_name, 94 as score
          un