Re: SQL Problem about moving average

rene.nyffenegger_at_gmx.ch (Rene Nyffenegger) wrote in message news:<Xns90F57E7AD84CBgnuegischgnueg_at_130.133.1.4>...
> agichen_at_kimo.com (xman) wrote in
> <44fefe22.0108060212.3d9904fb_at_posting.google.com>:
>
> >Hello, theres
> > I have a question about getting Moving Average in Stock.
> > I have a table called cp ( ClosePrice ).
> > create table cp ( xday int, StockID char(4), LastTrade number(5,2))
> > (xday is the sequence)
> >
> > here are the sample data:
> >xday StockID LastTrade
> >==========================
> >1 2303 59.03
> >2 2303 47.51
> >3 2303 57.56
> >4 2303 85.43
> >5 2303 86.63
> >6 2303 78.84
> >7 2303 34.34
> >8 2303 20.54
> >9 2303 56.82
> >10 2303 58.04
> >11 2303 1.23
> >12 2303 82.98
> >
> >I want to get the result like
> >xday StockID LastTrade MA6 (AVG(ClosePrice) between xday-5 and
> >xday) ----------------------------------
> >1 2303 59.03 59.030000
> >2 2303 47.51 53.270000
> >3 2303 57.56 54.700000
> >4 2303 85.43 62.382500
> >5 2303 86.63 67.232000
> >6 2303 78.84 69.166666 ( day 1,2..6's avg)
> >7 2303 34.34 65.051666 ( day 2,3..7's avg)
> >8 2303 20.54 60.556666
> >9 2303 56.82 60.433333
> >10 2303 58.04 55.868333
> >11 2303 1.23 41.635000
> >12 2303 82.98 42.325000 ( day 7-12's avg )
> >
> >Can I write SQL statement to deal with this ??
> >
> >It seems doesn't support the syntax that MSSQL does
> >
> >select *, (select avg(LastTrade)
> > from cp as a
> > where a.xday between b.xday-5 and b.xday
> > and a.stockID=b.stockID ) as MA6
> > from cp b
> >
> >
> >Rgds,
> >Agi Chen
>
> SELECT
> LastTrade,
> xDay,
> to_char(avg(LastTrade)
> OVER (order by xDay rows between 5 preceding and current row),
> '99D99')
> as MA6 from cp
>
>
> Something like this should do. However, I don't know how trade id is
> supposed to influence the output.
>
> Rene

Thanks !!
Does OVER syntax supports begin with 8i ?? It's cool !!

[Quoted] If my oracle version doesn't support OVER syntax, how to do ???

Rgds,
Agi Chen Received on Tue Aug 07 2001 - 03:19:44 CEST