On Mon, Nov 22, 2004 at 05:50:05PM -0000, Mark Rogers wrote:
Brett Parker wrote:
Yup, that's how that's done... Here's the *MAGIC* ;)
create view testwithrunningtotal as select date,description, amount,(select sum(amount) from test as test2 where test2.date <= test.date) as balance from test order by date;
select * from testwithrunningtotal;
What sort of performance would you expect from this sort of query in a large table? If I'm reading it right it isn't really generating a running total, so much as calculating a complete sum for each transaction. Assuming that several years of records will be kept, this could get quite slow?
With a large dataset, I'd expect it to start getting quite slow. But, ofcourse, you could split the accounts in to monthly tables to assist in the process, and write a slightly more sick and twisted query to generate the results, not sure how off the top of my head, though ;)
[That's a genuine question, not a snide remark - I'm interested to know whether PostgreSQL can optimise this somehow, or is just fast enough to do it, or if I'm reading it wrong in the first place?]
You're reading it right, unfortunately, I could probably work out a better query, it's just that one rolled off the top of my head quickest :)
To Chris: is there a reason for generating this at the query level, rather than the application level? If you are still playing with PHP this should be trivial to calculate after you have the data from PostgreSQL. Two queries (one to calculate the total of all transactions before the start date of the report, and another to bring back the transactions for the report itself) seems to me that it ought to be quicker as the number of stored transactions grows.
Still, I like the ingenuity of Brett's query (but as I do most of my db work in mySQL I have to look up to real database programmers with awe :-)
If you forget about creating the view, and just use it as a select, it should even work in mysql. Of course, mysql should be getting views RSN :).
Cheers,