Re: Calculating moving average

On May 18, 11:32 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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.
>
> You may need an extra handling for NULL values - that may depend on your
> business logic.
>
> Best regards
>
> Maxim

Impressive! Models, dimensions, measures and rules, and not a factorial in sight - it looks like I have some more reading to do.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun May 18 2008 - 15:14:08 CDT