Oracle SQL Analytic query - recursive spreadsheet-

问题:

I have the following data, composed of the A value, ordered by MM (month).

The B column is computed as GREATEST(current value of A + previous value of B, 0) in a spreadsheet-like fashion.

How can I compute B using a SQL Query?

• I tried using Analytic Functions, but I was unable to succeed.
• I know there is the Model Clause; I found a similar example, but I don't know where to begin.

I am using Oracle 10g, therefore I cannot use recursive queries.

---

Here is my test data:

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

And here is the "table definition":

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;

回答1:

So let's unleash the MODEL clause (a device whose mystery is only exceeded by its power) on this problem:

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)
      )

The above yields:

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

回答2:

I came up with a user-defined aggregate function

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;
/

Here is the query

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

回答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;

It does however not produce this line:

2012-05-01 |    900 |  900

because it calculates 900 - 20000 in that row, and zero is bigger than the result of that. You can "fix" that if you use the abs function to get rid of the negative value in the computation.

回答4:

Sorry if this is off topic, given the Oracle version of the question, but we can now use the SQL:2016 MATCH_RECOGNIZE clause:

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
);