SQL model clause example: forecast

create table sales (
  ctry  varchar2(20),
  prod  varchar2(20),
  yr    number  (4),
  qty   number  (5)
);

insert into sales values ('Argentina', 'Table', 2002,  220);
insert into sales values ('Argentina', 'Table', 2003,  248);
insert into sales values ('Brazil',    'Table', 2002,  515);
insert into sales values ('Brazil',    'Table', 2003,  602);
insert into sales values ('Chile',     'Table', 2002,   29);
insert into sales values ('Chile',     'Table', 2003,   42);

insert into sales values ('Argentina', 'Chair', 2002,  115);
insert into sales values ('Argentina', 'Chair', 2003,  134);
insert into sales values ('Brazil',    'Chair', 2002,  297);
insert into sales values ('Brazil',    'Chair', 2003,  368);
insert into sales values ('Chile',     'Chair', 2002,   14);
insert into sales values ('Chile',     'Chair', 2003,   22);

insert into sales values ('Argentina', 'Phone', 2002, 1244);
insert into sales values ('Argentina', 'Phone', 2003, 1590);
insert into sales values ('Brazil',    'Phone', 2002, 8190);
insert into sales values ('Brazil',    'Phone', 2003,10844);
insert into sales values ('Chile',     'Phone', 2002, 3103);
insert into sales values ('Chile',     'Phone', 2003, 4965);

select 
  ctry, prod, yr, qty 
from 
  sales
model
  partition by (prod, ctry)
  dimension by (yr)
  measures     (qty)
  rules (
    qty[2004] = qty[2003] * (qty[2003]/qty[2002])
  )
order by yr,ctry,prod;

CTRY                 PROD                         YR        QTY
-------------------- -------------------- ---------- ----------
Argentina            Chair                      2002        115
Argentina            Phone                      2002       1244
Argentina            Table                      2002        220
Brazil               Chair                      2002        297
Brazil               Phone                      2002       8190
Brazil               Table                      2002        515
Chile                Chair                      2002         14
Chile                Phone                      2002       3103
Chile                Table                      2002         29
Argentina            Chair                      2003        134
Argentina            Phone                      2003       1590
Argentina            Table                      2003        248
Brazil               Chair                      2003        368
Brazil               Phone                      2003      10844
Brazil               Table                      2003        602
Chile                Chair                      2003         22
Chile                Phone                      2003       4965
Chile                Table                      2003         42
Argentina            Chair                      2004  156.13913
Argentina            Phone                      2004 2032.23473
Argentina            Table                      2004 279.563636
Brazil               Chair                      2004 455.973064
Brazil               Phone                      2004 14358.0386
Brazil               Table                      2004 703.697087
Chile                Chair                      2004 34.5714286
Chile                Phone                      2004 7944.32001
Chile                Table                      2004 60.8275862