Re: SQL Problem about moving average

agichen_at_kimo.com (xman) wrote in
<44fefe22.0108061719.2eed426e_at_posting.google.com>:

>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 !!

I believe it started with 8.1.7. But I am not being sure on that.

>If my oracle version doesn't support OVER syntax,
>how to do ???

install a newer version... SCNR

-- 
Recherchen im Schweizerischen Handelsamtsblatt:
http://www.adp-gmbh.ch/SwissCompanies/Search.php3