rolling product

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
PHIL SawyerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
perhaps you could explain how "rolling_product" is calculated?
0
PortletPaulfreelancerCommented:
oops, do you mean "product" as in *
|                          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
;

Open in new window

The lag(value) over(order by adate) supplies the previous row's value for the calculation
0
johnsoneSenior Oracle DBACommented:
PortletPaul,

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PHIL SawyerAuthor Commented:
Sorry - should have given Product logic in first instance - see attached for correct Product values and logic.
product.xlsx
0
PortletPaulfreelancerCommented:
B2 contains:   =PRODUCT(1+A1:A2)^(2/COUNT(A1:A2))-1
B3 contains:   =PRODUCT(1+A1:A3)^(3/COUNT(A1:A3))-1
B4 contains:   =PRODUCT(1+A1:A4)^(4/COUNT(A1:A4))-1

^(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?
0
PHIL SawyerAuthor Commented:
No specific purpose...
^(2/2)-1
 ^(3/3)-1
 ^(4/4)-1
.. is fine
0
PortletPaulfreelancerCommented:
my point really was that inside the parens 2/2 = 1 3/3 = 1 4/4 = 1

^1-1

appears to be constant
0
PHIL SawyerAuthor Commented:
Correct - overkill
0
awking00Commented:
SQL> select * from dataset;

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.7182818284569,value)))
  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
0
PHIL SawyerAuthor Commented:
awking

This looks good but got error as

Execution (17: 42): ORA-01428: argument '-.02323306236326536924557729372082855266' is out of range
0
PHIL SawyerAuthor Commented:
awking
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
0
awking00Commented:
I believe you're getting that error because some of the values are negative numbers and the exspression parameter for the log function requires a positive value. I'm not sure how you would want to handle this issue, but I think it can be done using absolute values and negating results if needed.
0
awking00Commented:
To develop a test case for negative numbers, can you search for a negative number and, if you find one get that date and records for a couple of days before and a couple of days after and post them back here.
0
sdstuberCommented:
similar idea to above but doesn't require double query on the source data

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;
0
sdstuberCommented:
Another option - using MODEL clause, will support negative values

SELECT yourdate,VALUE,ROUND(rolling_product,9) rolling_product FROM
                 (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)-1],1) * VALUE[CV(rn)])
ORDER BY yourdate
0
awking00Commented:
Thanks, sdstuber. I was trying to do this in one pass but forgot all about an OVER (ORDER BY ... ROWS BETWEEN ...) clause (don't use it very much). I like the MODEL approach in that it manages negative numbers. 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.
0
sdstuberCommented:
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(rolling_product,9)) rolling_product FROM
                 (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)-1],1) * VALUE[CV(rn)])
ORDER BY yourdate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PHIL SawyerAuthor Commented:
Hi
Unfortunately the Model logic eturns are incorrect - see attached.
Regards
data.xlsx
0
sdstuberCommented:
The results in column L look correct to me.

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;
0
sdstuberCommented:
on second glance, your column G values aren't sums either.

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.
0
sdstuberCommented:
I don't understand the close.  Did you actually get your answer? Did every post really contribute to that answer?
0
sdstuberCommented:
The close on this still seems dubious, but I thought I'd offer one more variation anyway.
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.