Re: Calculating moving average
Date: Sun, 18 May 2008 07:29:53 -0700 (PDT)
On May 18, 2:19 am, nickli2..._at_gmail.com wrote:
> 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 =
> 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
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(
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);
AVG(PRICE) OVER (PARTITION BY STOCK ORDER BY STOCK_DATE) AVG_PRICE FROM
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
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
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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun May 18 2008 - 09:29:53 CDT