BigQuery SQL，基于配额值的汇总

Question

问题：

• 所以我有一张包含产品配额批次的表。对于1个产品，它将具有几个quota_id，每个Quota_id具有不同的配额值。请在CTE 配额

  中找到此表
• 然后我有一张购买表，需要与配额表连接并进行汇总。汇总购买以填充第一个批次（第一个是用较早的批次_date批量的），然后在配额已满时移至第二批次。请参阅CTE 购买

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

预期结果：

我不知道执行加入或聚合，因为我当前的结果仍在汇总为所有结果。我已经探索了使用分析功能，累积总和等，但仍然无法解决问题。

当前结果：

有什么想法吗？谢谢

Answer 1

使用 /a>并调整您的JOIN条件：

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_next_batch as (
  SELECT 
    *, 
    LEAD(batch_date) OVER (PARTITION BY product ORDER BY batch_date) as next_batch_date
  FROM quota
)
SELECT 
  q.product,
  q.quota_id,
  q.quota,
  q.batch_date,
  SUM(p.qty) as quota_filled_by_qty,
  SUM(p.price) as total_price
FROM quota_next_batch q 
LEFT JOIN purchase p
  ON p.product = q.product AND p.sales_date >= q.batch_date AND (q.next_batch_date IS NULL OR p.sales_date < q.next_batch_date)
GROUP BY q.product, q.quota_id, q.quota, q.batch_date

输出：

product     quota_id        quota   batch_date  quota_filled_by_qty total_price
Product A   Quota_batch_1   10      2022-01-01  10                  250
Product A   Quota_batch_2   20      2022-02-01  2                   200