DROP TABLE batches PURGE;
CREATE TABLE batches (batch_id INTEGER PRIMARY KEY, avail_qty INTEGER NOT NULL);
INSERT INTO batches VALUES (1, 1);
INSERT INTO batches VALUES (2, 1);
INSERT INTO batches VALUES (3, 1);
INSERT INTO batches VALUES (4, 1);
INSERT INTO batches VALUES (5, 1);
INSERT INTO batches VALUES (6, 1);
INSERT INTO batches VALUES (7, 1);
INSERT INTO batches VALUES (8, 2);
INSERT INTO batches VALUES (9, 3);
INSERT INTO batches VALUES (10, 4);
INSERT INTO batches VALUES (11, 4);
INSERT INTO batches VALUES (12, 4);
INSERT INTO batches VALUES (13, 5);
INSERT INTO batches VALUES (14, 5);
INSERT INTO batches VALUES (15, 13);
INSERT INTO batches VALUES (16, 20);
INSERT INTO batches VALUES (17, 20);
COMMIT;

SQL> DEFINE groups=3
SQL> /
     GROUP QUANTITIES           TOTAL
---------- --------------- ----------
         1 20,4,3,1,1              29
         2 20,4,2,1,1,1            29
         3 13,5,5,4,1              28

SQL> DEFINE groups=4
SQL> /

     GROUP QUANTITIES           TOTAL
---------- --------------- ----------
         1 20,1,1                  22
         2 20,1,1                  22
         3 13,4,3,1                21
         4 5,5,4,4,2,1             21

SQL> DEFINE groups=5
SQL> /

     GROUP QUANTITIES           TOTAL
---------- --------------- ----------
         1 20                      20
         2 20                      20
         3 13,1,1,1                16
         4 5,4,4,1,1               15
         5 5,4,3,2,1               15

SELECT bucket_name AS GROUPS,
trim(BOTH ','FROM regexp_replace(XMLAgg(
     XMLElement(X,avail_qty) ORDER BY avail_qty desc),'<X>|</X><X>|</X>',',')) AS QUANTITIES, 
SUM(avail_qty) TOTAL        
FROM 
(SELECT batch_id , avail_qty , bucket_name, rn, bucket_1, bucket_2, bucket_3 
 FROM
 (SELECT batch_id , avail_qty , count(*) OVER ( ) counter,
         ROW_NUMBER() OVER (ORDER BY avail_qty  desc) rn FROM batches)
 MODEL 
 DIMENSION BY (rn)
 MEASURES (batch_id , avail_qty , 0 it, counter, 
 CAST(NULL AS NUMBER) bucket_name, CAST(NULL AS NUMBER) min_tmp,
 CAST(NULL AS NUMBER) bucket_1,    CAST(NULL AS NUMBER) bucket_2,  
 CAST(NULL AS NUMBER) bucket_3,    CAST(NULL AS NUMBER) pbucket_1,   
 CAST(NULL AS NUMBER) pbucket_2,   CAST(NULL AS NUMBER) pbucket_3 
 )
 RULES ITERATE (100000)
 UNTIL (ITERATION_NUMBER>= counter[1])
 (
 pbucket_1[2] = CASE WHEN it[ITERATION_NUMBER] = 0 THEN 0 END ,
 pbucket_2[2] = CASE WHEN it[ITERATION_NUMBER] = 0 THEN 0.00001 END ,
 pbucket_3[2] = CASE WHEN it[ITERATION_NUMBER] = 0 THEN 0.00002 END ,
 min_tmp[1] = least(sum(pbucket_1)[ANY], sum(pbucket_2)[ANY], sum(pbucket_3)[ANY]) ,
 bucket_1[ITERATION_NUMBER] = CASE WHEN sum(pbucket_1)[ANY] = min_tmp[1] 
                                   THEN avail_qty [ITERATION_NUMBER]  END,
 bucket_2[ITERATION_NUMBER] = CASE WHEN sum(pbucket_2)[ANY] =  min_tmp[1]
                                   THEN avail_qty [ITERATION_NUMBER]  END, 
 bucket_3[ITERATION_NUMBER] = CASE WHEN sum(pbucket_3)[ANY] =  min_tmp[1] 
                                   THEN avail_qty [ITERATION_NUMBER]  END, 
 bucket_name[ITERATION_NUMBER] =CASE WHEN sum(pbucket_1)[ANY] = min_tmp[1] THEN 1
                                     WHEN sum(pbucket_2)[ANY] = min_tmp[1] THEN 2
                                     WHEN sum(pbucket_3)[ANY] = min_tmp[1] THEN 3  
                                END,                         
pbucket_1[1] = sum(bucket_1)[ANY],
pbucket_2[1] = sum(bucket_2)[ANY],
pbucket_3[1] = sum(bucket_3)[ANY],
it[ITERATION_NUMBER] = ITERATION_NUMBER  
 )                  
) 
WHERE batch_id  IS NOT NULL
GROUP BY bucket_name; 




   GROUPS QUANTITIES      TOTAL                            
--------- --------------- ----------                            
1          20,4,3,1,1      29                            
2          20,4,2,1,1,1    29                            
3          13,5,5,4,1,1    29   
  
SQL> spool off;