sql - 用于分配金额的 Oracle 聚合函数

Question

假设我有 2 张 table T1和 T2如下
T1 :

bag_id bag_type capacity
------|--------|--------
  1       A       500
  2       A       300
  3       A       100
  4       B       200
  5       B       100

T2 :

item_type item_amount
---------|-----------
   A         850
   B         300

表中的每条记录 T1代表一个包和它的容量，这里我有 5 个包。我想编写一个 SQL 来分配表 T2 中的项目放入相同类型的每个包中，即结果应该是这样的

bag_id bag_type capacity allocated_amount
------|--------|--------|----------------
  1       A        500        500
  2       A        300        300
  3       A        100        50
  4       B        200        200
  5       B        100        100

因此， 我正在寻找某种聚合函数，我们称之为 allocate() ，即可以产生列allocated_amount如上。 我猜测，如果存在，它可能会像这样使用

select 
    t1.bag_id,
    t1.bag_type, 
    t1.capacity,
    allocate(t2.item_amount, t1.capacity) 
        over (partition by t1.bag_type order by t1.capacity desc) as allocatd_amount
from t1, t2
where t2.item_type = t1.bag_type

我目前的解决方案是使用临时表和 PL/SQL 循环进行计算，但我希望我可以用一个简单的 SQL 来完成。

Answer 1

您正在寻找累计金额。像这样的东西:

select t1.*,
       (case when cumecap <= t2.item_amount 
             then t1.capacity
             when cumecap - t1.capacity <= t2.item_amount
             then t2.item_amount - (cumecap - t1.capacity)
             else 0
        end) as allocated_capacity
from (select t1.*,
             sum(t1.capacity) over (partition by bag_type order by bag_id) as cumecap
      from t1
     ) t1 join
     t2
     on t1.bag_type = t2.item_type;