> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of telenieko@xxxxxxxxx > Sent: Tuesday, October 30, 2012 7:34 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: Average Balance "life" > > Hi there, > > Not sure if this is the right place to post this question: > > I'm tryting to find a way to do the following without going row by row on the > client program doing the calculations: > > I've got a "movements" table (from an accounting program) for which I've > already done monthly balances and running balances but now I need to > calculate the "life" of the balances. > > Ie, account 1 is 0 and gets 1000 on day 1, -1000 on day 3. That would make a > life of two days for this balance. Note this is completely diferent to "average > balance". > > In words that would be "Every time money goes into this account it takes an > average of X days to go out". > > Any ideas on how could I do this calculation inside PostgreSQL to avoid > transfering all rows to the client? > > In case you are curious, I want to do this calculation to see which accounts > have "balance lifes" to long in order to find accounting errors. > > Thanks, > marc Start learning about Window functions/clauses: http://www.postgresql.org/docs/9.2/interactive/tutorial-window.html The lag function over a window ordered by date will allow you to calculate how many days since the last transaction. You sample data is simplistic to the point of being unusable. "Balance Life" may have a concrete definition in your domain but the example and descriptions are lacking. If indeed you mean "days between transactions" then a direct window function application will solve the problem easily. But given that most accounts have numerous debits and credits flowing through them the logic by which you choose the endpoints is unclear but fundamental to the solution you seek. At first blush you seem to need to decide whether you want to deal with FIFO, LIFO, or specific-lots. Do you have some other identifier (i.e., control) attached to these amounts that would aid in choosing the endpoints? Regardless, most likely any pure SQL solution is going to require the use of Window functions so at least learn what those can do and then if you still need help reply back with more details. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
