我有以下数据中，组成A值，通过有序的MM （月）。
所述B柱计算为GREATEST(current value of A + previous value of B, 0)在电子表格状的方式。
我如何计算B使用SQL查询？
- 我试着用解析函数，但我无法成功。
- 我知道有该型号条款 ; 我发现了一个类似的例子 ，但我不知道从哪里开始。
我使用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;
Answer 1:
因此，让我们发挥的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
Answer 2:
我想出了一个用户定义的聚集函数
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
Answer 3:
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功能，摆脱在计算负值的。
Answer 4:
很抱歉，如果这是题外话，鉴于问题的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
);
文章来源: Oracle SQL Analytic query - recursive spreadsheet-like running total
