- CREATE TABLE MODEL_TEST
- (
- DOMAINS VARCHAR2(20),
- NAME VARCHAR2(20),
- YEAR NUMBER,
- INPUTS NUMBER,
- INCREAMENTS NUMBER
- )
- insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)
- values ('chchina', 'apple', 2000, 100, 150);
- insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)
- values ('chchina', 'banana', 2001, 200, 250);
- insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)
- values ('chchina', 'orange', 2002, 300, 450);
- insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)
- values ('hongkong', 'apple', 2000, 100, 150);
- insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)
- values ('hongkng', 'banana', 2001, 200, 250);
- insert into MODEL_TEST (DOMAINS, NAME, YEAR, INPUTS, INCREAMENTS)
- values ('hongkong', 'orange', 2002, 300, 450);
select domains,
name,year,
inputs,
increaments,
total
from modeL_test t1
model return updated rows
partition by (domains,name)
dimension by (year)
measures (0 total,inputs,increaments)
rules automatic order (
total [year] = nvl(total [cv(year)-1],0)+ inputs[cv(year)]+increaments[cv(year)]
)