如何在BigQuery中对含timestamp列的表进行反向填充、正向填充与线性插值？

需求：处理包含timestamp列的表，对mycol列的空值按以下规则填充：

• 开头的连续空值：反向填充（用第一个非空值填充）
• 结尾的连续空值：正向填充（用最后一个非空值填充）
• 中间的空值：基于前后非空值做线性插值

原始数据 #

预期结果 #

BigQuery 实现SQL #

WITH original_data AS (
  SELECT * FROM UNNEST([
    STRUCT(1 AS timestamp, NULL AS mycol),
    STRUCT(2, NULL),
    STRUCT(3, 69),
    STRUCT(4, NULL),
    STRUCT(5, 71),
    STRUCT(6, 72),
    STRUCT(7, NULL)
  ])
),
grouped AS (
  SELECT
    timestamp,
    mycol,
    SUM(CASE WHEN mycol IS NOT NULL THEN 1 ELSE 0 END) OVER (ORDER BY timestamp) AS group_id
  FROM original_data
),
boundaries AS (
  SELECT
    timestamp,
    mycol,
    group_id,
    FIRST_VALUE(mycol) OVER (PARTITION BY group_id ORDER BY timestamp) AS first_val,
    LAST_VALUE(mycol) OVER (PARTITION BY group_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val,
    MIN(timestamp) OVER (PARTITION BY group_id) AS min_ts,
    MAX(timestamp) OVER (PARTITION BY group_id) AS max_ts,
    LEAD(timestamp) OVER (ORDER BY timestamp) AS next_ts,
    LEAD(mycol) OVER (ORDER BY timestamp) AS next_val,
    LAG(timestamp) OVER (ORDER BY timestamp) AS prev_ts,
    LAG(mycol) OVER (ORDER BY timestamp) AS prev_val
  FROM grouped
)
SELECT
  timestamp,
  CASE
    WHEN group_id = 0 THEN (SELECT mycol FROM original_data WHERE mycol IS NOT NULL ORDER BY timestamp LIMIT 1)
    WHEN timestamp = (SELECT MAX(timestamp) FROM original_data) AND mycol IS NULL THEN last_val
    WHEN mycol IS NULL THEN 
      prev_val + (next_val - prev_val) * (timestamp - prev_ts) / (next_ts - prev_ts)
    ELSE mycol
  END AS mycol
FROM boundaries
ORDER BY timestamp;

代码说明 #

1. original_data：模拟原始表数据，实际使用时替换为你的目标表名。
2. grouped：通过累计非空值的数量，将连续空值与相邻非空值划分为同一分组，明确填充/插值的区间范围。
3. boundaries：用窗口函数获取每个位置的前后非空值、分组内的首尾值，为不同场景的空值处理准备基础数据。
4. 最终SELECT：按规则处理空值：
   • 开头无有效分组（group_id=0）的空值，取表中第一个非空值填充；
   • 结尾的空值，取所在分组的最后一个非空值填充；
   • 中间空值通过线性插值公式计算；
   • 非空值直接保留原值。

内容的提问来源于stack exchange，提问作者Muhammad Ikhwan Perwira