Oracle SQL 分析查询 - 类似递归电子表格的运行总计

我有以下数据，由A值，排序依据MM（月）。

The B列计算为GREATEST(current value of A + previous value of B, 0)以类似电子表格的方式。

我怎样才能计算B使用 SQL 查询？

• 我尝试使用分析函数，但未能成功。
• 我知道有示范条款 http://docs.oracle.com/cd/B19306_01/server.102/b14223/sqlmodel.htm;我发现类似的例子 http://www.orafaq.com/forum/mv/msg/180678/551890/156078/#msg_551890，但我不知道从哪里开始。

我使用的是 Oracle 10g，因此无法使用递归查询。

这是我的测试数据：

MM         | A      | B
-----------+--------+------
2012-01-01 |    800 |  800
2012-02-01 |   1900 | 2700
2012-03-01 |   1750 | 4450
2012-04-01 | -20000 |    0
2012-05-01 |    900 |  900
2012-06-01 |   3900 | 4800
2012-07-01 |  -2600 | 2200
2012-08-01 |  -2600 |    0
2012-09-01 |   2100 | 2100
2012-10-01 |  -2400 |    0
2012-11-01 |   1100 | 1100
2012-12-01 |   1300 | 2400

这是“表定义”：

select t.* from (
  select date'2012-01-01' as mm, 800 as a from dual union all
  select date'2012-02-01' as mm, 1900 as a from dual union all
  select date'2012-03-01' as mm, 1750 as a from dual union all
  select date'2012-04-01' as mm, -20000 as a from dual union all
  select date'2012-05-01' as mm, 900 as a from dual union all
  select date'2012-06-01' as mm, 3900 as a from dual union all
  select date'2012-07-01' as mm, -2600 as a from dual union all
  select date'2012-08-01' as mm, -2600 as a from dual union all
  select date'2012-09-01' as mm, 2100 as a from dual union all
  select date'2012-10-01' as mm, -2400 as a from dual union all
  select date'2012-11-01' as mm, 1100 as a from dual union all
  select date'2012-12-01' as mm, 1300 as a from dual
) t;

那么让我们释放MODEL关于这个问题的条款（一种其神秘性仅因其功率而被超越的装置）：

with data as (
  select date'2012-01-01' as mm,    800 as a from dual union all
  select date'2012-02-01' as mm,   1900 as a from dual union all
  select date'2012-03-01' as mm,   1750 as a from dual union all
  select date'2012-04-01' as mm, -20000 as a from dual union all
  select date'2012-05-01' as mm,    900 as a from dual union all
  select date'2012-06-01' as mm,   3900 as a from dual union all
  select date'2012-07-01' as mm,  -2600 as a from dual union all
  select date'2012-08-01' as mm,  -2600 as a from dual union all
  select date'2012-09-01' as mm,   2100 as a from dual union all
  select date'2012-10-01' as mm,  -2400 as a from dual union all
  select date'2012-11-01' as mm,   1100 as a from dual union all
  select date'2012-12-01' as mm,   1300 as a from dual
)
select mm, a, b
from (
  -- Add a dummy value for b, making it available to the MODEL clause
  select mm, a, 0 b
  from data
)
      -- Generate a ROW_NUMBER() dimension, in order to access rows by RN
model dimension by (row_number() over (order by mm) rn)
      -- Spreadsheet values / measures involved in calculations are mm, a, b
      measures (mm, a, b)
      -- A single rule will do. Any value of B should be calculated according to
      -- GREATEST([previous value of B] + [current value of A], 0)
      rules (
        b[any] = greatest(nvl(b[cv(rn) - 1], 0) + a[cv(rn)], 0)
      )

上面的结果是：

MM              A     B
01.01.2012    800   800
01.02.2012   1900  2700
01.03.2012   1750  4450
01.04.2012 -20000     0
01.05.2012    900   900
01.06.2012   3900  4800
01.07.2012  -2600  2200
01.08.2012  -2600     0
01.09.2012   2100  2100
01.10.2012  -2400     0
01.11.2012   1100  1100
01.12.2012   1300  2400