Re: Calculating moving average

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>
>
> Kind regards
>
> robert

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.

Best regards

Maxim Received on Mon May 19 2008 - 03:24:44 CDT