extract pairs [message #433801] |
Fri, 04 December 2009 05:45  |
joshua82
Messages: 31 Registered: December 2009
|
Member |
|
|
Hi!
I have a problem with PL/SQL. I need to do a subtraction between pairs of values in a column.
Like this:
VALUES_MARKET
12
13
15
18
17
18
and create a table like this:
RESULT
1
3
1
(13-12=1; 18-15=3 and so on..). I'm creating a function but I don't know how..
Thanks!
|
|
|
|
Re: extract pairs [message #433809 is a reply to message #433803] |
Fri, 04 December 2009 06:08   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
LAG/LEAD leaves you with some fiddly processing to handle first and last values.
I'd do it with First_Value and Last_Value:create table test_105 (ord number, VALUES_MARKET number);
insert into test_105 values (1,12);
insert into test_105 values (2,13);
insert into test_105 values (3,15);
insert into test_105 values (4,18);
insert into test_105 values (5,17);
insert into test_105 values (6,18);
select first_val,last_val
from (select first_value(values_market) over (partition by grp order by ord rows between unbounded preceding and unbounded following) first_val
,last_value(values_market) over (partition by grp order by ord rows between unbounded preceding and unbounded following) last_val
,row_number() over (partition by grp order by ord) rnum
from (select ord
,values_market
,ceil(row_number() over (order by ord)/2) grp
from test_105))
where rnum = 1;
|
|
|
Re: extract pairs [message #433930 is a reply to message #433809] |
Sat, 05 December 2009 02:46   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
I was thinking about another solution.
Since the problem only involves pairs, you would not be bothered by last & first rows.
What about something like this? (Note: used JRowbottom's testcase)
SQL> select diff
2 from (select values_market - lag(values_market) over (order by ord) as diff
3 , rownum as rn
4 from test_105
5 order by ord
6 )
7 where mod(rn, 2) = 0
8 order by rn
9 /
DIFF
----------
1
3
1
[Edit: Added order by in main query]
[Updated on: Sat, 05 December 2009 02:47] Report message to a moderator
|
|
|
|
|
Re: extract pairs [message #434430 is a reply to message #434098] |
Wed, 09 December 2009 04:51   |
joshua82
Messages: 31 Registered: December 2009
|
Member |
|
|
I resume this topic because I have a problem with Frank's solution: I create a function like this:
select diff from(
select valmer - lag(valmer) over (order by num) as diff, rownum as rn
from (select valmer, rownum num
from (select a_function(valmer,x) as valmer
from derivative
where cod = '7155' group by valmer))
order by num
)
where mod(rn, 2) = 0
order by rn;
where a_function is execute for every values in valmer which return a number and x is a constant defined before.
This situation creates an error when I'm compiling..
[Updated on: Wed, 09 December 2009 04:52] Report message to a moderator
|
|
|
|
|
|
Re: extract pairs [message #434437 is a reply to message #434434] |
Wed, 09 December 2009 05:06   |
joshua82
Messages: 31 Registered: December 2009
|
Member |
|
|
a_function takes valmer (is a stock valuation) and x and return the price of correlated finance option (for everyday stock valuation).
If I do this in a new window
select a_function(valmer,3) as valmer from derivative
where cod = '7155' group by valmer
this return:
VALMER
------
6,12
3,77
2,35
|
|
|
|
|
|
Re: extract pairs [message #434450 is a reply to message #434444] |
Wed, 09 December 2009 05:35   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The structure doesn't error on 10.2.0.4
SQL>
SQL> create or replace function a_function (p_in number,p_in2 number) return number as
2 begin
3 return p_in;
4 end;
5 /
Function created.
SQL>
SQL> create table test_derivative (valmer number, cod varchar2(4));
Table created.
SQL>
SQL> insert into test_derivative values (1,'7155');
1 row created.
SQL> insert into test_derivative values (2,'7155');
1 row created.
SQL> insert into test_derivative values (3,'7155');
1 row created.
SQL>
SQL> select diff from(
2 select valmer - lag(valmer) over (order by num) as diff, rownum as rn
3 from (select valmer, rownum num
4 from (select a_function(valmer,3) as valmer
5 from test_derivative
6 where cod = '7155' group by valmer))
7 order by num
8 )
9 where mod(rn, 2) = 0
10 order by rn;
DIFF
----------
1
|
|
|
Re: extract pairs [message #434547 is a reply to message #434430] |
Wed, 09 December 2009 11:59  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Since the only function the group by performs in your (oversimplified?) query is to remove duplicates, try to replace it with distinct.
|
|
|