在BigQuery中实现月度数据拆分日度及每日成本计算

完整查询代码 #

WITH DailyBreakdown AS (
  SELECT
    StartDate,
    EndDate,
    IDCampaign,
    TotalCost,
    -- 计算日期范围内的总天数（包含首尾两天）
    DATE_DIFF(EndDate, StartDate, DAY) + 1 AS TotalDays,
    -- 生成每日日期数组并展开
    UNNEST(GENERATE_DATE_ARRAY(StartDate, EndDate, INTERVAL 1 DAY)) AS DailyDate
  FROM tmptable
)
SELECT
  DailyDate AS StartDate, -- 替换为每日日期
  EndDate,
  IDCampaign,
  -- 计算每日成本，格式化保留两位小数并使用逗号作为分隔符
  FORMAT("%.2f", TotalCost / TotalDays) AS TotalCost
FROM DailyBreakdown
ORDER BY IDCampaign, DailyDate;

关键步骤说明 #

• 保留原始关联字段：直接从tmptable获取所有核心字段（IDCampaign、TotalCost、原始起止日期），确保拆分后的每日数据能和原记录关联
• 计算准确总天数：用DATE_DIFF(EndDate, StartDate, DAY) + 1确保包含起止当天，比如2024-01-01到2024-01-02实际是2天，DATE_DIFF返回1，加1后得到正确的天数统计
• 拆分日期范围：通过UNNEST(GENERATE_DATE_ARRAY(...))将连续日期范围拆分为单个日期条目
• 格式化日均成本：用总费用除以总天数得到日均成本，通过FORMAT函数输出为两位小数的格式，匹配示例中的显示样式

结果验证 #

以C1的记录为例：

• 2024-01-01到2024-03-31共91天（31+29+31）
• 每日成本：50000 / 91 ≈ 555.55，与期望输出一致

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