【sql 孤岛问题 】填充空值为最近的不为空的数据

0. 题目

根据value数据原表补充缺失数据，如果当月value数据没有则取上月填充,上月也没有则一直向上查找首个不为空的值

输入表

month	value
1	200
2	null
3	null
4	600
5	null

预期结果集

month	value
1	200
2	200
3	200
4	600
5	600

1、gaps-and-islands

这类问题被称为gaps-and-islands （间隙与孤岛），

思路：将行拆分为“组”。其中每个组中只有第一行(上边界)为NOT NUL 即,组的上边界 是第一行not null的值，组的下边界是下一组第一行not null的值的序号减一。这样再组内赋值即可。

关键：使用开窗函数 lead，lead ：形象的理解就是把数据从下向上顶👆，下端出现空格。

lead 语法:
lead(col,n,default) 用于统计窗口内往下第n行值
- 参数1为列名，即需要查找的字段
- 参数2为往下第n行（可选，默认为1），即往后查找的num行的数据
- 参数3为默认值（当往下第n行为null时候，取默认值，如不指定，则为null

最终sql

with t as (
select month as roof_idx,
lead(month,1, (select max(month) from table) +1) ---若没有找到边界,取月份最大值+1
over (order by month ) -1 as floor_idx,  ---注意这里有个减一,因为lead取的是下个组第一行not null的值,所以得减
value 
where value is  not null),  --t表先找到每组中not null的值，并通过lead确定每组的下界
result as (
select raw.month ,t.value  --给null的行赋值
from table raw,t   --t是with临时表的名称,这里是笛卡尔积;实际生产建议按需join
where raw.value is null and (raw.month between t.from_month and t.to_month) -- 按组找出null值的行,赋值
union all  --上面给null行赋值操作排除了not null行，下面union回来
select month ,value from table where value is not null) 
select * from result order by month 

1. t 这个临时表的工作就是找出每个组的roof和floor 的index,这里month是按顺序来的所以可以直接用,一般我们可能还需要先row number 构造一个序列;
2. t 构造好上下组边界后,result临时表开始构建返回结果,我们 where clause 选择 value 为null的行 +

也可以用cte递归写法，待补充