PostgreSQL - групиране по сума

djman

Registered
Имам 2 таблици:

[sql]CREATE TABLE Vehicle (
id INT,
max_weight REAL
);

CREATE TABLE Package (
id INT,
weight REAL
);
[/sql]

Как да се групират Packages така, че сумата от weight да е <= от max_weight? Ясно е, че такива групи може да има много, но тук няма значение коя - важното е да има поне един package във всяка група, за всеки Vehicle.

Целта е да се изкара списък [package_ids, sum(weight), max_weight]. package_ids може да е "STRING_AGG(id::varchar(16), ',')", но нататък не мога да го измисля. :roll:
 
Много интересна задача :?: Поиграх си малко да направя 1 рекурсивна заявка за комбинации:
[sql]WITH result AS (
WITH RECURSIVE t(id, weight) AS (
SELECT id, weight FROM Package
),
agg AS (
SELECT ('["' || t.id || '"]')::jsonb AS comb, id, weight
FROM t
UNION ALL
SELECT agg.comb || ('["' || t.id || '"]')::jsonb, t.id, agg.weight + t.weight
FROM agg, t
WHERE NOT agg.comb @> ('["' || t.id || '"]')::jsonb
--AND agg.weight + t.weight <= 100
)
SELECT comb, weight FROM agg ORDER BY weight, comb
)
SELECT v.id v_id, v.max_weight, comb p_ids, weight
FROM result r
INNER JOIN Vehicle v ON v.max_weight >= r.weight
ORDER BY v.max_weight DESC, r.weight DESC;[/sql]

Твърде много ми се спи, за да я докарам до край - надявам се да съм ти дал насока поне.
 
Ако разбирам правилно, в agg има безброй комбинации [(id1; 10), (id1, id2; 10+20)...] и от там избираме нужните с INNER JOIN накрая? Само че изпълнението на заявката не спира, дори с 10 packages & 5 vehicles. :D

От тук измислих следното:

[sql]WITH RECURSIVE t AS (
SELECT
id,
weight,
row_number() OVER (ORDER BY id) seqnum
FROM package
), cte(id, weight, total_weight, ids, seqnum, grp) AS (
SELECT
id,
weight,
weight as total_weight,
t.id::text,
1 as seqnum,
1 as grp
FROM t
WHERE seqnum = 1
UNION ALL
SELECT
t.id,
t.weight,
(CASE WHEN cte.total_weight + t.weight > 150 THEN t.weight ELSE cte.total_weight + t.weight END) as total_weight,
(CASE WHEN cte.total_weight + t.weight > 150 THEN t.id::text ELSE cte.ids || ',' || t.id::text END) as ids,
t.seqnum,
(CASE WHEN cte.total_weight + t.weight > 150 THEN cte.grp + 1 ELSE cte.grp END) as grp
FROM t JOIN cte ON cte.seqnum = t.seqnum - 1
)
SELECT grp, max(ids) ids, total_weight, v.id as vid, v.max_weight
FROM cte
INNER JOIN vehicle v ON v.max_weight >= total_weight
GROUP BY grp, total_weight, vid
ORDER BY total_weight DESC;[/sql]

Това дава кои packages могат да се натоварят в кой vehicle, но тук max_weight е статичен - 150 и не може да се вземе от vehicle. :roll:

Ако се махнат CASE WHEN и се оставят само сумите, то тогава резултата е само 1; 1,2; 1,2,3;... и съответните total_weights. А трябва да е 1,2,3; 4,5,6,7; 8,9.
 

Горе