Обсуждение: 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 balancesbut 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 thisis 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 findaccounting errors. Thanks, marc
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of telenieko@gmail.com > Sent: Tuesday, October 30, 2012 7:34 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] 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.
Hi, On Tuesday, October 30, 2012 8:12:25 PM UTC+1, "David Johnston" wrote: > 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. Will do, thanks for the tip. > You sample data is simplistic to the point of being unusable. (...) > 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. I am trying to find accounting errors in the style of: credits/debits put into wrong accounts (ie: payments to providerswithout bills, etc), payrolls with typing errors... So if I know, for example, that all payroll accounts go to 0 two days after getting credited (it's "Average Balance Life"would be 2 days) I want to see which accounts do not comply ie: part or the whole balance is not debited on the twodays. Reading the LAG function it seems it may help me to catch the most simple cases (ie: those with sequential credit / debitmovements). > Do you have some other identifier (i.e., control) attached to these amounts that would aid in choosing the endpoints? Nope. Anyway I just thought that ANOTHER way to look at it would be "If account balance was going to 0 and now it is not withouthaving reached 0, alert" this seems like a far simpler approach that would detect the same kind of errors I'm lookingfor (I'd have to restart set the balance to 0 on every alert, etc). I'll work on that one which smells like some ofthis window function stuff. Thanks, marc