# Re: Calculating moving average

Date: Sun, 18 May 2008 07:29:53 -0700 (PDT)

Message-ID: <8d96966c-a1ec-44ba-8489-54c2f67bb394@u6g2000prc.googlegroups.com>

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