Any function to calculate exponential moving average in oracle ?
437716May 30 2010 — edited Jun 1 2010I am able to calculate MA200 easily.
My current query looks something like that
with base as ( select ROW_NUMBER() OVER (partition by STOCK_CODE ORDER BY px_Date) rowno , a.* from price a)
,base2 as(
select
/*MA200*/
case when rowno >= 200 then avg(close) OVER (partition by STOCK_CODE ORDER BY px_Date ROWS BETWEEN 200 PRECEDING AND CURRENT ROW )
else null end as MA200,
/*MA100*/
case when rowno >= 100 then avg(close) OVER (partition by STOCK_CODE ORDER BY px_Date ROWS BETWEEN 100 PRECEDING AND CURRENT ROW )
else null end as MA100,
/*MA50*/
case when rowno >= 50 then avg(close) OVER (partition by STOCK_CODE ORDER BY px_Date ROWS BETWEEN 50 PRECEDING AND CURRENT ROW )
else null end as MA50,
b.* from base b
)select * from base2