Oracle聚合功能分配金额 [英] Oracle aggregation function to allocate amount

问题描述

假设我有2个表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中的项目分配到具有相同类型的每个包中，即结果应该像这样

Each record in table T1 represents a bag and its capacity, here I have 5 bags. I want to write an SQL that allocate items in table T2 into each bag with the same type, i.e. the result should be like this

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.我猜测，如果存在，它可能会这样使用

Therefore, I am finding some kind of aggregation function, let's call it allocate(), that can produce the column allocated_amount as above. I have a guess that, if exists, it might be used like this

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来实现.

My current solution is to use a temp table and PL/SQL loop for calculation, but I hope I can do it with one simple SQL.

推荐答案

您正在寻找一个累加的总和.像这样:

You are looking for a cumulative sum. Something like this:

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;

这篇关于Oracle聚合功能分配金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！