BigQuery如何补全用户余额表缺失日期的最近更新值？

实现这个需求最简洁的方案是结合 BigQuery 原生的日期数组生成能力和LAST_VALUE窗口函数的忽略空值特性完成，无需复杂的自关联逻辑。
完整查询代码如下：

WITH global_params AS (
  -- 计算全表最大日期作为补全截止日期
  SELECT MAX(updated_ag) AS max_date FROM balance
),
user_date_range AS (
  -- 按用户计算各自的起始日期，生成从起始到最大日期的连续日期序列
  SELECT 
    user_id,
    MIN(updated_ag) AS user_min_date
  FROM balance
  GROUP BY user_id
),
user_full_dates AS (
  -- 展开日期数组得到每个用户的完整连续日期
  SELECT
    udr.user_id,
    date
  FROM user_date_range udr,
       global_params gp,
       UNNEST(GENERATE_DATE_ARRAY(udr.user_min_date, gp.max_date)) AS date
)
-- 关联原表+填充空值
SELECT
  ufd.user_id,
  ufd.date,
  LAST_VALUE(b.value IGNORE NULLS) OVER(
    PARTITION BY ufd.user_id 
    ORDER BY ufd.date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS value
FROM user_full_dates ufd
LEFT JOIN balance b 
  ON ufd.user_id = b.user_id 
  AND ufd.date = b.updated_ag
ORDER BY user_id, date

逻辑说明 #

• 第一步先计算全表最大日期，作为所有用户补全数据的统一截止时间
• 第二步按用户维度计算每个用户的第一条余额记录日期，作为该用户补全数据的起始时间
• 用GENERATE_DATE_ARRAY生成每个用户从起始到截止的连续日期数组，再通过UNNEST展开为行级数据，得到每个用户的完整日期序列
• 左关联原余额表后，使用LAST_VALUE(b.value IGNORE NULLS)窗口函数，按用户分组、日期升序排序，自动将缺失日期的空余额值填充为最近一次非空的余额值
  运行上述查询即可直接得到和示例预期完全一致的输出结果。

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