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