Re: Calculating moving average
Date: Mon, 19 May 2008 00:47:43 -0700 (PDT)
Message-ID: <82c1e0a7-164f-4f78-a3a3-83c459df1c17@b64g2000hsa.googlegroups.com>
On May 18, 5:32 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> nickli2..._at_gmail.com schrieb:
>
>
>
> > Hi,
>
> > I am trying to calculating the following with SQL:
>
> > Stock Date Price
> > X 5/1/2008 10
> > X 5/2/2008 12
> > X 5/3/2008 13
> > Y 5/1/2008 20
> > Y 5/2/2008 22
> > Y 5/3/2008 23
> > ..........
>
> > It will be a month of data with stock, date, price as unique,
> > with thousands of symbols.
>
> > Today's moving average is calculated based on previous days'
> > moving average plus today's price. The sum will be divided by the
> > number of days as in the following:
>
> > For stock X:
> > The moving average on 5/1/2008 will be 10
> > The moving average on 5/2/2008 will be (10 + 12) / 2 = 11
> > The moving average on 5/2/2008 will be (10 + 11 + 13) / 3 =
> > 11.33
>
> > The same for the stock Y:
> > The moving average on 5/1/2008 will be 20
> > The moving average on 5/2/2008 will be (20 + 22) / 2 = 21
> > The moving average on 5/2/2008 will be (20 + 21 + 23) / 3 =
> > 21.33
>
> > I am trying to using windowing function to get the moving aveage
> > but it doesn't seem to be working. Do I have to use PL/SQL to
> > accomplish this?
>
> > Thanks in advance.
>
> > Nick Li
>
> As Charles already shown, the key point for your problem is recursion -
> you need for every row ( ordered by date ) previously calculated values
> to calculate the current one. In my opinion, to solve tasks of recursive
> nature only with analytical functions might be a good challenge and in
> general case probably not always doable. However, if you are on the
> somewhat recent version of oracle, then the solution may be trivial (and
> very efficient in terms of resources)
>
> SQL> with t as (
> 2 select 'X' Stock,to_date('05.01.2008','mm.dd.yyyy') Dt,10 Price
> from dual union all
> 3 select 'X',to_date('05.02.2008','mm.dd.yyyy'),12 from dual union all
> 4 select 'X',to_date('05.03.2008','mm.dd.yyyy'),13 from dual union all
> 5 select 'Y',to_date('05.01.2008','mm.dd.yyyy'),20 from dual union all
> 6 select 'Y',to_date('05.02.2008','mm.dd.yyyy'),22 from dual union all
> 7 select 'Y',to_date('05.03.2008','mm.dd.yyyy'),23 from dual
> 8 )
> 9 -- End test data
> 10 select Stock,dt,Price,av
> 11 from t
> 12 model
> 13 partition by (stock)
> 14 dimension by (row_number() over(partition by stock order by dt) n)
> 15 measures(price,dt,0 av,row_number() over(partition by stock order
> by dt) n_m )
> 16 rules
> 17 (
> 18 av[any] order by n=(sum(av)[any] + price[cv()])/ n_m[cv()]
> 19 )
> 20 ;
>
> S DT PRICE AV
> - ---------- ---------- ----------
> Y 05/01/2008 20 20
> Y 05/02/2008 22 21
> Y 05/03/2008 23 21.3333333
> X 05/01/2008 10 10
> X 05/02/2008 12 11
> X 05/03/2008 13 11.3333333
>
> 6 rows selected.
Thanks for that! I'll have to digest this. In the meantime, can you tell us why you did not do this, which seems much simpler?
SQL> with t as (
2 select 'X' Stock,to_date('05.01.2008','mm.dd.yyyy') Dt,10 Price
from dual union all
3 select 'X',to_date('05.02.2008','mm.dd.yyyy'),12 from dual
union all
4 select 'X',to_date('05.03.2008','mm.dd.yyyy'),13 from dual
union all
5 select 'Y',to_date('05.01.2008','mm.dd.yyyy'),20 from dual
union all
6 select 'Y',to_date('05.02.2008','mm.dd.yyyy'),21 from dual
union all
7 select 'Y',to_date('05.03.2008','mm.dd.yyyy'),23 from dual
8 )
9 -- End test data
10 select Stock,dt,Price,avg(price) over (partition by Stock order
by dt rows unbounded preceding) av
11 from t
12 order by Stock, dt;
S DT PRICE AV
- --------- ---------- ---------- X 01-MAY-08 10 10 X 02-MAY-08 12 11 X 03-MAY-08 13 11.6666667 Y 01-MAY-08 20 20 Y 02-MAY-08 21 20.5 Y 03-MAY-08 23 21.3333333
6 rows selected.
SQL> Kind regards
robert Received on Mon May 19 2008 - 02:47:43 CDT