Re: How to distribute quantity if same product is in multiple rows
От | Andrus Moor |
---|---|
Тема | Re: How to distribute quantity if same product is in multiple rows |
Дата | |
Msg-id | FE7C1097D95B4CC1A1491B57279F5E6B@andrusnotebook обсуждение исходный текст |
Ответ на | How to distribute quantity if same product is in multiple rows ("Andrus" <kobruleht2@hot.ee>) |
Список | pgsql-general |
Tim, Thank you. >>It can be done in SQL: "SUM(kogus) OVER (PARTITION BY toode >>ORDER BY ID) - kogus" (*1) will give you the running sum of >>the product up to that row. You can then subtract that value >>from the delivered quantity to calculate the delivered quan- >>tity for the current row. I tried to get list of undelivered items using script below. Second row value (22) is incorrect (it seems to be is cumulative sum but must be undelivered quantity for this row). How to fix this so that every row contains correct undelivered quantity ? Andrus. -- Order details CREATE TEMP TABLE tellrid ( id serial primary key, toode char(20), -- product id kogus numeric(12,5) ) -- ordered quantity on commit drop; insert into tellrid (toode,kogus) values ('PRODUCT1', 10 ); insert into tellrid (toode,kogus) values ('PRODUCT1', 20 ); -- Delivery details CREATE TEMP TABLE rid ( id serial primary key, toode char(20), -- product id kogus numeric(12,5) ) -- delivered quantity on commit drop; insert into rid (toode,kogus) values ('PRODUCT1', 8 ); select tellrid.id, max(tellrid.kogus) as ordered, GREATEST( 0, SUM(MAX(tellrid.kogus) ) OVER (PARTITION BY MAX(tellrid.toode) ORDER BY tellrid.ID) - COALESCE( SUM(rid.kogus),0) ) as not_delivered from tellrid LEFT JOIN rid USING (toode) GROUP BY 1 Observed: id ordered not_delivered 1 10.00000 2.00000 2 20.00000 22.00000 Expected: id ordered not_delivered 1 10.00000 2.00000 2 20.00000 20.00000
В списке pgsql-general по дате отправления: