在BigQuery中基于现有列生成完整月度日期列的方案求助

完整实现SQL #

WITH source_data AS (
  -- 替换为你的实际表名
  SELECT * FROM `your-project.your-dataset.your-table`
),
-- 获取唯一的维度组合（处理多Month/PlanSystemId场景）
unique_dimensions AS (
  SELECT DISTINCT Month, PlanSystemId 
  FROM source_data
),
-- 生成每个维度对应的全月日期序列
full_month_dates AS (
  SELECT 
    date,
    ud.Month,
    ud.PlanSystemId
  FROM unique_dimensions ud,
  UNNEST(GENERATE_DATE_ARRAY(
    PARSE_DATE('%Y-%m', ud.Month),  -- 转换为当月第一天
    LAST_DAY(PARSE_DATE('%Y-%m', ud.Month))  -- 当月最后一天
  )) AS date
)
-- 左连接源数据并填充缺失值
SELECT
  fmd.date AS Date,
  sd.DeliveryDate,
  fmd.Month,
  fmd.PlanSystemId,
  IFNULL(sd.cost, 0) AS cost
FROM full_month_dates fmd
LEFT JOIN source_data sd
  ON fmd.date = sd.DeliveryDate
  AND fmd.Month = sd.Month
  AND fmd.PlanSystemId = sd.PlanSystemId
ORDER BY fmd.date;

关键逻辑说明 #

• 维度锚定：先通过unique_dimensions提取唯一的Month+PlanSystemId组合，再和全月日期做笛卡尔积，确保每个日期都有对应的维度值，解决你之前左连后维度列缺失的问题。
• 日期生成：用BigQuery内置的GENERATE_DATE_ARRAY直接生成当月1号到月底的完整日期序列，无需手动构造循环。
• 缺失值处理：左连后用IFNULL将无匹配记录的cost设为0，DeliveryDate自然保留为null，完全匹配需求。
• 多场景兼容：即使源表存在多个不同的Month或PlanSystemId，该SQL也能自动生成对应每个维度组合的全月数据。

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