BigQuery中基于起止日期的日销售总额聚合优化方案咨询

我在BigQuery数据仓库中处理一组「销售事件」记录，每条记录代表某一SKU在指定start_date和end_date时段内以特定sale_price可售的状态，需要计算每日在售商品的总金额（简化计算，假设数量=1）。

输入示例 #

输出示例 #

*items_for_sale仅为辅助说明，非必填输出

当前使用的方案虽然简洁，但计算量极大，不适用于海量数据，现寻求无需为每个活跃日期复制销售记录的优化方法。

现有方案代码 #

with date_series as (
select dd
from unnest(generate_date_array(date('2023-01-01'), date('2023-01-10'), INTERVAL 1 DAY)) as dd)

select
d.dd as selling_date,
sum(sale_price) as total_value_for_sale
from date_series d
left join sales_records s on s.start_date <= d.dd and s.end_date >= d.dd
group by selling_date
order by selling_date

优化方案 #

采用事件流+累计求和的方式，避免生成日期序列后做全量关联，大幅降低计算量：

核心思路 #

把每条销售记录拆成两个事件：

• 在start_date当天，增加对应商品的售价金额
• 在end_date + 1当天，减去对应商品的售价金额
  然后按日期汇总这些金额变化，再通过累计求和得到每日的在售总金额，最后补全完整日期序列确保输出连续。

优化后代码 #

-- 生成事件流：每条记录拆分为生效和失效两个事件
with events as (
  select 
    start_date as event_date,
    sale_price as price_change
  from sales_records
  union all
  select 
    date_add(end_date, interval 1 day) as event_date,
    -sale_price as price_change
  from sales_records
),
-- 计算每日的金额变化总和
daily_changes as (
  select
    event_date,
    sum(price_change) as daily_change
  from events
  group by event_date
),
-- 生成完整的日期区间（覆盖所有销售记录的起止日期）
date_range as (
  select 
    dd as selling_date
  from unnest(generate_date_array(
      (select min(start_date) from sales_records),
      (select max(end_date) from sales_records),
      interval 1 day
    )) as dd
),
-- 关联日期与变化，计算累计在售金额
running_totals as (
  select
    dr.selling_date,
    sum(dc.daily_change) over (order by dr.selling_date) as total_value_for_sale
  from date_range dr
  left join daily_changes dc on dr.selling_date = dc.event_date
)
select * from running_totals order by selling_date

方案优势 #

• 彻底避免了原方案中日期序列与销售记录的笛卡尔积关联，计算复杂度从O(N*M)降至O(N+M)（N为销售记录数，M为日期数）
• 针对海量数据场景，尤其是时间跨度大、销售记录多的情况，性能提升显著
• 逻辑直观，通过事件变化准确反映每日在售金额的增减

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