How to select and order by grouping n rows with sum of them between a and b?
I have the list number like:
1,2,3,4,5,6,7,8,9,10,11,12...100
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 100
I need to order a group of each 5rows have 80 <= sum <= 100
1,12,22,23,31, 2,3,15,24,47, 5,11,17,26,37, ...
Inside:
sum(1,12,22,23,31) = 89 match the condition
sum(2,3,15,24,45) = 91 match the condition
sum(5,11,17,26,37) = 96 match the condition
...
Anyone can help me?
Your problem is impossible.
1 to 100.80 and 100.If you start with the largest number 100 then it will immediately fill a group and there will be no room for anything else so there cannot be 5 items in that group without violating the maximum size constraint.
If you want to:
n bins;Then you can define the data types:
CREATE TYPE int_list AS TABLE OF INT;
CREATE TYPE bin AS OBJECT(
id INT,
items int_list,
total INT,
CONSTRUCTOR FUNCTION bin(id IN INT) RETURN SELF AS RESULT,
MEMBER PROCEDURE add_item(item IN INT)
);
CREATE TYPE BODY bin AS
CONSTRUCTOR FUNCTION bin(id INT) RETURN SELF AS RESULT
IS
BEGIN
self.id := id;
self.items := INT_LIST();
self.total := 0;
RETURN;
END;
MEMBER PROCEDURE add_item(item IN INT)
IS
BEGIN
self.items.EXTEND;
self.items(self.items.COUNT) := item;
self.total := self.total + item;
END;
END;
/
CREATE TYPE bin_table AS TABLE OF bin;
Then the function:
CREATE FUNCTION bin_packing(
items IN INT_LIST,
num_bins IN PLS_INTEGER
) RETURN bin_table DETERMINISTIC
IS
sorted_items INT_LIST;
bins BIN_TABLE := BIN_TABLE();
current_bin BIN := NULL;
idx PLS_INTEGER;
BEGIN
SELECT COLUMN_VALUE
BULK COLLECT INTO sorted_items
FROM TABLE(items)
ORDER BY COLUMN_VALUE DESC;
bins.EXTEND(num_bins);
FOR i IN 1 .. num_bins LOOP
bins(i) := NEW bin(i);
END LOOP;
FOR i IN 1 .. sorted_items.COUNT LOOP
idx := 1;
bins(1).add_item(sorted_items(i));
current_bin := bins(1);
WHILE idx < num_bins AND current_bin.total > bins(idx + 1).total LOOP
bins(idx) := bins(idx+1);
idx := idx + 1;
END LOOP;
IF idx > 1 THEN
bins(idx) := current_bin;
END IF;
END LOOP;
RETURN bins;
END;
/
Then, if you want to pack the numbers 1 to 100 into 5 bins you can use the query:
SELECT id,
total,
(SELECT LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP ( ORDER BY COLUMN_VALUE)
FROM TABLE(b.items)
) As items
FROM bin_packing(
( SELECT CAST( COLLECT(LEVEL) AS int_list)
FROM DUAL
CONNECT BY LEVEL <= 100),
5
) b
Which outputs:
ID TOTAL ITEMS 1 1010 1,10,11,20,21,30,31,40,41,50,51,60,61,70,71,80,81,90,91,100 2 1010 2,9,12,19,22,29,32,39,42,49,52,59,62,69,72,79,82,89,92,99 3 1010 3,8,13,18,23,28,33,38,43,48,53,58,63,68,73,78,83,88,93,98 4 1010 4,7,14,17,24,27,34,37,44,47,54,57,64,67,74,77,84,87,94,97 5 1010 5,6,15,16,25,26,35,36,45,46,55,56,65,66,75,76,85,86,95,96
If you wanted the numbers 1 to 100 into 7 bins then you can change the number of bins:
SELECT id,
total,
(SELECT LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP ( ORDER BY COLUMN_VALUE)
FROM TABLE(b.items)
) As items
FROM bin_packing(
( SELECT CAST( COLLECT(LEVEL) AS int_list)
FROM DUAL
CONNECT BY LEVEL <= 100),
7
) b
Which outputs:
ID TOTAL ITEMS 3 721 5,14,19,28,33,42,47,56,61,70,75,84,89,98 4 721 6,13,20,27,34,41,48,55,62,69,76,83,90,97 5 721 7,12,21,26,35,40,49,54,63,68,77,82,91,96 6 721 8,11,22,25,36,39,50,53,64,67,78,81,92,95 7 721 9,10,23,24,37,38,51,52,65,66,79,80,93,94 2 722 1,4,15,18,29,32,43,46,57,60,71,74,85,88,99 1 723 2,3,16,17,30,31,44,45,58,59,72,73,86,87,100
(Note: as you can see, it is impossible to equally split the items into 7 bins as 7 is not a factor of the sum of the item values.)
If you want to split the items so that there are about 5 items in each bin then you can calculate the number of bins:
WITH items ( item ) AS (
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 100
)
SELECT id,
total,
(SELECT LISTAGG(COLUMN_VALUE, ',') WITHIN GROUP ( ORDER BY COLUMN_VALUE)
FROM TABLE(b.items)
) As items
FROM bin_packing(
( SELECT CAST( COLLECT(item) AS int_list) FROM items ),
( SELECT CEIL( COUNT(item)/5 ) FROM items )
) b
Which outputs:
ID TOTAL ITEMS 20 243 1,40,41,80,81 19 244 2,39,42,79,82 18 245 3,38,43,78,83 17 246 4,37,44,77,84 16 247 5,36,45,76,85 15 248 6,35,46,75,86 14 249 7,34,47,74,87 13 250 8,33,48,73,88 12 251 9,32,49,72,89 11 252 10,31,50,71,90 10 253 11,30,51,70,91 9 254 12,29,52,69,92 8 255 13,28,53,68,93 7 256 14,27,54,67,94 6 257 15,26,55,66,95 5 258 16,25,56,65,96 4 259 17,24,57,64,97 3 260 18,23,58,63,98 2 261 19,22,59,62,99 1 262 20,21,60,61,100
db<>fiddle here
Your problem is impossible.
But I have a solution if you don't want to cover all the values in the column.
If it is not helpful to you, may be helpful for others who have a similar problem like yours.
CREATE or REPLACE PACKAGE ffes_pkg AS
TYPE numtab is table of NUMBER;
FUNCTION FFES RETURN numtab PIPELINED ;
end;
CREATE or REPLACE PACKAGE BODY ffes_pkg AS
FUNCTION FFES RETURN numtab PIPELINED AS
n PLS_INTEGER;
flag BOOLEAN := FALSE ;
v_num ffes_pkg.numtab;
i INT := 1; j INT := 1; k INT := 1; l INT := 1; m INT := 1;
BEGIN
SELECT count(*) into n FROM NUMBERS;
SELECT num BULK COLLECT into v_num FROM NUMBERS ORDER BY num;
for q in 1..n LOOP
WHILE i <= (n - 4 )LOOP j := i +1;
WHILE j <= (n - 3 )LOOP k := j + 1;
WHILE k <= (n - 2 )LOOP l := k + 1;
WHILE l <= (n - 1 )LOOP m := l + 1;
WHILE m <= (n)LOOP
if( v_num(i) <> 0 AND v_num(j) <> 0 AND v_num(k) <> 0 AND v_num(l) <> 0 AND v_num(m) <> 0 ) then
if( (v_num(i) + v_num(j) + v_num(k) + v_num(l) + v_num(m) ) BETWEEN 80 AND 100) THEN
PIPE ROW (v_num(i)); v_num(i) := 0;
PIPE ROW (v_num(j)); v_num(j) := 0;
PIPE ROW (v_num(k)); v_num(k) := 0;
PIPE ROW (v_num(l)); v_num(l) := 0;
PIPE ROW (v_num(m)); v_num(m) := 0;
flag := TRUE ;
END IF;
END IF;
m := m + 1;
if (flag) THEN EXIT ; END IF; END LOOP;
l := l + 1;
if (flag) THEN EXIT ; END IF; END LOOP;
k := k + 1;
if (flag) THEN EXIT ; END IF; END LOOP;
j := j + 1;
if (flag) THEN EXIT ; END IF; END LOOP;
i := i + 1;
if (flag) THEN EXIT ; END IF; END LOOP;
i := 1;
flag := FALSE;
end LOOP ;
END;
END;
SELECT * FROM TABLE(ffes_pkg.FFES());
Time complexity is huge for this solution around O(n^6)