Evenly distribute amounts

[pvzshark]

Good day all.

I have no clue where to begin. I have a php script that imports csv from local file and then imports to mysql. this all works 100%. i have 4 other tables.

lets call main csv import table - csv1. fields amount.

then i have 4 other tables

tb1
tb2
tb3
tb4

now comes the part i am struggling with.

lets say i get 9 amounts.

100
12
15
22
32
45
80
36
32. Now i need to instert them evenly into the different tables.

example
tb1 should get - 100 total 100
tb2 should get - 80,12 total 92
tb3 should get - 15,32,45 total 92
tb4 should get - 22,36,32 total 90.

so in essence all 4 rows sum values should be close to even. U have tried average, but that does not work, I trien inserting from higest to lowest. but then i do not get them evenly matched.

regards

[m_hutley]

So i’m going to spitball ideas. There’s probably a mathematical formula for this, but heres how it plays out in my head, but I already know my plan has a couple of flaws.

Well, your target is the average. You will need to keep track of the number of tables remaining.
If there exists one or more values that equal this average, remove them from your array; they go into a table each.
Now it gets more difficult.

Phase 2:
Find the total of all combinations of 2 values. If any of them equal the average, remove them from the array, and put those numbers into a table.
Eventually, there will be either no more sets of two-value pairings, or no more values in the array.
Repeat this process with 3 (if 3 or more elements remain), then 4, etc… until the selection set is of size TotalLength-X, where X is the number of remaining tables to fill.

Phase 3: (AKA the ‘it didnt work’ phase)
Repeat the process above, but store the results of each combination in a multidimensional array, keyed by the absolute value of the difference between the average and total (the distance from the average), and holding an array of combinations.
Key Sort the array.
Take an element from the first element of the array; assign it to a table.
Remove from that first key any other element that uses a component of the one you selected.
If the first key is not empty and there are more tables to be filled, remove another element from the key’s array and repeat.
Repeat phase 3 until the tables are all filled.

So what’s the flaw?
The flaw is that this algorithm will get as close as it can to the average at each step - but that doesn’t mean it gets the best values for all the steps as a whole. You might end up with a set that generates 100-95-94-91, or a set that generates 100-99-98-20.

What you could do is add a phase 4, which combines unique combinations of sets from the Phase 3 table, and finds sets that are closest to X*Mean, where X is the number of remaining tables.

Also keep in mind that this is designed for small sets of numbers. The number of combinations of sets grows exponentially-stupid (EDIT: Actually factorially-stupid.) the more numbers exist in your set.

[pvzshark]

My example was small, i am talking about 1000 entries

[pvzshark]

i was thinking of select order by amount limit 1, then take the first one and pop it in the first table and update first line with imported. then select amount order by where importeed <> ‘1’ then also select the 4 tables and the one with the lowest sum wil get the next entry, then repeat repeat repeat. but it sounds long anf heavy on processing

[m_hutley]

yeah, do you know how many combinations of elements you would have between 1000 entries?
C(1000,2)+C(1000,3)+C(1000,4)…+C(1000,997) = …
Please hold. Math processing.
elevator music
1x10^301. I don’t even know what this number would be called.
Yeah. Your PHP engine’s not holding that many combinations in memory. When I said small, I meant SMALL.

If the set is fairly well distributed, then assigning-to-lowest should roughly work. Whatever process is applied will be ‘long and heavy’… because the thing you’re trying to do IS long and heavy.

[TheRedDevil]

I do not really understand the business case, i.e. where the numbers come from etc.

However what you describe that it should just try to even out the numbers, to make them as identical as possible it is not that hard to do.

Setup a main class that manage the distribution, to this class in the constructor you pass a long an list of value objects (one for each “table”), then afterwards, you parse the csv file (or partially parse it all depending on the size), and keep feeding the numbers to a method in the main class.

The main class will for each number fed, check which of the value objects should receive it to ensure they are as equal as possible, when it know which to pass it along to it send it to the value object, which store it.

Finally when the distribution is completed, you get the list of value objects back from the class (this is only required if you use immutable value objects, else its just for ease of understanding the code, as they are passed by reference).

For each value object, you now get the data and store it in the database (or any other method you use for storage).

Note. For this case, I would not use immutable value objects as it would not really be required.

Edit:
In the end you can also have a method that if possible even out the value objects even more, by checking the current numbers, and then what would be required to even it out more. When that is known, you ask the value objects if they have those numbers stored, and if you can switch them between the value objects.