Home » SQL & PL/SQL » SQL & PL/SQL » cumulative sum & product (oracle 11g release 2 )
cumulative sum & product [message #660367] Wed, 15 February 2017 07:27 Go to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi All ,

I am trying to achieve below result with out using loops in sql alone. I could do cumulative sum & cumulative multiplication but could not find the way to achieve both together.

The business use case I am trying to achieve is as below.

We need to find the increase in product price through out the horizon , for example in case of the current period , it would be product price from previous period + (Product price from previous period * percentage increase over previous period ) .

below is the sample data

Product cost percent expected_result
1 10 2 20
2 0 3 20 + (20 * 3) = 80
3 0 4 80 + (80 *4) = 400
4 0 5 400 + (400 * 5) = 2400

Thanks & Regards
Santosh Varada
Re: cumulative sum & product [message #660368 is a reply to message #660367] Wed, 15 February 2017 07:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: cumulative sum & product [message #660370 is a reply to message #660368] Wed, 15 February 2017 07:52 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi

Sorry , I am posting this in correct format now.

The business use case I am trying to achieve is as below.

We need to find the increase in product price through out the horizon , for example in case of the current period , it would be product price from previous period + (Product price from previous period * percentage increase over previous period ) .

below is the sample data


   Product  cost   percent   expected_result
    1       10      2        20
    2       0       3        20 + (20 * 3) = 80
    3       0       4        80 + (80 *4) = 400
    4       0       5        400 + (400 * 5) = 2400


Thanks & Regards
Santosh Varada

[Updated on: Wed, 15 February 2017 08:21]

Report message to a moderator

Re: cumulative sum & product [message #660374 is a reply to message #660370] Wed, 15 February 2017 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Have a look at SUM function in its analytic form.

Re: cumulative sum & product [message #660378 is a reply to message #660374] Wed, 15 February 2017 09:48 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi Michel

Sorry , as my original table is very big I tried to minimize it and post the relevant version here, as mentioned in earlier message the business case is such that find the increase in product price through out the horizon using the formula mentioned.

Thanks & Regards
Santosh Varada
Re: cumulative sum & product [message #660379 is a reply to message #660378] Wed, 15 February 2017 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't need to post the whole table, just the columns and data relevant to the question and shown in the example.
Please read the link.



Re: cumulative sum & product [message #660380 is a reply to message #660379] Wed, 15 February 2017 10:02 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi Michel

Below is the table structure & insert statements with data , also one more thing I would like to mention that , I tried it using "WITH" clause, but I am looking for a way which is other than "WITH" clause.

create table prod_info(product number, cost number  , percent number );

insert into prod_info values(1,10,2);
insert into prod_info values(2,0,3);
insert into prod_info values(3,0,4);
insert into prod_info values(4,0,5);

  

Thanks for your help.

Regards
Santosh Varada
Re: cumulative sum & product [message #660383 is a reply to message #660380] Wed, 15 February 2017 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, I misread you problem, you can't do it with analytic function, you have to do it using a recursive query (assuming your product number is a sequence number):
SQL> with
  2    data (product, cost, percent, result) as (
  3      select product, cost, percent, cost*percent result
  4      from prod_info
  5      where product = 1
  6      union all
  7      select p.product, p.cost, p.percent, d.result + (d.result * p.percent)
  8      from data d, prod_info p
  9      where p.product = d.product+1
 10    )
 11  select *
 12  from data
 13  order by product
 14  /
   PRODUCT       COST    PERCENT     RESULT
---------- ---------- ---------- ----------
         1         10          2         20
         2          0          3         80
         3          0          4        400
         4          0          5       2400

4 rows selected.
Re: cumulative sum & product [message #660384 is a reply to message #660383] Wed, 15 February 2017 10:20 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi Michel

Okay, thank you.

which means we can't achieve this using any of the joins & existing window/Analytic functions, either we have to use recursive sub-query (or) loops, but as per my unuderstanding compared to loops recursive subquery gives better performance. Please correct me if I am wrong.

Regards
Santosh Varada
Re: cumulative sum & product [message #660385 is a reply to message #660384] Wed, 15 February 2017 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is correct, the native recursive query is far faster than any programmatic loop.

Re: cumulative sum & product [message #660390 is a reply to message #660384] Wed, 15 February 2017 10:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
You can also use MODEL:

select  product,
        cost,
        percent,
        result
  from  prod_info
  model
    dimension by(product)
    measures(
             cost,
             percent,
             percent * cost result
            )
    rules(
          result[product > 1] order by product = result[cv() - 1] + result[cv() - 1] * percent[cv()]
         )
  order by product
/

   PRODUCT       COST    PERCENT     RESULT
---------- ---------- ---------- ----------
         1         10          2         20
         2          0          3         80
         3          0          4        400
         4          0          5       2400

SQL> 

SY.
Re: cumulative sum & product [message #660392 is a reply to message #660390] Wed, 15 February 2017 12:04 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Thank You , Michel & Solomon .
Re: cumulative sum & product [message #660396 is a reply to message #660392] Wed, 15 February 2017 13:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Actually, there is no need for recursion. Formula for Nth row result:

COST1 * PERCENT1 * (PERCENT2 + 1) * (PERCENT3 + 1) ... * (PERCENTn + 1)

But unfortunately there is no analytic product function so I'll use LOG + SUM + POWER:

select  product,
        cost,
        percent,
        first_value(cost) over(order by product) * first_value(percent) over(order by product) *
        power(10,sum(case product when 1 then 0 else log(10,percent + 1) end) over(order by product)) result
  from  prod_info
  order by product
/

   PRODUCT       COST    PERCENT RESULT
---------- ---------- ---------- ------
         1         10          2     20
         2          0          3     80
         3          0          4    400
         4          0          5   2400

SQL> 

Also, you could use hierarchical query + either XMLQUERY function or dbms_aw.eval_number function, if you are licensed for OLAP:

select  product,
        cost,
        percent,
        xmlcast(
                xmlquery(
                         connect_by_root cost ||
                           sys_connect_by_path(
                                               '(' || percent ||
                                                 case product
                                                   when 1 then ')'
                                                   else ' + 1)'
                                                 end,
                                               ' * '
                                              )
                         returning content
                        )
                as number
               ) result
  from  prod_info
  start with product = 1
  connect by product = prior product + 1
/

   PRODUCT       COST    PERCENT RESULT
---------- ---------- ---------- ------
         1         10          2     20
         2          0          3     80
         3          0          4    400
         4          0          5   2400

SQL> 

select  product,
        cost,
        percent,
        dbms_aw.eval_number(
                            connect_by_root cost ||
                              sys_connect_by_path(
                                                  '(' || percent ||
                                                    case product
                                                      when 1 then ')'
                                                      else ' + 1)'
                                                    end,
                                                  ' * '
                                                 )
                           ) result
  from  prod_info
  start with product = 1
  connect by product = prior product + 1
/

   PRODUCT       COST    PERCENT RESULT
---------- ---------- ---------- ------
         1         10          2     20
         2          0          3     80
         3          0          4    400
         4          0          5   2400

SQL> 

SY.
Re: cumulative sum & product [message #660409 is a reply to message #660396] Wed, 15 February 2017 22:02 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi Solomon

Thanks a lot for the several approaches, the below query suits my scenario , but I missed one point in my earlier description , i.e the initial sum which should be like below.
I tried to tweak the query but no luck , could you help me once again.


select  product,
        cost,
        percent,
        first_value(cost) over(order by product) * first_value(percent) over(order by product) *
        power(10,sum(case product when 1 then 0 else log(10,percent + 1) end) over(order by product)) result
  from  prod_info
  order by product

Product  cost   percent   expected_result
    1       10      2        10 + (10 * 2) = 30
    2       0       3        30 + (30 * 3) = 120
    3       0       4        120 + (120 *4) = 600
    4       0       5        600 + (600 * 5) = 3600




[Updated on: Thu, 16 February 2017 06:29]

Report message to a moderator

Re: cumulative sum & product [message #660438 is a reply to message #660409] Thu, 16 February 2017 07:19 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
That's even simpler. Formula becomes consistent:

COST1 * (PERCENT1 + 1) * (PERCENT2 + 1) * (PERCENT3 + 1) ... * (PERCENTn + 1)

select  product,
        cost,
        percent,
        first_value(cost) over(order by product) *
        power(10,sum(log(10,percent + 1)) over(order by product)) result
  from  prod_info
  order by product
/

   PRODUCT       COST    PERCENT     RESULT
---------- ---------- ---------- ----------
         1         10          2         30
         2          0          3        120
         3          0          4        600
         4          0          5       3600

SQL> 

SY.
Re: cumulative sum & product [message #660440 is a reply to message #660438] Thu, 16 February 2017 08:11 Go to previous messageGo to next message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi Solomon

Thank you for your help once again , but the query works fine with the above data set, when I use real percentages where the values are as below , the query does not give desired result. Could it be because of logarithmic functions being used.

insert into prod_info values(1 ,90 , 0.0328);
insert into prod_info values(2, 0, 0.0358);
insert into prod_info values(3 , 0, 0.0344);
insert into prod_info values (4, 0, 0.0332);

Result is as below
---------------

  PRODUCT       COST       PERCENT            RESULT
---------- ----------      ----------        ----------
         1         90          0.0328         3.222
         2          0          0.0358         0.11083679999999999
         3          0          0.0344         0.003679781759999998
         4          0          0.0332         0.003679781759999998



but the expected result as per the formula is as below


  PRODUCT       COST       PERCENT            RESULT
---------- ----------      ----------        ----------
         1         90          0.0328         90 + (90 * 0.0328) = 92.952
         2          0          0.0358         92.952 + (92.952 * 0.0358) = 96.27
         3          0          0.0344         96.27 + (96.27 * 0.0344) = 99.58
         4          0          0.0332         99.58 + (99.58 * 0.0332) = 102.88



Thanks & Regards
Santosh Varada
Re: cumulative sum & product [message #660444 is a reply to message #660440] Thu, 16 February 2017 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You would benefit from remedial mathematics training.
"0.0328%" = 0.000328
Re: cumulative sum & product [message #660445 is a reply to message #660440] Thu, 16 February 2017 08:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, that's what happens when you don't provide detail. LOG, in general, results in an infinite fraction while Oracle number precision is limited and you'll lose precision on very large or very small numbers or on numbers with long fractional part. Therefore, this solution will not work in your case. You can use other solutions or write your own analytic/aggregate PRODUCT function:

create or replace
  type product_type as object(
                              factor number,
                              static function ODCIAggregateInitialize(
                                                                      sctx in out product_type
                                                                     )
                                return number,
                              member function ODCIAggregateIterate(
                                                                   self   in out product_type,
                                                                   factor in     number
                                                                  )
                                return number,
                              member function ODCIAggregateTerminate(
                                                                     self    in  product_type,
                                                                     product out number,
                                                                     flags   in  number
                                                                    )
                                return number,
                              member function ODCIAggregateMerge(
                                                                 self in out product_type,
                                                                 ctx2 in     product_type
                                                                )
                                return number
                             );
/ 
create or replace
  type body product_type
    is
      static function ODCIAggregateInitialize(
                                              sctx in out product_type
                                             )
        return number
        is
        begin
            sctx := product_type(1) ;
            return ODCIConst.Success ;
      end;
      member function ODCIAggregateIterate(
                                           self   in out product_type,
                                           factor in     number
                                          )
        return number
        is
        begin
            self.factor := self.factor * factor ;
            return ODCIConst.Success;
      end;
      member function ODCIAggregateTerminate(
                                             self    in  product_type,
                                             product out number,
                                             flags   in  number
                                            )
        return number
        is
        begin
            product := self.factor;
            return ODCIConst.Success;
        end;
      member function ODCIAggregateMerge(
                                         self in out product_type ,
                                         ctx2 in     product_type
                                        )
        return number
        is
        begin
            self.factor := self.factor * ctx2.factor;
            return ODCIConst.Success;
        end;
end;
/ 
create or replace
  function product(
                   factor number
                  )
    return number
    deterministic
    parallel_enable
    aggregate using product_type;
/

Now:

select  product,
        cost,
        percent,
        first_value(cost) over(order by product) *
        product(percent + 1) over(order by product) result
  from  prod_info
  order by product
/

   PRODUCT       COST    PERCENT       RESULT
---------- ---------- ---------- ------------
         1         90      .0328    92.952000
         2          0      .0358    96.279682
         3          0      .0344    99.591703
         4          0      .0332   102.898147

SQL> 

SY.
Re: cumulative sum & product [message #660450 is a reply to message #660445] Thu, 16 February 2017 10:02 Go to previous message
varada
Messages: 9
Registered: February 2017
Junior Member
Hi Solomon

I am sorry that I did not provide the correct information , Thanks a lot for your help.

hence it is not possible to achieve this with simple analytic/window functions (or) any of the sql joins .

Thanks & Regards
Santosh Varada
Previous Topic: Create a Job to run Oracle Package stored procedure
Next Topic: Synonym switching
Goto Forum:
  


Current Time: Thu Mar 28 20:21:48 CDT 2024