Re: Calculating moving average

On May 18, 10:32 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> nickli2..._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- Hide quoted text -
>
> - Show quoted text -

Nicely done. Thank you for the education on the MODEL clause.

David Fitzjarrell Received on Sun May 18 2008 - 14:41:26 CDT