CALCULATING MOVING AVERAGE?

[Calivari]

HOW BOUT A BOMBSHELL ... CALCULATING 3 DAY Simple moving average problem . ... I'm currently having to use perl to loop through every close and insert the moving avg into a csv and import it into the data model .. any other ideas?

select
a1.symbol, a1.date, a1.open, a1.close, a2.high, a1.low, a1.vol,
avg(a2.close)
from
data_feed as a1,
data_feed as a2
where
(a2.date between '2003-04-30' and '2003-05-02' and a2.symbol='ABX' )
and (a1.symbol='ABX' and a1.date='2003-05-05')
group by a2.symbol

RESULT
symbol date open close high low vol 3_DMA
ABX 2003-05-05 15.45 15.87 15.23 15.45 1606100 15.340000



OR

Select
A1.symbol, A1.date, A1.open, A1.close, A2.high, A1.low, A1.vol,
((A2.CLOSE+A3.CLOSE+A4.CLOSE)/3)
from
data_feed as A1,
data_feed as A2,
data_feed as A3,
data_feed as A4
Where
A1.symbol='ABX'
and A2.symbol='ABX'
and A3.symbol='ABX'
and A4.symbol='ABX'
and A1.date = '2003-05-05'
and A2.date = '2003-05-02'
and A3.date = '2003-05-01'
and A4.date = '2003-04-30'

RESULT
symbol date open close high low vol 3_DMA
ABX 2003-05-05 15.45 15.87 15.73 15.45 1606100 15.3400


Seems simple enough, but let's compound the problem by saying now give me ALL
3 day moving averages for all closes from 1997-01-01 to 2003-05-05.

symbol date open close high low vol 3_DMA
ABX 1997-01-01 15.45 15.87 15.73 15.45 1606100 0
....
ABX 2003-05-05 15.45 15.87 15.73 15.45 1606100 15.3400

 

[dnoeth]

Which RDBMS?
Teradata, Oracle and IBM already implemented SQL:1999 OLAP functions:

select
datecol,
avg(close) over (partition by symbol
order by datecol
rows 2 preceding)
...

Dieter