Home » SQL & PL/SQL » SQL & PL/SQL » calculate on calculated field
calculate on calculated field [message #422695] Fri, 18 September 2009 06:59 Go to next message
lfotopoulos
Messages: 4
Registered: September 2009
Junior Member
Hello,
I have the below table:

month, revenue
1, 15
2, 12
3, 14
4, 13
5, 14
6, 15

and i want to create the following:

month, revenue_forecast
1, 15
2, 12
3, 14
4, 13
5, 14
6, 15
7, =moving average of 3 previous months = (13+14+15)/3 = 14
8, =moving average of 3 previous months = (14+15+14)/3= 14.3 --based on previous calculated month and the 5,6 month
9, =moving average of 3 previous months = (15+14+14.3)/3 = 14.43 --based on previous 2 calculated months and the 6 month
10,....and so on
11, ....
12, ...

Is it possible with sql?
Thanks
Re: calculate on calculated field [message #422696 is a reply to message #422695] Fri, 18 September 2009 07:02 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Probably.

Have a look at analytical functions, lead / lag in particular.
Re: calculate on calculated field [message #422697 is a reply to message #422695] Fri, 18 September 2009 07:21 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Is it fixed that values of 6 months are given and you have to calculate for next six months?

If yes, then you can do it using MODEL clause.

regards,
Delna
Re: calculate on calculated field [message #422698 is a reply to message #422695] Fri, 18 September 2009 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Fri, 18 September 2009 07:27]

Report message to a moderator

Re: calculate on calculated field [message #422702 is a reply to message #422695] Fri, 18 September 2009 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
drop table t ;
create table t (m integer, r integer);
insert into t values(1, 15);
insert into t values(2, 12);
insert into t values(3, 14);
insert into t values(4, 13);
insert into t values(5, 14);
insert into t values(6, 15);
commit;

SQL> select * from t order by 1;
         M          R
---------- ----------
         1         15
         2         12
         3         14
         4         13
         5         14
         6         15

6 rows selected.

SQL> select m "MONTH", round(r,2) "REVENUE"
  2  from (select * from t order by m)
  3  model 
  4    dimension by (m)
  5    measures (r)
  6    rules upsert iterate(12)
  7      (r[iteration_number+1]=nvl(r[cv()],avg(r)[m between cv()-3 and cv()-1]))
  8  order by m
  9  /
     MONTH    REVENUE
---------- ----------
         1         15
         2         12
         3         14
         4         13
         5         14
         6         15
         7         14
         8      14.33
         9      14.44
        10      14.26
        11      14.35
        12      14.35

12 rows selected.

Regards
Michel
Re: calculate on calculated field [message #422711 is a reply to message #422702] Fri, 18 September 2009 08:21 Go to previous messageGo to next message
lfotopoulos
Messages: 4
Registered: September 2009
Junior Member
Geeee, Thank you! Smile
It works.

Re: calculate on calculated field [message #423338 is a reply to message #422702] Wed, 23 September 2009 08:23 Go to previous messageGo to next message
lfotopoulos
Messages: 4
Registered: September 2009
Junior Member
If i have another column-dimension like:

drop table t ;
create table t (m integer, d varchar2(50), r integer);
insert into t values(1, 'product1', 15);
insert into t values(1, 'product2', 12);
insert into t values(2, 'product1', 14);
insert into t values(2, 'product2', 13);
insert into t values(3, 'product1', 14);
insert into t values(3, 'product2', 15);
insert into t values(4, 'product1', 15);
insert into t values(4, 'product2', 12);
insert into t values(5, 'product1', 14);
insert into t values(5, 'product2', 13);
insert into t values(6, 'product1', 14);
insert into t values(6, 'product2', 15);
commit;



If i do this

select m "MONTH", d "PRODUCT", round(r,2) "REVENUE"
    from (select * from t order by m)
    model 
      dimension by (m,d)
      measures (r)
      rules upsert iterate(12)
        (r[iteration_number+1]=nvl(r[cv()],avg(r)[m between cv()-3 and cv()-1]))
    order by m


i get an "ORA-00947: not enough values error".

Any ideas how to change the rule?
I want the 3 Months Average per per product.
Thanks again.

PS: The product column values may change, i don't know the actual values when i build the query, they must be "dynamic" not "static" (if you know what i mean). In other words, i cannot reference a "cell" like r[1,'product1']

[Updated on: Wed, 23 September 2009 08:28]

Report message to a moderator

Re: calculate on calculated field [message #423342 is a reply to message #423338] Wed, 23 September 2009 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> select m "MONTH", d "PRODUCT", round(r,2) "REVENUE"
  2  from (select * from t order by d, m)
  3  model 
  4    dimension by (m,d)
  5    measures (r)
  6    rules upsert all iterate(12)
  7      (r[iteration_number+1, any] = 
  8         nvl(r[cv(),cv()],avg(r)[m between cv()-3 and cv()-1,cv()]))
  9  order by d, m
 10  /
     MONTH PRODUCT       REVENUE
---------- ---------- ----------
         1 product1           15
         2 product1           14
         3 product1           14
         4 product1           15
         5 product1           14
         6 product1           14
         7 product1        14.33
         8 product1        14.11
         9 product1        14.15
        10 product1         14.2
        11 product1        14.15
        12 product1        14.17
         1 product2           12
         2 product2           13
         3 product2           15
         4 product2           12
         5 product2           13
         6 product2           15
         7 product2        13.33
         8 product2        13.78
         9 product2        14.04
        10 product2        13.72
        11 product2        13.84
        12 product2        13.87

24 rows selected.

You have to read about MODEL clause if you want to use it, you cannot infer what are the syntax and keywords to use without studying the subject.

Regards
Michel
icon14.gif  Re: calculate on calculated field [message #423420 is a reply to message #423342] Thu, 24 September 2009 01:48 Go to previous message
lfotopoulos
Messages: 4
Registered: September 2009
Junior Member
Thanks Michel,
you're right!!

I was just looking for a "quick win" solution.

Thanks again! Smile
Previous Topic: Complicated SQL to Calculate Prices
Next Topic: Upload Word document; view contents of Word document
Goto Forum:
  


Current Time: Sat Feb 08 07:54:24 CST 2025