CODEWITHSUNDEEP
sqloracle-database
DXBKing
DXBKing

Reputation: 315

equally distribute objects using SQL

I have the data as follows

|Fruit   |Numbers|
|Apples  |     50|
|Banana  |     30|
|Oranges |     20|
|Grapes  |    100|
|Peach   |     30|
|Kiwi    |     70|

I need to re-arrange them 3 bags in such a way that I have 3 bags of equal(or almost) number of fruits using NTILE. Could you please help?

Output should be like

|Bag_id|Contents        |Count
|Bag 1 | Apples,Oranges,Peaches|100|
|Bag 2 | Kiwi,Banana    |100|
|Bag 3 |Grapes|100|

Upvotes: 1

Views: 189

Answers (3)

DXBKing
DXBKing

Reputation: 315

I had ~300 fruits, so based on @MatBailie, I found the soln.

Thank you all!!

SELECT Wm_concat(fruit),
       bag
FROM   (SELECT i.,
               CASE
                 WHEN item <= 10 THEN item
                 ELSE 10 - item
               END AS bag
        FROM   (SELECT b.,
                       Row_number()
                         OVER (
                           partition BY batch
                           ORDER BY cnt) AS item
                FROM   (SELECT t.*,
                               Ntile(50)
                                 OVER (
                                   ORDER BY cnt) AS batch
                        FROM   tmp t) b) i)
GROUP  BY bag; 

Upvotes: 0

MatBailie
MatBailie

Reputation: 86765

SELECT
  i.*,
  CASE WHEN item <= 3 THEN item ELSE 7 - item END  AS bag
FROM
(
  SELECT
    b.*,
    ROW_NUMBER() OVER (PARTITION BY batch, ORDER BY numbers) AS item
  FROM
  (
    SELECT
      t.*,
      NTILE(6) OVER (ORDER BY numbers) AS batch
    FROM
      your_table t
  )
    b
)
  i

I'm on my phone, so I won't add the bit done by Gordon to concatenate the items in to a single row.

But this implements a Very simple bagging algorithm.

It happens to be Fairly good when you have 6 items to put in 3 bags.

It happens to be Awful when you have 4 items to put in 3 bags.

And it doesn't really cope with large amount of small numbers and a small amount of large numbers.

There are many algorithms for you to Google, and pick you preference.

But it's an example, using NTILE...

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

If you just want two fruits in each basket (ignoring the numbers column), then you can use ntile():

select 'Bag ' || tercile,
       listagg(Fruit, ',') over (within group order by Fruit) as fruits
from (select t.*,
             ntile(3) over (order by numbers) as tercile
      from t
     ) t
group by tercile;

As with your sample results, the sum of numbers is not even approximately equal across the baskets.

Upvotes: 0

Related Questions