Solved

Posted on 2014-07-11

Hi

Is it possible to have a rolling PRODUCT function on a data set and if so - how?

eg select Date, Value from table

My data set ....

Date Value

01/01/2014 0.4656

01/02/2014 0.5684

01/03/2014 1.4567

01/04/2014 0.6589

... required data set with column "Rolling_Product"

Date Value Rolling_Product

01/01/2014 0.4656

01/02/2014 0.5684 0.26464704

01/03/2014 1.4567 0.385511343

01/04/2014 0.6589 0.254013424

Is it possible to have a rolling PRODUCT function on a data set and if so - how?

eg select Date, Value from table

My data set ....

Date Value

01/01/2014 0.4656

01/02/2014 0.5684

01/03/2014 1.4567

01/04/2014 0.6589

... required data set with column "Rolling_Product"

Date Value Rolling_Product

01/01/2014 0.4656

01/02/2014 0.5684 0.26464704

01/03/2014 1.4567 0.385511343

01/04/2014 0.6589 0.254013424

22 Comments

```
| ADATE | VALUE | ROLLING_PRODUCT |
|--------------------------------|--------|-----------------|
| January, 01 2014 00:00:00+0000 | 0.4656 | (null) |
| January, 02 2014 00:00:00+0000 | 0.5684 | 0.26464704 |
| January, 03 2014 00:00:00+0000 | 1.4567 | 0.82798828 |
| January, 04 2014 00:00:00+0000 | 0.6589 | 0.95981963 |
select
adate
, value
, value * lag(value) over(order by adate) as rolling_product
from yourtable
;
```

The lag(value) over(order by adate) supplies the previous row's value for the calculation
You are mulitplying by the previous value, not the previous product. To match the expected results posted, the ROLLING_PRODUCT in the third row should be 1.4567 * 0.26464704, not 1.4567 * 0.5684.

product.xlsx

B3 contains: =PRODUCT(1+A1:A3)

B4 contains: =PRODUCT(1+A1:A4)

^(2/2)-1

^(3/3)-1

^(4/4)-1

is there a specific purpose to the method of calculation for the second part of those equations?

SOMEDATE VALUE

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

01-JAN-14 .4656

02-JAN-14 .5684

03-JAN-14 1.4567

04-JAN-14 .6589

SQL> select d1.somedate, d1.value,

2 (select exp(sum(log(2.718281828456

3 from dataset d2

4 where d2.somedate <= d1.somedate

5 ) as rolling_product

6 from dataset d1;

SOMEDATE VALUE ROLLING_PRODUCT

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

01-JAN-14 .4656 .4656

02-JAN-14 .5684 .26464704

03-JAN-14 1.4567 .385511343

04-JAN-14 .6589 .254013424

This looks good but got error as

Execution (17: 42): ORA-01428: argument '-.02323306236326536924557

Just to note that the data I gave originally was test data hence no error.

When run against my full data set then got the error

SELECT yourdate,

VALUE,

ROUND(

EXP(

SUM(LN(VALUE))

OVER(ORDER BY yourdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

),

9

)

rolling_product

FROM yourtable

ORDER BY yourdate;

SELECT yourdate,VALUE,ROUND(rolli

(SELECT t.*,ROW_NUMBER() OVER(ORDER BY yourdate) rn FROM yourtable t)

MODEL

DIMENSION BY(rn)

MEASURES(yourdate, VALUE, 1 rolling_product)

RULES (rolling_product[ANY] = NVL(rolling_product[CV(rn)

ORDER BY yourdate

The only potential problem I see is that once a negative value is found, all of the rolling_product numbers will be negative until another negative value is found, which may not be what the asker wants.

If a negative result is a problem, it's easily resolved by apply ABS() to the final results

SELECT yourdate,VALUE,ABS(ROUND(r

(SELECT t.*,ROW_NUMBER() OVER(ORDER BY yourdate) rn FROM yourtable t)

MODEL

DIMENSION BY(rn)

MEASURES(yourdate, VALUE, 1 rolling_product)

RULES (rolling_product[ANY] = NVL(rolling_product[CV(rn)

ORDER BY yourdate

The results in G are not a product, they are a sum which is different than what you originally asked for.

If you just want a rolling sum, that's a lot easier, but it's a different question

SELECT yourdate,

VALUE,

SUM(VALUE) OVER(ORDER BY yourdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

rolling_product

FROM yourtable

ORDER BY yourdate;

The product question has been answered, for this new data set and new results, please open a new question that includes the data as well as an explanation of what the expected results are supposed to be.

This will handle a rolling product of negative values and zeros without needing the model clause.

```
SELECT yourdate,
VALUE,
EXP(
SUM(LN(ABS(NULLIF(VALUE, 0))))
OVER(ORDER BY yourdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
)
* DECODE(
MOD(
COUNT(CASE WHEN VALUE < 0 THEN 1 END)
OVER(ORDER BY yourdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
2
),
0, 1,
-1
)
* DECODE(
COUNT(DECODE(VALUE, 0, 1))
OVER(ORDER BY yourdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
0, 1,
0
)
product
FROM yourtable
ORDER BY yourdate
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Oracle SQL Nested Select | 3 | 50 | |

Request assistance granting user permission to execute a sys function from within a package | 2 | 52 | |

Oracle SQL Query Syntax | 6 | 71 | |

Oracle PL/SQL syntax | 4 | 29 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**9** Experts available now in Live!