Oracle SQL 分析查询 - 递归电子表格式运行总计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12243488/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL Analytic query - recursive spreadsheet-like running total
提问by Danilo Piazzalunga
I have the following data, composed of the Avalue, ordered by MM(month).
我有以下数据，由A值组成，按MM（月）排序。
The Bcolumn is computed as GREATEST(current value of A + previous value of B, 0)in a spreadsheet-like fashion.
该B列GREATEST(current value of A + previous value of B, 0)以类似电子表格的方式计算。
How can I compute Busing a SQL Query?
如何B使用 SQL 查询进行计算？
- 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.
我使用的是 Oracle 10g，因此我不能使用递归查询。
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;
回答by Lukas Eder
So let's unleash the MODELclause (a device whose mystery is only exceeded by its power) on this problem:
因此，让我们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)
)
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
回答by Nick Krasnov
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
回答by a_horse_with_no_name
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 notproduce 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 absfunction to get rid of the negative value in the computation.
因为它在该行计算 900 - 20000，而零比结果大。如果您使用该abs函数来消除计算中的负值，则可以“修复”该问题。
回答by Stew Ashton
Sorry if this is off topic, given the Oracle version of the question, but we can now use the SQL:2016 MATCH_RECOGNIZE clause:
抱歉，如果这是题外话，鉴于问题的 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
);
