So you want to assign the quantities in multiples of the item's volume as evenly as possible between the buckets?
To see how much you want to assign to each bucket, you can do the rounding to this unit with this formula:
round ( avg ( item quantities ) / volume ) * volume
There are some challenges to this scenario.
First up, this is a form of bin-packing problem. In general these are
hard problems, meaning there are no known solutions that are guaranteed to be both fast and correct.
Next you need to keep track of how much you've assigned from other buckets. This means using recursive with or the model clause. These can be tricky to write and understand. Both are likely to take a long time to process large data sets too.
So while I usually advocate SQL approaches, this is one case where procedural PL/SQL is likely to be both easier to write and faster to execute.
FinaIly I know you're on 12.1, but the new loop constructs in 21c make the code much more compact. So I couldn't resist using them ;) Read more about these at:
https://blogs.oracle.com/plsql-and-ebr/better-loops-and-qualified-expressions-array-constructors-in-plsql Here's a sketch of the algorithm I've used:
- Get all the buckets for an item, calculating the target quantity using the formula above
- Iterate through the buckets in priority order, skipping over any which already have sufficient
- Have an inner loop that works backwards through the buckets
- Assign the amount to the outer bucket if the inner bucket either has
- Lower priority
- Excess quantity
create table priorities (bucket, priority) as
select 'A', 1 from dual union all
select 'B', 2 from dual union all
select 'C', 3 from dual union all
select 'D', 4 from dual union all
select 'E', 5 from dual union all
select 'F', 6 from dual union all
select 'G', 7 from dual union all
select 'H', 8 from dual ;
create table volumes (item, quantity) as
select 'P1', 8 from dual union all
select 'P2', 7 from dual ;
create table data (bucket, item, present_qty) as
select 'A', 'P1', 6 from dual union all
select 'B', 'P1', 9 from dual union all
select 'C', 'P1', 8 from dual union all
select 'D', 'P1', 1 from dual union all
select 'F', 'P1', 0 from dual union all
select 'G', 'P1', 8 from dual union all
select 'H', 'P1', 72 from dual union all
select 'A', 'P2', 3 from dual union all
select 'B', 'P2', 5 from dual union all
select 'C', 'P2', 7 from dual union all
select 'D', 'P2', 9 from dual union all
select 'E', 'P2', 1 from dual union all
select 'F', 'P2', 4 from dual union all
select 'H', 'P2', 4 from dual ;
create or replace procedure fill_buckets ( fill_item varchar2 ) is
cursor bucket_cur is
select bucket, present_qty,
round (
avg ( present_qty ) over (
partition by item
) / quantity
) * quantity as quantity,
null source
from priorities
join data
using ( bucket )
join volumes
using ( item )
where item = fill_item
order by priority;
type bucket_arr
is table of bucket_cur%rowtype
index by pls_integer;
bucket_recs bucket_arr;
num_buckets pls_integer;
excess pls_integer;
begin
open bucket_cur;
fetch bucket_cur
bulk collect into bucket_recs;
close bucket_cur;
num_buckets := bucket_recs.count;
for i, v in pairs of bucket_recs
/* Only consider buckets under capacity */
when bucket_recs ( i ).present_qty < bucket_recs ( i ).quantity
loop
for j in reverse 1 .. num_buckets
/* Only attempt different buckets
with lower priority or excess quantity */
when i <> j and (
i < j or
bucket_recs ( j ).present_qty > bucket_recs ( i ).quantity
)
loop
if i < j then
excess := least (
bucket_recs ( i ).quantity - bucket_recs ( i ).present_qty,
bucket_recs ( j ).present_qty
);
else
excess := least (
bucket_recs ( j ).present_qty - bucket_recs ( j ).quantity,
bucket_recs ( i ).quantity - bucket_recs ( i ).present_qty
);
end if;
continue when excess <= 0;
bucket_recs (i).present_qty := bucket_recs (i).present_qty + excess;
bucket_recs (j).present_qty := bucket_recs (j).present_qty - excess;
bucket_recs (i).source := bucket_recs (i).source || ',' || bucket_recs (j).bucket || '-' || excess;
end loop;
end loop;
for i, v in pairs of bucket_recs loop
dbms_output.put_line (
v.bucket || '-' || v.present_qty || ' taken from ' || v.source
);
end loop;
end;
/
exec fill_buckets ( 'P1' );
A-16 taken from ,H-10
B-16 taken from ,H-7
C-16 taken from ,H-8
D-16 taken from ,H-15
F-16 taken from ,H-16
G-16 taken from ,H-8
H-8 taken from
exec fill_buckets ( 'P2' );
A-7 taken from ,H-4
B-7 taken from ,F-2
C-7 taken from
D-7 taken from
E-5 taken from ,F-2,D-2
F-0 taken from
H-0 taken from