Oracle aggregation function to allocate amount

Question

Suppose I have 2 tables T1 and T2 as follows

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

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

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

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.

is order of the allocation relevant? In your example data it seams you allocate on capacity DESC, in the aggregation function you order on capacity ASC. Or do you event look for optimal allocation? — Marmite Bomber, Jul 15 '15 at 16:03
Yes, I expected the order to be `DESC`. I will correct the post. — asinkxcoswt, Jul 15 '15 at 16:07

Gordon Linoff · Accepted Answer · 2015-07-15T17:00:19.833

4

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;

edited Jul 15 '15 at 17:00

answered Jul 15 '15 at 16:05

Gordon Linoff

1,242,037
58
646
786

typo - " from t1 ) t join" ==> "from t1 ) t1 join" Awesome query! – OldProgrammer Jul 15 '15 at 16:20

score 4 · Answer 2 · answered Jul 15 '15 at 16:26

This should do the trick:

select t1.bag_id
     , t1.bag_type
     , t1.capacity
     , least( t1.capacity -- don't over fill the bag
            , greatest( 0 -- don't under fill the bag
                      , t2.item_amount  -- to be allocated
                      - nvl(sum(t1.capacity) -- less previous allocations
                          over (partition by t1.bag_type
                                    order by t1.capacity desc
                             rows between unbounded preceding and 1 preceding)
                           , 0))) Allocated
  from t1
  join t2
    on t2.item_type = t1.bag_type;

    BAG_ID B   CAPACITY  ALLOCATED
---------- - ---------- ----------
         1 A        500        500
         2 A        300        300
         3 A        100         50
         4 B        200        200
         5 B        100        100

score 3 · Answer 3 · answered Jul 15 '15 at 16:22

assuming allocation in the descendent order of bag capacity

with agg as ( 
select bag.BAG_ID, bag.BAG_TYPE, bag.CAPACITY,
SUM(bag.CAPACITY) over (partition by bag.bag_type order by bag.capacity DESC) agg_capacity,
item_amount
from bag, item
where bag.bag_type = item.item_type
)
select 
    BAG_ID, BAG_TYPE, CAPACITY, 
    case when ITEM_AMOUNT >= AGG_CAPACITY then CAPACITY /* Full allocated */
    when ITEM_AMOUNT >= AGG_CAPACITY-CAPACITY then  ITEM_AMOUNT - (AGG_CAPACITY-CAPACITY) /* partly allocated */ 
    else 0 end /* not allocated */
    as allocated 
from agg
order by bag_type, capacity desc;

    BAG_ID BAG_TYPE   CAPACITY  ALLOCATED
   ------ -------- ---------- ----------
     1 A               500        500 
     2 A               300        300 
     3 A               100         50 
     4 B               200        200 
     5 B               100        100

Note that the order of the allocation is important if you want to minimize the waste capacity and finding an optimal allocation using different orders could be hard.

Nice solution, I hope I could have more accept vote but I choose @Gordon's solution because of the introduction to cumulative sum. — asinkxcoswt, Jul 15 '15 at 17:04

Oracle aggregation function to allocate amount

3 Answers3

Linked