Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

rolling product

Posted on 2014-07-11
22
Medium Priority
?
272 Views
Last Modified: 2014-07-15
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
0
Comment
Question by:PHIL Sawyer
  • 7
  • 6
  • 4
  • +2
22 Comments
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 320 total points
ID: 40190248
perhaps you could explain how "rolling_product" is calculated?
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 320 total points
ID: 40190282
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 80 total points
ID: 40190302
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:PHIL Sawyer
ID: 40190308
Sorry - should have given Product logic in first instance - see attached for correct Product values and logic.
product.xlsx
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 320 total points
ID: 40190424
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
 

Author Comment

by:PHIL Sawyer
ID: 40190455
No specific purpose...
^(2/2)-1
 ^(3/3)-1
 ^(4/4)-1
.. is fine
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 320 total points
ID: 40190460
my point really was that inside the parens 2/2 = 1 3/3 = 1 4/4 = 1

^1-1

appears to be constant
0
 

Author Comment

by:PHIL Sawyer
ID: 40190502
Correct - overkill
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 640 total points
ID: 40190608
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
 

Author Comment

by:PHIL Sawyer
ID: 40190643
awking

This looks good but got error as

Execution (17: 42): ORA-01428: argument '-.02323306236326536924557729372082855266' is out of range
0
 

Author Comment

by:PHIL Sawyer
ID: 40190650
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 640 total points
ID: 40190693
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
 
LVL 32

Expert Comment

by:awking00
ID: 40190716
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 960 total points
ID: 40190808
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 960 total points
ID: 40190846
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 640 total points
ID: 40190954
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 960 total points
ID: 40190971
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
 

Author Comment

by:PHIL Sawyer
ID: 40193948
Hi
Unfortunately the Model logic eturns are incorrect - see attached.
Regards
data.xlsx
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 960 total points
ID: 40194331
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 960 total points
ID: 40194641
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40196698
I don't understand the close.  Did you actually get your answer? Did every post really contribute to that answer?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40197822
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question