Re: Calculating moving average
Date: Sun, 18 May 2008 17:32:14 +0200
Message-ID: <48304BFE.1000008@gmail.com>
nickli2000_at_gmail.com schrieb:
> 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
>
As Charles already shown, the key point for your problem is recursion - you need for every row ( ordered by date ) previously calculated values to calculate the current one. In my opinion, to solve tasks of recursive nature only with analytical functions might be a good challenge and in general case probably not always doable. However, if you are on the somewhat recent version of oracle, then the solution may be trivial (and very efficient in terms of resources)
SQL> with t as (
2 select 'X' Stock,to_date('05.01.2008','mm.dd.yyyy') Dt,10 Price from dual union all
3 select 'X',to_date('05.02.2008','mm.dd.yyyy'),12 from dual union all
4 select 'X',to_date('05.03.2008','mm.dd.yyyy'),13 from dual union all
5 select 'Y',to_date('05.01.2008','mm.dd.yyyy'),20 from dual union all
6 select 'Y',to_date('05.02.2008','mm.dd.yyyy'),22 from dual union all
7 select 'Y',to_date('05.03.2008','mm.dd.yyyy'),23 from dual
8 )
9 -- End test data
10 select Stock,dt,Price,av
11 from t
12 model
13 partition by (stock)
14 dimension by (row_number() over(partition by stock order by dt) n) 15 measures(price,dt,0 av,row_number() over(partition by stock order by dt) n_m )
16 rules
17 (
18 av[any] order by n=(sum(av)[any] + price[cv()])/ n_m[cv()] 19 )
20 ;
S DT PRICE AV
- ---------- ---------- ----------
Y 05/01/2008 20 20 Y 05/02/2008 22 21 Y 05/03/2008 23 21.3333333 X 05/01/2008 10 10 X 05/02/2008 12 11 X 05/03/2008 13 11.3333333
6 rows selected.
Now a slightly modified example, which shows - 1000 recursive iterations are not a problem at all:
SQL> with t as (
2 select 'X' Stock, trunc(sysdate) + rownum - 1000 dt,trunc(dbms_random.value(0,100)) price from dual
3 connect by level < 1001
4 )
5 -- End test data
6 select Stock,dt,Price,av
7 from t
8 model
9 partition by (stock)
10 dimension by (row_number() over(partition by stock order by dt) n)
11 measures(price,dt,0 av,row_number() over(partition by stock order
by dt) n_m )
12 rules
13 (
14 av[any] order by n=(sum(av)[any] + price[cv()])/ n_m[cv()]
15 )
16 ;
1000 rows selected.
Elapsed: 00:00:00.39
Execution Plan
Plan hash value: 2046059844
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 3
(34)| 00:00:01 |
| 1 | SQL MODEL ORDERED | | 1 | 22 | 3
(34)| 00:00:01 |
| 2 | WINDOW SORT | | 1 | 22 | 3
(34)| 00:00:01 |
| 3 | VIEW | | 1 | 22 | 2
(0)| 00:00:01 |
| 4 | COUNT | | | |
| |
|* 5 | CONNECT BY WITHOUT FILTERING| | | |
| |
| 6 | FAST DUAL | | 1 | | 2
(0)| 00:00:01 |
Predicate Information (identified by operation id):
5 - filter(LEVEL<1001)
Statistics
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
46891 bytes sent via SQL*Net to client
1126 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1000 rows processed
You may need an extra handling for NULL values - that may depend on your business logic.
Best regards
Maxim Received on Sun May 18 2008 - 10:32:14 CDT