BigQuery中如何按月补全用户交易数据缺失日期的对应值？

这是实现需求最简洁的写法，核心利用BigQuery内置的日期数组生成函数实现：

WITH original_data AS (
  -- 你的原始数据
  select 
      1 as user_id, date('2021-01-01') as transaction_date, 1 as value
  union all (select 1, '2021-01-02', 2)
  union all (select 1, '2021-01-05', 2)
  union all (select 1, '2021-02-01', 2)
  union all (select 1, '2021-02-03', 2)
  union all (select 2, '2021-01-02', 2)
  union all (select 2, '2021-02-01', 2)
  union all (select 2, '2021-02-03', 3)
),
-- 计算每个用户每个月的交易首尾日期
user_month_range AS (
  SELECT 
    user_id,
    DATE_TRUNC(transaction_date, MONTH) as month,
    MIN(transaction_date) as month_start,
    MAX(transaction_date) as month_end
  FROM original_data
  GROUP BY user_id, month
),
-- 生成连续日期
all_dates AS (
  SELECT 
    user_id,
    date
  FROM user_month_range,
  UNNEST(GENERATE_DATE_ARRAY(month_start, month_end)) as date
)
-- 左关联匹配原始数据的value
SELECT 
  a.user_id,
  a.date as transaction_date,
  o.value
FROM all_dates a
LEFT JOIN original_data o 
  ON a.user_id = o.user_id 
  AND a.date = o.transaction_date
ORDER BY a.user_id, a.date

逻辑说明 #

• 先对每个用户+月份分组，取当月该用户最早和最晚的交易日期作为补全范围的边界，不会补全当月没有交易的日期段
• GENERATE_DATE_ARRAY直接生成边界内的所有连续日期，配合UNNEST展开为行级数据
• 左关联原始表后，没有交易的日期value自动为null，完全符合预期输出

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