使用BigQuery SQL在起止日期间按天分配营销活动预算

你需要针对每个营销活动单独生成日期数组，而非取全局的最小/最大日期范围，同时计算单日预算。以下是适配BigQuery的SQL代码：

SELECT
  营销活动名称,
  单日日期 AS 日期,
  FORMAT("%.2f €", 总预算 / (DATE_DIFF(CAST(结束日期 AS DATE), CAST(开始日期 AS DATE), DAY) + 1)) AS 每日预算
FROM
  total_budget,
  UNNEST(GENERATE_DATE_ARRAY(CAST(开始日期 AS DATE), CAST(结束日期 AS DATE))) AS 单日日期
ORDER BY
  营销活动名称, 日期

关键逻辑说明 #

• 用UNNEST(GENERATE_DATE_ARRAY(...))为每个活动单独生成从开始到结束的完整日期序列，避免全局日期范围导致的无效行
• DATE_DIFF(结束日期, 开始日期, DAY) + 1计算活动总天数（包含开始和结束当天）
• FORMAT("%.2f €", ...)将预算格式化为带欧元符号的数值，保留两位小数；如果不需要小数，可改为%.0f €
• 最终按活动名称和日期排序，确保结果顺序与预期一致

示例验证结果 #

代入你的测试数据后，运行代码会得到：

特殊情况处理 #

如果你的总预算字段是带€符号的字符串（而非纯数值），需要先提取数值再计算，代码调整如下：

SELECT
  营销活动名称,
  单日日期 AS 日期,
  FORMAT("%.2f €", CAST(REGEXP_EXTRACT(总预算, r'(\d+)') AS FLOAT64) / (DATE_DIFF(CAST(结束日期 AS DATE), CAST(开始日期 AS DATE), DAY) + 1)) AS 每日预算
FROM
  total_budget,
  UNNEST(GENERATE_DATE_ARRAY(CAST(开始日期 AS DATE), CAST(结束日期 AS DATE))) AS 单日日期
ORDER BY
  营销活动名称, 日期

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