使用以前的值计算字段

Question

我面临的问题涉及到计算字段的值，但使用前一行的值。在具有数据

ID Original Rate  Capital 
-- ------------------ ------- 
1 50      1 
2 50 + 1     1 
3 50 + 1 + 1    1 
4 50 + 1 + 1 + 2   2 
5 50 + 1 + 1 + 2 + 2  2 

表RATE的公式是：原始速率（N）=原始率（n-1）+资本

在原始的，我有1行与原始速率是50，我希望下一个值应该是原始利率+资本。

我也试过使用Lag Function，但它似乎不适用于计算字段。

请帮助我，如果你们对这种情况有什么想法？

谢谢你的时间！

---

更新为新案件：

+----+---------------+------+---------+-------+-------+-------+ 
| ID | Original Rate | Type | Capital | Rate1 | Rate2 | Rate3 | 
+----+---------------+------+---------+-------+-------+-------+ 
| 1 |   50 | 1 |  2 | 0.1 |  |  | 
| 2 |   50 | 1 |  2 | 0.1 |  |  | 
| 3 |   50 | 1 |  2 | 0.1 |  |  | 
| 4 |   50 | 2 |  1 |  | 0.2 |  | 
| 5 |   50 | 3 |  5 |  |  | 0.3 | 
+----+---------------+------+---------+-------+-------+-------+ 

New Rate (n) = New Rate (n-1) + Original Rate * IF(Type=1,Rate1*capital,IF(Type=2,Rate2*capital),Rate3*capital) 

我期待输出为如下最后一栏

+----+---------------+------+---------+-------+-------+-------+-----+ 
| ID | Original Rate | Type | Capital | Rate1 | Rate2 | Rate3 |  | 
+----+---------------+------+---------+-------+-------+-------+-----+ 
| 1 |   50 | 1 |  2 | 0.1 |  |  | 10 | 
| 2 |   50 | 1 |  2 | 0.1 |  |  | 20 | 
| 3 |   50 | 1 |  2 | 0.1 |  |  | 30 | 
| 4 |   50 | 2 |  4 |  | 0.2 |  | 70 | 
| 5 |   50 | 3 |  5 |  |  | 0.3 | 145 | 
+----+---------------+------+---------+-------+-------+-------+-----+ 

任何意见和建议，欢迎！

[更新17-APR-2017] 我实现了使用递归递归的萃取

WITH RATE_MASTER AS (ID, RATE) 
(
    SELECT 1, ORIGINALRATE FROM RATE WHERE ID = 1 

    UNION ALL 

    SELECT ID, 
    CASE 
     WHEN TYPE = 1 THEN RATE + Rate1*Capital 
     WHEN TYPE = 2 THEN RATE + Rate2*Capital 
     WHEN TYPE = 3 THEN RATE + Rate3*Capital 
    END AS RATE 

    FROM RATE, RATE_MASTER 
    WHERE RATE_MASTER.ID = RATE.ID - 1 
) 
SELECT * FROM RATE_MASTER; 

一切正常！

[关闭]

Answer 1

甲骨文设置：

CREATE TABLE rate (id, capital) AS 
    SELECT 1, 1 FROM DUAL UNION ALL 
    SELECT 2, 1 FROM DUAL UNION ALL 
    SELECT 3, 1 FROM DUAL UNION ALL 
    SELECT 4, 2 FROM DUAL UNION ALL 
    SELECT 5, 2 FROM DUAL; 

查询：

SELECT id, 
     50 + SUM(capital) OVER (ORDER BY id) 
      - FIRST_VALUE(capital) OVER (ORDER BY id) AS rate, 
     capital 
FROM rate; 

输出：

ID RATE CAPITAL 
-- ---- ------- 
1 50  1 
2 51  1 
3 52  1 
4 54  2 
5 56  2