BigQuery按配额批次顺序填充聚合采购数据SQL实现问询

• 存在存储产品配额批次的表，单个产品对应多个quota_id，每个quota_id对应不同配额值，对应CTE quota。
• 另有采购表，需要和quota表关联聚合，匹配规则为：采购量优先填充batch_date更早的首个配额批次，首个批次配额填满后，剩余采购量再计入下一批次，对应CTE purchase。

现有SQL代码 #

WITH quota AS (
select 'Product A' as product, 'Quota_batch_1' as quota_id, 10 as quota, '2022-01-01' as batch_date
UNION All
select 'Product A' as product, 'Quota_batch_2' as quota_id, 20 as quota, '2022-02-01' as batch_date
),

purchase as (
  select '2022-01-01' as sales_date, 5 as qty, 'sales_1' as sales_id, 'Product A' as product, 100 as price
  UNION ALL
  select '2022-01-01' as sales_date, 5 as qty, 'sales_2' as sales_id, 'Product A' as product, 150 as price
  UNION ALL
  select '2022-02-03' as sales_date, 2 as qty, 'sales_3' as sales_id, 'Product A' as product, 200 as price
)
select 
quota.*,
sum(qty) as quota_filled_by_qty,
sum(price) as total_price

from quota
left join purchase using (product)
group by 1,2,3,4

预期结果 #

• Quota_batch_1填充10个采购量，对应总金额250
• Quota_batch_2填充2个采购量，对应总金额200

现存问题 #

当前SQL直接全量关联后聚合，所有批次都统计了全部采购数据，结果不符合预期，已尝试分析函数、累计求和等方案未解决。

核心逻辑是通过累计值计算区间，再匹配重叠区间完成FIFO（先进先出）配额分配：

1. 对quota表按产品分组、按批次日期升序排序，计算截止当前批次的累计配额值，得到每个批次的配额占用区间[上一批次累计配额, 当前批次累计配额)
2. 对purchase表按产品分组、按销售日期+销售ID升序排序（保证先发生的采购优先占用早批次配额），计算截止当前采购记录的累计采购量，得到每笔采购的量占用区间[上一条累计采购量, 当前累计采购量)
3. 关联两个结果集，匹配同产品下两个区间存在重叠的记录，计算重叠部分的采购量、按占比分摊对应金额，最后按配额批次分组聚合即可。

可运行SQL代码 #

WITH quota AS (
select 'Product A' as product, 'Quota_batch_1' as quota_id, 10 as quota, '2022-01-01' as batch_date
UNION All
select 'Product A' as product, 'Quota_batch_2' as quota_id, 20 as quota, '2022-02-01' as batch_date
),
purchase as (
  select '2022-01-01' as sales_date, 5 as qty, 'sales_1' as sales_id, 'Product A' as product, 100 as price
  UNION ALL
  select '2022-01-01' as sales_date, 5 as qty, 'sales_2' as sales_id, 'Product A' as product, 150 as price
  UNION ALL
  select '2022-02-03' as sales_date, 2 as qty, 'sales_3' as sales_id, 'Product A' as product, 200 as price
),
-- 计算配额累计区间
quota_cum AS (
  SELECT 
    *,
    COALESCE(SUM(quota) OVER(PARTITION BY product ORDER BY batch_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS quota_start,
    SUM(quota) OVER(PARTITION BY product ORDER BY batch_date) AS quota_end
  FROM quota
),
-- 计算采购累计区间
purchase_cum AS (
  SELECT
    *,
    COALESCE(SUM(qty) OVER(PARTITION BY product ORDER BY sales_date, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) AS pur_start,
    SUM(qty) OVER(PARTITION BY product ORDER BY sales_date, sales_id) AS pur_end
  FROM purchase
)
SELECT
  q.product,
  q.quota_id,
  q.quota,
  q.batch_date,
  -- 计算区间重叠部分的采购量
  SUM(LEAST(q.quota_end, p.pur_end) - GREATEST(q.quota_start, p.pur_start)) AS quota_filled_by_qty,
  -- 按重叠量占单笔采购量的比例分摊金额
  SUM(
    (LEAST(q.quota_end, p.pur_end) - GREATEST(q.quota_start, p.pur_start)) * p.price / p.qty
  ) AS total_price
FROM quota_cum q
LEFT JOIN purchase_cum p
  ON q.product = p.product
  -- 区间重叠判断条件
  AND q.quota_start < p.pur_end
  AND q.quota_end > p.pur_start
GROUP BY q.product, q.quota_id, q.quota, q.batch_date
ORDER BY q.batch_date;

运行结果完全匹配预期：Quota_batch_1填充量10、总金额250，Quota_batch_2填充量2、总金额200。若采购量超过总配额，超出部分不会计入任何批次；多产品场景下逻辑会自动按产品维度隔离计算。

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