SQL向下填充

6.SQL向下填充

with soucre as ( 
    select 1 as id , 3 as score 
    union all 
    select 2 as id , 4 as score 
    union all 
    select 3 as id , null as score 
    union all 
    select 4 as id , 3 as score 
    union all 
    select 5 as id , null as score 
    union all 
    select 6 as id , null as score 
    union all 
    select 7 as id , 5 as score ) -- 测试数据表创建
    
 select step_1.id,step_1.score as old_score,step_2.score as new_score
 from ( 
     select id
     ,score 
     ,sum(case when score is null then 0 else 1 end)over(order by id) as rank_id 
     from soucre ) as step_1     -- step_1:score是否有值分组排序
join( 
    select id
    ,score
    ,row_number()over(order by id) as join_id 
    from soucre where score is not null ) as step_2  -- step_2:score有值分组排序
on step_1.rank_id = step_2.join_id