Minimum Sql Transfer Request

This can be solved with plain SQL using a helper table containing sections of integers. For this example, suppose this helper table has three columns: sections, rank, and number. For any given number, there will be rows of possible partitions, each with its own rank. If the number is 5, selecting all rows from this table where the number is 5, you will see:

partitions          rank        number
5                   1           5
4, 1                2           5
3, 2                2           5
3, 1, 1             3           5
2, 2, 1             3           5
2, 1, 1, 1          4           5
1, 1, 1, 1, 1       5           5

      

        
        
        
      

    

Rank is the number of sections used per string and this is important for the problem you presented as it allows us to choose the minimum translations.

For number 5, we have 7 lines representing the sections. For higher numbers, the returned strings will be much higher - the number 12 will contain 77 sections! - but at the scale that we work with databases, this auxiliary partition table is easy to query in numbers from 1 to 99, for example, in the above example. Higher numbers are a matter of scalability.

If you need instructions on how to create such a table, I'll be happy to provide you with it, but since this is a long solution, let it be possible to defer the creation of the auxiliary table for now.

Look at Store ID A that had a quantity to ship. Their values:

30
16
10
9
6
5
2 

      

        
        
        
      

    

For each number of stores, we can query the section support table and get different sections for that size and their rank. We can then create our own combination of sections. For example, 30 will give many lines, one of which will be:

partitions         rank         number
15,10,5            3            30

      

        
        
        
      

    

and 10, among many others:

partitions         rank         number
6,4                2            10

      

        
        
        
      

    

You can construct a Cartesian product of all possible candidates by cross-joining the results, and there are ascending sections for each row of that product, and the rank is the sum of the section ranks.

On the other hand, you have a store ID B that needs quantities. You are just doing the same exact treatment, which results in another rather large Cartesian product of the ranked ordered sections. Congratulations on being so far.

Now you only need to see the section lines where the Store ID B section collection is fully contained in the Store ID collection. This will greatly reduce the significant build to a few rows of potential gears. One of the lines from Store ID B (above) would be:

partitions                     rank
15,10,10,7,6,6,5,5,4,2         10

      

        
        
        
      

    

Since it appears in both store ID A and store ID B. in store ID A, it will combine with:

30 = 15,10,5     rank 3
16 = 10,6        rank 2
10 = 6,4         rank 2
9  = 7,2         rank 2
6  = 5,1         rank 2
5  = 5           rank 1
2  = 2           rank 1

      

        
        
        
      

    

gives you the line:

partitions                     rank
15,10,10,7,6,6,5,5,5,4,2,2,1   13

      

        
        
        
      

    

The last step is to select the row with the lowest rank in store ID B. This will be the minimum number of transfers and you can output it just like you did above.

BONUS to get this value: if you want to know if we can completely destroy the Store ID. All inventory (not execute store ID B), change the containment relation: make sure the section collection A is completely contained within the section collection B. To see the minimum translations to move exactly every item from A to B, performing B and exhausting A, find identical sections in both collections.

And some actual SQL to simulate the algorithm, at least in part:

-- this function handles the sorting. It not necessary but it help make the result look better.
WITH
FUNCTION SORT_PARTITIONS(p_id IN VARCHAR2) RETURN VARCHAR2 IS
result VARCHAR2(100);
BEGIN   
  select rtrim(XMLAGG(XMLELEMENT(E,str||',')).EXTRACT('//text()'),',') into result  
from (
with temp as  (
   select p_id num from dual       
 )
SELECT   trim(regexp_substr(str, '[^,]+', 1, level)) str
FROM (SELECT num str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by to_number(str)
);
return result;
END;
-- this function handles containment - when we want to fulfil store ID B, and not necessarily deplete store ID A, or visa-versa.
FUNCTION PARTITION_CONTAINED(seta_partition IN VARCHAR2, setb_partition IN VARCHAR2) RETURN NUMBER IS
result NUMBER;
BEGIN
 with seta as 
(select str, count(str) cnt from (
SELECT trim(regexp_substr(str, '[^,]+', 1, level)) str
FROM (SELECT num str FROM (select SETA_PARTITION num from dual)) t
CONNECT BY instr(str, ',', 1, level - 1) > 0)
group by str),
setb as 
(select str, count(str) cnt from (
SELECT trim(regexp_substr(str, '[^,]+', 1, level)) str
FROM (SELECT num str FROM (select SETB_PARTITION num from dual)) t
CONNECT BY instr(str, ',', 1, level - 1) > 0)
group by str),
lenab as (select count(1) strab from seta, setb where seta.str=setb.str and seta.cnt>=setb.cnt),
lenb as (select count(1) strb from setb)
select strb-strab into result from lenb,lenab;
RETURN result;
END;
-- this store_a simply represents a small Cartesian product of two stores from the stores ID A table - one with quantity 5, the other with quantity 4. I found this was easier to set up. 
store_a as (select SORT_PARTITIONS(n1||','||n2) partitions_sending, rank1+rank2 rank_sending from (select num_partitions n1, rank rank1 from n_partitions where num=5),(select num_partitions n2, rank rank2 from n_partitions where num=4)),

-- this store_b represents the stores ID B Cartesian product of partitions, again for simplicity. The receiving quantities are 3, 3 and 3.
store_b as (select SORT_PARTITIONS(n1||','||n2||','||n3) partitions_receive, rank1+rank2+rank3 rank_receive from (select num_partitions n1, rank rank1 from n_partitions where num=3),(select num_partitions n2, rank rank2 from n_partitions where num=3),(select num_partitions n3, rank rank3 from n_partitions where num=3))

-- and finally, the filtering that provides all possible transfers - with both "=" (which works for deplete and fulfil) and "partition_contained" which allows for fulfil or deplete. You can choose to leave both or just use partition contained, as it is more flexible.
SELECT * from store_a, store_b where store_a.partitions_sending=store_b.partitions_receive or partition_contained(store_a.partitions_sending,store_b.partitions_receive)=0 order by store_b.rank_receive, store_a.rank_sending asc;