lundi 4 janvier 2016

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

Have table like below viz. table1. What I'm trying to do is to compute Col3 values for each row based on col1 & col2 values of current and previous row in SQL.

table1

Id   Time             Col1    Col2   Col3
1    09:37.43.755       1      0      0.0    #=> col3 row1 start value = 0
2    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
3    09:37.44.755       10     2      0.0    #=> (row2.col2-row1.col2)/((row2.col2-row1.col2)+(row2.col1-row1.col1)) => (2-2)/((2-2) + (10-5)) => 0/5 = 0.0
4    09:37.45.255       10     2      0.0    # same as 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.

Aucun commentaire:

Enregistrer un commentaire