Re: Calculating moving average

On May 18, 2:19 am, nickli2..._at_gmail.com wrote:
> 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,
> with thousands of symbols.
>
> 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 =
> 11.33
>
> 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 =
> 21.33
>
> 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

It appears that the AVG analytical function with a PARTITION BY and ORDER BY clause will provide a moving average solution to the problem, but not the solution that you are looking for. In the future, please include the DDL and DML to set up a test case for your problem.

Test case DDL and DML:
CREATE TABLE T1(
  STOCK VARCHAR2(5),
  STOCK_DATE DATE,
  PRICE NUMBER(10,2));

INSERT INTO T1 VALUES('X',TO_DATE('05/01/2008','MM/DD/YYYY'),10);
INSERT INTO T1 VALUES('X',TO_DATE('05/02/2008','MM/DD/YYYY'),12);
INSERT INTO T1 VALUES('X',TO_DATE('05/03/2008','MM/DD/YYYY'),13);
INSERT INTO T1 VALUES('Y',TO_DATE('05/01/2008','MM/DD/YYYY'),20);
INSERT INTO T1 VALUES('Y',TO_DATE('05/02/2008','MM/DD/YYYY'),22);
INSERT INTO T1 VALUES('Y',TO_DATE('05/03/2008','MM/DD/YYYY'),23);

SELECT
  STOCK,
  STOCK_DATE,
  AVG(PRICE) OVER (PARTITION BY STOCK ORDER BY STOCK_DATE) AVG_PRICE FROM
  T1;

STOCK STOCK_DAT AVG_PRICE

----- --------- ----------
X     01-MAY-08         10
X     02-MAY-08         11
X     03-MAY-08 11.6666667
Y     01-MAY-08         20
Y     02-MAY-08         21
Y     03-MAY-08 21.6666667

You will note that the last average for X and Y are different than what you expected. Let's see if we can mathematically calculate your rolling average:
X 10 = (10 + 0) / 1
X 11 = (12 + (10 + 0) / 1) / 2
X 11.333 = (13 + (10 + 0) / 1 + (12 + (10 + 0) / 1) / 2) / 3

Converting the numbers into variables, with P1 as the first price, P2 as the second price, P3 as the third price, etc. Xa3 = (P3 + (P1 + 0) / 1 + (P2 + (P1 + 0) / 1) / 2) / 3 Simplifying the above, which seems to imply that at Xa3, P1 has a greater impact on the average than either P2 or P3: Xa3 = P3/3 + P1/3 + P2/6 + P1/6
Working the example a couple more steps: Xa4 = (P4 + P3/3 + P1/3 + P2/6 + P1/6) / 4 Xa4 = P4/4 + P3/12 + P1/12 + P2/24 + P1/24

Xa5 = (P5 + P4/4 + P3/12 + P1/12 + P2/24 + P1/24) / 5 Xa5 = P5/5 + P4/20 + P3/60 + P1/60 + P2/120 + P1/120

Following this pattern, the following rule seems to apply, where n is a number, and n! is n factorial (n times every integer number down to 1)
Xan = Pn / ((n! / (n-1)!) + Pn-1 / ((n! / (n-2)!) + Pn-2 / ((n! / (n-3)!) +

     ... + P2 / ((n! / 1) + P1 / ((n! / 1) + P1 / ((n! / 2)

Are you sure that this is how the moving average is calculated? If it is, I suggest that you add another column to the table to store the previously calculated moving average, and then it is a simple matter to calculate the average of the moving average column without pegging the server's CPU trying to repeatedly calculate 1000! (assuming n is 1000) many times over.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun May 18 2008 - 09:29:53 CDT