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

On May 18, 2:19 am, nickli2... (AT) gmail (DOT) com wrote:
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(
STOCK VARCHAR2(5),
STOCK_DATE DATE,
PRICE NUMBER(10,2));

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);

SELECT
STOCK,
STOCK_DATE,
AVG(PRICE) OVER (PARTITION BY STOCK ORDER BY STOCK_DATE) AVG_PRICE
FROM
T1;

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
rolling average:
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
1)
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.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On May 18, 2:19 am, nickli2... (AT) gmail (DOT) com wrote:
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(
STOCK VARCHAR2(5),
STOCK_DATE DATE,
PRICE NUMBER(10,2));

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);

SELECT
STOCK,
STOCK_DATE,
AVG(PRICE) OVER (PARTITION BY STOCK ORDER BY STOCK_DATE) AVG_PRICE
FROM
T1;

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
rolling average:
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
1)
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.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On May 18, 2:19 am, nickli2... (AT) gmail (DOT) com wrote:
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(
STOCK VARCHAR2(5),
STOCK_DATE DATE,
PRICE NUMBER(10,2));

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);

SELECT
STOCK,
STOCK_DATE,
AVG(PRICE) OVER (PARTITION BY STOCK ORDER BY STOCK_DATE) AVG_PRICE
FROM
T1;

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
rolling average:
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
1)
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.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On May 18, 2:19 am, nickli2... (AT) gmail (DOT) com wrote:
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(
STOCK VARCHAR2(5),
STOCK_DATE DATE,
PRICE NUMBER(10,2));

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);

SELECT
STOCK,
STOCK_DATE,
AVG(PRICE) OVER (PARTITION BY STOCK ORDER BY STOCK_DATE) AVG_PRICE
FROM
T1;

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
rolling average:
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
1)
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.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

nickli2000 (AT) gmail (DOT) com schrieb:
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

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

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

nickli2000 (AT) gmail (DOT) com schrieb:
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

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

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

nickli2000 (AT) gmail (DOT) com schrieb:
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

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

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

nickli2000 (AT) gmail (DOT) com schrieb:
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

-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

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

On May 18, 10:32*am, Maxim Demenko <mdeme... (AT) gmail (DOT) com> wrote:
Nicely done. Thank you for the education on the MODEL clause.


David Fitzjarrell