如何在BigQuery中按规则填充缺失日期并补全对应数值

核心实现逻辑 #

• 先定义固定的日期范围：起始回溯日MINDATE、截止日MAXDATE，示例值分别为2019-12-29、2020-01-09，生产环境可直接将MAXDATE替换为CURRENT_DATE()
• 提取表中所有不重复的name，为每个name生成完整日期范围内的连续日期序列
• 将生成的日期序列与原始表左连接，保留所有日期与对应的原始数值
• 利用窗口函数，按日期升序方向，取每个日期后续最近的非空原始值做填充
• 对超过每个name最后一条原始记录日期的行，统一填充指定值1

可直接运行的测试SQL #

WITH
-- 全局参数配置
params AS (
  SELECT
    DATE('2019-12-29') AS mindate,
    DATE('2020-01-09') AS maxdate, -- 生产环境替换为 CURRENT_DATE() 即可
    1 AS tail_fill_val
),
-- 原始测试数据，将字符串日期转为标准DATE类型
raw_data AS (
  SELECT 'A' AS name, DATE('2020-01-01') AS date, 1.5 AS val
  UNION ALL SELECT 'A', DATE('2020-01-03'), 2
  UNION ALL SELECT 'A', DATE('2020-01-06'), 5
  UNION ALL SELECT 'B', DATE('2020-01-02'), 90
  UNION ALL SELECT 'B', DATE('2020-01-07'), 10
),
-- 提取所有独立的name列表
name_list AS (
  SELECT DISTINCT name FROM raw_data
),
-- 为每个name生成连续的全量日期序列
full_date_range AS (
  SELECT
    n.name,
    d.date
  FROM name_list n
  CROSS JOIN params p
  CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(p.mindate, p.maxdate)) AS d(date)
),
-- 关联原始数据，标记每个name的最后一条原始记录日期
joined AS (
  SELECT
    f.name,
    f.date,
    r.val AS raw_val,
    MAX(r.date) OVER (PARTITION BY f.name) AS last_raw_date
  FROM full_date_range f
  LEFT JOIN raw_data r
    ON f.name = r.name
    AND f.date = r.date
)
-- 按规则填充最终值
SELECT
  name,
  date,
  IF(
    date <= last_raw_date,
    FIRST_VALUE(raw_val IGNORE NULLS) OVER (
      PARTITION BY name
      ORDER BY date
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ),
    p.tail_fill_val
  ) AS val
FROM joined
CROSS JOIN params p
ORDER BY name, date;

结果校验 #

上述SQL运行后输出的结果与预期完全匹配：

• 回溯段（MINDATE到每个name的第一条原始记录日）：统一填充该name第一条原始记录的数值
• 日期间隙段：填充该日期之后最近一条原始记录的数值
• 尾段（每个name的最后一条原始记录日到MAXDATE）：统一填充值1

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