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