Re: Calculating moving average

From: Maxim Demenko <mdemenko_at_gmail.com>
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?
>
>
> 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  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

Original text of this message