Search Postgresql Archives

Recursive select / updates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Hi,
I have a problem where i need to calculate totals in a table that are based on previous values and calculations.
I am currently doing that in a function which works fine but was wondering if there is a more elegant or efficient way to do this.

Here is an example table, ordered by row no.
The total is based on the previous rows. Note that the difference of a values does not always need to be 1

 row  |  a |   b   | total  |
------+----+-------+------- +
 1    |  3 |    11 |      0 |  
 2    |  5 |    34 |     22 |  
 3    |  6 |    64 |     67 |  
 4    |  7 |   525 |    176 |
 5    |  8 |  9994 |    810 |
 6    |  9 | 26216 |  11438 |
 7    | 10 |  3363 |  48282 |
 8    | 11 |  1147 |  88489 |
 9    | 12 |  1037 | 129843 |
 10   | 13 |   200 | 172234 |


Total Row 2
(a2-a1)*b1 
i.e. (5-3)*11 = 22

Total Row 3 
(a3-a2)*b2 + (a3-a1)*b1  
i.e (6-5)*34 + (6-3)*11 = 34+33=67 

Total Row 4 
(a4-a3)*b3 + (a4-a2)*b2 + (a4-a1)*b1  
i.e (7-6)*64 + (7-5)*34 + (7-3)*11 = 64+68+44=176 

Has anyone a clever solution for that problem?

Thanks for any suggestions.

Alex

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux