Calculating moving average

Hi,

  I am trying to calculating the following with SQL:

     Stock    Date        Price
      X        5/1/2008    10
      X        5/2/2008    12
      X        5/3/2008    13
      Y        5/1/2008    20
      Y        5/2/2008    22
      Y        5/3/2008    23
      ..........

      It will be a month of data with stock, date, price as unique,

      Today's moving average is calculated based on previous days' moving average plus today's price. The sum will be divided by the number of days as in the following:

      For stock X:
      The moving average on 5/1/2008 will be 10
      The moving average on 5/2/2008 will be (10 + 12) / 2 = 11
      The moving average on 5/2/2008 will be (10 + 11 + 13) / 3 =

      The same for the stock Y:
      The moving average on 5/1/2008 will be 20
      The moving average on 5/2/2008 will be (20 + 22) / 2 = 21
      The moving average on 5/2/2008 will be (20 + 21 + 23) / 3 =

      I am trying to using windowing function to get the moving aveage but it doesn't seem to be working. Do I have to use PL/SQL to accomplish this?

      Thanks in advance.

      Nick Li Received on Sun May 18 2008 - 01:19:00 CDT