- PostgreSQL - групиране по сума
WT форуми -> PHP MySQL ASP.NET -> PostgreSQL - групиране по сума
Създайте нова тема Напишете отговор 
Автор Съобщение
djman
Активен
Активен

Регистриран на: 12/09/2009 10:07 am

Support: 105
Bonus: 200
Мнения: 2778
Мнение 01/11/2018 10:23 pm     PostgreSQL - групиране по сума Отговорете с цитат


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

SQL code:
CREATE TABLE Vehicle (
id INT,
max_weight REAL
);

CREATE TABLE Package (
id INT,
weight REAL
);


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

Целта е да се изкара списък [package_ids, sum(weight), max_weight]. package_ids може да е "STRING_AGG(id::varchar(16), ',')", но нататък не мога да го измисля. Rolling Eyes
Върнете се в началото
Вижте профила на потребителя Изпратете лично съобщение
raiden
Активен
Активен

Регистриран на: 08/12/2006 10:13 am

Support: 189
Bonus: 464
Мнения: 2181
Мнение 01/11/2018 11:55 pm      Отговорете с цитат


Много интересна задача Question Поиграх си малко да направя 1 рекурсивна заявка за комбинации:
SQL code:
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;


Твърде много ми се спи, за да я докарам до край - надявам се да съм ти дал насока поне.
Върнете се в началото
Вижте профила на потребителя Изпратете лично съобщение
djman
Активен
Активен

Регистриран на: 12/09/2009 10:07 am

Support: 105
Bonus: 200
Мнения: 2778
Мнение 02/11/2018 11:23 am      Отговорете с цитат


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

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

SQL code:
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;


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

Ако се махнат CASE WHEN и се оставят само сумите, то тогава резултата е само 1; 1,2; 1,2,3;... и съответните total_weights. А трябва да е 1,2,3; 4,5,6,7; 8,9.
Върнете се в началото
Вижте профила на потребителя Изпратете лично съобщение
Покажи мнения от преди:    
Създайте нова тема   Напишете отговор    web-tourist.net Форуми -> PHP MySQL ASP.NET Часовете са според зоната GMT + 2 Часа
Страница 1 от 1


 
Идете на:  
Не Можете да пускате нови теми
Не Можете да отговаряте на темите
Не Можете да променяте съобщенията си
Не Можете да изтривате съобщенията си
Не Можете да гласувате в анкети