Re: SQL Problem about moving average

agichen_at_kimo.com (xman) wrote in
[Quoted] <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

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