Oracle aggregation function to allocate the 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 entry in table T1 represents a bag and its capacity, here I have 5 bags. I want to write SQL that selects the elements in table T2 in each bag of the same type, i.e. the result should look 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 

So I find some kind of aggregation function, let it allocate() , which can create the allocated_amount column as above. I have an assumption that if it exists, it can use it as

 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 temporary table and a PL / SQL loop to compute, but I hope I can do this with simple SQL.

Answer 1

You are looking for a cumulative amount. Something like that:

 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; 

Answer 2

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 

Answer 3

assuming allocation in descending order of total 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 

Please note that the distribution order is important if you want to minimize the amount of waste and find the optimal distribution using different orders can be difficult.