在BigQuery中特定场景下将列中的NULL值替换为对应整数

我们有一列col1，它在分区内是仅递增的整数列，包含大量NULL值。需求很明确：只把夹在两个相同非NULL整数中间的NULL替换成该整数，其他NULL保持不变。比如示例里第11、13、14行的NULL要换成2，第24行的NULL换成3，其余NULL原样保留。

先看示例数据：

with
  t1 as (
    select 1 as rowNum, null as col1 union all
    select 2 as rowNum, null as col1 union all
    select 3 as rowNum, 1 as col1 union all
    select 4 as rowNum, null as col1 union all
    select 5 as rowNum, null as col1 union all
    select 6 as rowNum, null as col1 union all
    select 7 as rowNum, null as col1 union all
    select 8 as rowNum, null as col1 union all
    select 9 as rowNum, 2 as col1 union all
    select 10 as rowNum, 2 as col1 union all
    select 11 as rowNum, null as col1 union all
    select 12 as rowNum, 2 as col1 union all
    select 13 as rowNum, null as col1 union all
    select 14 as rowNum, null as col1 union all
    select 15 as rowNum, 2 as col1 union all
    select 16 as rowNum, null as col1 union all
    select 17 as rowNum, null as col1 union all
    select 18 as rowNum, null as col1 union all
    select 19 as rowNum, null as col1 union all
    select 20 as rowNum, null as col1 union all
    select 21 as rowNum, null as col1 union all
    select 22 as rowNum, 3 as col1 union all
    select 23 as rowNum, 3 as col1 union all
    select 24 as rowNum, null as col1 union all
    select 25 as rowNum, 3 as col1 union all
    select 26 as rowNum, 3 as col1 union all
    select 27 as rowNum, null as col1 union all
    select 28 as rowNum, null as col1 union all
    select 29 as rowNum, null as col1 union all
    select 30 as rowNum, 4 as col1 union all
    select 31 as rowNum, 4 as col1 union all
    select 32 as rowNum, null as col1 union all
    select 33 as rowNum, null as col1
  )
select * from t1;

解决方案 #

用窗口函数LAG和LEAD配合IGNORE NULLS参数，精准定位当前NULL前后最近的非NULL值，判断是否相等后进行填充：

with
  t1 as (
    select 1 as rowNum, null as col1 union all
    select 2 as rowNum, null as col1 union all
    select 3 as rowNum, 1 as col1 union all
    select 4 as rowNum, null as col1 union all
    select 5 as rowNum, null as col1 union all
    select 6 as rowNum, null as col1 union all
    select 7 as rowNum, null as col1 union all
    select 8 as rowNum, null as col1 union all
    select 9 as rowNum, 2 as col1 union all
    select 10 as rowNum, 2 as col1 union all
    select 11 as rowNum, null as col1 union all
    select 12 as rowNum, 2 as col1 union all
    select 13 as rowNum, null as col1 union all
    select 14 as rowNum, null as col1 union all
    select 15 as rowNum, 2 as col1 union all
    select 16 as rowNum, null as col1 union all
    select 17 as rowNum, null as col1 union all
    select 18 as rowNum, null as col1 union all
    select 19 as rowNum, null as col1 union all
    select 20 as rowNum, null as col1 union all
    select 21 as rowNum, null as col1 union all
    select 22 as rowNum, 3 as col1 union all
    select 23 as rowNum, 3 as col1 union all
    select 24 as rowNum, null as col1 union all
    select 25 as rowNum, 3 as col1 union all
    select 26 as rowNum, 3 as col1 union all
    select 27 as rowNum, null as col1 union all
    select 28 as rowNum, null as col1 union all
    select 29 as rowNum, null as col1 union all
    select 30 as rowNum, 4 as col1 union all
    select 31 as rowNum, 4 as col1 union all
    select 32 as rowNum, null as col1 union all
    select 33 as rowNum, null as col1
  ),
  t2 as (
    select 
      rowNum,
      col1,
      -- 获取当前行之前最近的非NULL值
      lag(col1 ignore nulls) over (order by rowNum) as prev_val,
      -- 获取当前行之后最近的非NULL值
      lead(col1 ignore nulls) over (order by rowNum) as next_val
    from t1
  )
select
  rowNum,
  case
    when col1 is not null then col1
    when prev_val = next_val then prev_val
    else null
  end as col1
from t2
order by rowNum;

逻辑说明 #

1. LAG(col1 IGNORE NULLS)：跳过所有NULL，找到当前行之前最近的有效col1值；
2. LEAD(col1 IGNORE NULLS)：跳过所有NULL，找到当前行之后最近的有效col1值；
3. 当当前行col1为NULL时，检查前后的有效数值是否相等：相等则用该数值替换NULL，不相等则保留NULL。

因为col1是分区内仅递增的，所以不会出现前后值递减的情况，只需要判断是否相等即可。

内容的提问来源于stack exchange，提问作者Canovice