我有以下数据，由A值组成，按MM（月）排序。
以类似电子表格的方式将B列计算为GREATEST(current value of A + previous value of B, 0)。
如何使用SQL查询计算B？
- 我试着使用分析函数，但没能成功
- 我知道有示范条款；我发现了一个类似的例子，但我不知道从哪里开始
我使用的是Oracle10g，因此不能使用递归查询。
这是我的测试数据：
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
我提出了一个用户定义的聚合函数
create or replace type tsum1 as object
(
total number,
static function ODCIAggregateInitialize(nctx IN OUT tsum1 )
return number,
member function ODCIAggregateIterate(self IN OUT tsum1 ,
value IN number )
return number,
member function ODCIAggregateTerminate(self IN tsum1,
retVal OUT number,
flags IN number)
return number,
member function ODCIAggregateMerge(self IN OUT tsum1,
ctx2 IN tsum1)
return number
)
/
create or replace type body tsum1
is
static function ODCIAggregateInitialize(nctx IN OUT tsum1)
return number
is
begin
nctx := tsum1(0);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT tsum1,
value IN number )
return number
is
begin
self.total := self.total + value;
if (self.total < 0) then
self.total := 0;
end if;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN tsum1,
retVal OUT number,
flags IN number)
return number
is
begin
retVal := self.total;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT tsum1,
ctx2 IN tsum1)
return number
is
begin
self.total := self.total + ctx2.total;
return ODCIConst.Success;
end;
end;
/
CREATE OR REPLACE FUNCTION sum1(input number)
RETURN number
PARALLEL_ENABLE AGGREGATE USING tsum1;
/
这是查询
with T1 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
, sum1(a) over(order by mm) as b
from t1
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
with sample_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,
greatest(nvl(a,0) + lag(a,1,0) over (order by mm), 0) as b
from sample_data;
然而，它确实不产生这条线：
2012-05-01 | 900 | 900
因为它在那一行中计算出900-20000，而零比结果更大。如果使用abs函数来消除计算中的负值，则可以"修复"此问题。
很抱歉，如果这偏离了主题，给出了问题的Oracle版本，但我们现在可以使用SQL:2016 MATCH_RECOGNIZE子句：
select * from t
match_recognize(
order by mm
measures case classifier() when 'POS' then sum(a) else 0 end as b
all rows per match
pattern (pos* neg{0,1})
define pos as sum(a) > 0
);