Re: Calculating moving average

On May 19, 10:24 am, Maxim <mdeme..._at_gmail.com> wrote:
> On 19 Mai, 09:47, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
>
>
> > 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>
>
> Robert, the solution with analytics was already provided by Charles -
> but it calculates moving average in most common sense. OP however has
> another definition of moving average which is - sum of all averages
> for previous days with price for current day divided by number of
> days - as i stated before, with analytics only it will be probably
> require more code to write than with model clause to get the result
> suitable for OP requirement.

Darn, I suspected I was missing something. Somehow I thought both formulas would yield same results and I also managed to not identify Charles solution - probably because of the missing "rows unbounded preceding". Thank you again!

Kind regards

robert Received on Mon May 19 2008 - 06:41:28 CDT