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 |
|
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 #660370 is a reply to message #660368] |
Wed, 15 February 2017 07:52 |
|
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 #660380 is a reply to message #660379] |
Wed, 15 February 2017 10:02 |
|
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 |
|
Michel Cadot
Messages: 68693 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 #660390 is a reply to message #660384] |
Wed, 15 February 2017 10:43 |
Solomon Yakobson
Messages: 3285 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 #660396 is a reply to message #660392] |
Wed, 15 February 2017 13:44 |
Solomon Yakobson
Messages: 3285 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 |
|
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 |
Solomon Yakobson
Messages: 3285 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 |
|
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 #660445 is a reply to message #660440] |
Thu, 16 February 2017 08:51 |
Solomon Yakobson
Messages: 3285 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 |
|
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
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:46:15 CDT 2024
|