Oracle SQL:使用上一行计算值 [英] Oracle SQL: Calculate value using previous row

问题描述

我有一个包含两列的表:Cur_value和Difference

I have a table with two columns: Cur_value and Difference

       Cur_value        Difference
(-3)       3    
(-2)       4    
(-1)       5    
(1)                        1
(2)                       -2
(3)                        3
(4)     
(5)     
(6)     
(7)     

现在我要计算从行(1)到行(7)的Cur_value的值，以便

Now I want to caculate value of Cur_value from row (1) to row (7) so that

cur_value(t)= cur_value(t-3)+ dif(t).

cur_value(t) = cur_value(t-3) + dif(t).

这意味着我的结果是:

           Value        Difference
(-3)           3    
(-2)           4    
(-1)           5    
(1)        3 + 1           1
(2)        4 - 2          -2
(3)        5 + 3           3
(4)    3 + 1 + 2           2
(5)    4 - 2 + 1           1
(6)    5 + 3 - 2          -2
(7) 3 + 1 + 2 - 1         -1

. 我的问题是:如何通过单个Oracle SQL语句执行此操作?

. My question is: how to do this action by single Oracle SQL statements?

推荐答案

请注意，数据库表没有任何固有顺序.假设您可以使用一些ID或其他列来定义行的顺序，则可以使用

Note that database table don't have any intrinsic order. Assuming you have some id or other column you can use to define the rows' order, you could use the lag function to get the t-3 record:

SELECT cur_value, 
       difference, 
       LAG(cur_value, 3, 0) OVER (ORDER BY id) + difference AS new_cur_value
FROM   my_table

这篇关于Oracle SQL:使用上一行计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！