BigQuery Pro Rata按日平摊支出：低效循环脚本的替代实现咨询

原实现的性能瓶颈根因 #

原脚本采用WHILE循环逐行处理的逻辑，完全不符合BigQuery分布式列存引擎的执行逻辑，是导致运行效率极低的核心原因：

• 逐行串行执行的循环逻辑，把原本可以并行批量处理的任务拆分为N次独立的作业调度，哪怕仅处理3行源数据，每轮循环都会触发表扫描、子查询重算、写入锁等待的固定调度开销，累计耗时会被放大到完全不合理的程度
• 代码存在大量冗余计算：为每行匹配行号的派生表重复扫描、对单条记录执行SUM聚合、多余的类型转换，进一步拉低了执行效率

优化后的核心实现 #

优化思路是完全去掉循环逻辑，依托BigQuery原生的数组展开、向量化计算能力，一次性完成全量源数据的日期展开、平摊计算，全程无重复扫描、无串行等待：

-- 直接重建结果表，一步完成所有计算
CREATE OR REPLACE TABLE `pro_rata_test.pro_rata` AS
SELECT
  day AS date,
  country,
  campaign,
  other,
  -- 直接在当前行计算单条记录覆盖的总天数，平摊到每个自然日
  spend / (DATE_DIFF(date_end, date_start, DAY) + 1) AS spend
FROM
  -- 对每行源数据直接生成日期区间数组，自动展开为按日粒度的行
  `pro_rata_test.data`,
  UNNEST(GENERATE_DATE_ARRAY(date_start, date_end)) AS day
ORDER BY date, country, campaign;

适配扩展场景 #

如果同一自然日、同一维度（country+campaign+other）下存在多条源数据的分摊结果，需要做日粒度聚合的话，只需在查询末尾增加分组聚合逻辑即可：

CREATE OR REPLACE TABLE `pro_rata_test.pro_rata` AS
SELECT
  day AS date,
  country,
  campaign,
  other,
  -- 对同维度同日的平摊值做汇总
  SUM(spend / (DATE_DIFF(date_end, date_start, DAY) + 1)) AS spend
FROM
  `pro_rata_test.data`,
  UNNEST(GENERATE_DATE_ARRAY(date_start, date_end)) AS day
GROUP BY date, country, campaign, other
ORDER BY date;

效率对比 #

优化后的方案仅需一次全表扫描，整个流程没有循环、没有重复子查询计算，哪怕源表行数到百万级，也可以在秒级到十秒级返回结果，相比原逐行循环的实现，性能会有数量级的提升。

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