I need to do a cumulative select on postgres, and I got it to work
with some extreme SQL query, but I was wondering if it could be done
more easily without having to drop to SQL but with SQLAlchemy
statements.
Let's say I have a table with stock transactions like this:
Transaction
id
id_product
price
quantity
And it is filled like this:
1 1 12 10
2 1 13 5
3 1 12 3
4 1 11 6
5 1 10 5
Now at moment X my stock is 13 and I want to know the costs for each
product in my stock. So I add a cumulative column to select on and
expect to get the last three rows back as their cumulative total is <=
as my stock:
CUM
1 1 12 10 29
2 1 13 5 19
3 1 12 3 14 this
4 1 11 6 11 this
5 1 10 5 5 and this...
Extra info:
This is the query I currently use to get the transaction ID and offset
back:
SELECT
f1.id,
(
SELECT
coalesce(sum(quantity), 0)
FROM transaction f2
WHERE f2.id>=f1.id
AND f2.id_item = %s
) - %s as offset
FROM
transaction f1
AND f1.id_item = %s
AND %s <= (
SELECT
coalesce(sum(quantity), 0)
FROM transaction f2
WHERE f2.id>=f1.id
AND f2.id_item = %s
)
ORDER BY f1.id DESC LIMIT 1
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
