compute current row col value based on values in current and previous row

Question

Have table viz. table1 which has Time, Col1 and Col2 fields. What I'm trying to do is to create a new table table2 that includes the computed Col3 values for each row based on Col1 & Col2 values of current and previous row in SQL of table1. The required format of table2 is as below.

table2

   Time             Col1    Col2   Col3
   09:37.43.755       1      0      0.0    #=> col3 row1 start value = 0
   09:37.44.255       5      2      0.3333 #=> (row2.col2-row1.col2)/((row2.col2-row1.col2)+(row2.col1-row1.col1)) => (2-0)/((2-0) + (5-1)) => 2/6 = 0.3333
   09:37.44.755       10     2      0.0    #=> (row3.col2-row2.col2)/((row3.col2-row2.col2)+(row3.col1-row2.col1)) => (2-2)/((2-2) + (10-5)) => 0/5 = 0.0
   09:37.45.255       10     2      0.0    # similar to row2,3 but a div by 0 case => (2-2)/( (2-2) + (10-10)) = 0/0 => this should be mapped to 0 if the numerator is 0.

Answer 1

The tool that will serve you the best here is probably the LAG windowed aggregate function. Still, there are probably a few different ways of doing this. Here's one example, using LAG and a subquery. The windowed aggregate functions are incredibly useful, so I'd say get to know them!

select * from table1 order by time, col1;

     TIME     | COL1 | COL2
--------------+------+------
 09:37:43.755 |    1 |    0
 09:37:44.255 |    5 |    2
 09:37:44.755 |   10 |    2
 09:37:45.255 |   10 |    2
(4 rows)


select time, col1, col2,
   coalesce ( case
      when (
            col2-lag_col2
         )
         + (col1-lag_col1) = 0 then 0
      else ((col2-lag_col2)*1.0/((col2-lag_col2)+(col1-lag_col1)))::NUMERIC(5,4)
   END , 0) col3
from (
      select time, col1, col2,
         lag(col1) over (order by time, col1) lag_col1,
         lag(col2) over (order by time, col1) lag_col2
      from table1
   )
   foo;

     TIME     | COL1 | COL2 |  COL3
--------------+------+------+--------
 09:37:43.755 |    1 |    0 | 0.0000
 09:37:44.255 |    5 |    2 | 0.3333
 09:37:44.755 |   10 |    2 | 0.0000
 09:37:45.255 |   10 |    2 | 0.0000
(4 rows)