Home » SQL & PL/SQL » SQL & PL/SQL » calculate on calculated field
calculate on calculated field [message #422695] |
Fri, 18 September 2009 06:59  |
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 #422698 is a reply to message #422695] |
Fri, 18 September 2009 07:26   |
 |
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   |
 |
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 #423338 is a reply to message #422702] |
Wed, 23 September 2009 08:23   |
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   |
 |
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
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:54:24 CST 2025
|