Home » SQL & PL/SQL » SQL & PL/SQL » complex sql query
complex sql query [message #193810] Tue, 19 September 2006 09:08 Go to next message
antana
Messages: 3
Registered: September 2006
Junior Member
i have this table

iprice(
ora varchar(8),
prz number(15,5),
vol number(15,5));

with this values:

ora prz vol
09:01:03 10.6 1000
09:01:05 10.5 1500
09:01:30 11.0 1600
09:02:05 10.8 2600
09:02:20 10.75 3500
09:02:40 11.01 3700
...

i want a select to obtain for every minutes
the first prz,max prz, min prz and the vol difference from the last time and the last time of the minute prior

in this example:
time min max first price vol
09:01 10.5 11.0 10.6 (1600-0) 1600
09:02 10.75 11.01 10.8 (3700-1600) 1100

thank's
GB
Re: complex sql query [message #193838 is a reply to message #193810] Tue, 19 September 2006 11:03 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


Couple of questions first

1) why do we have ora as varchar2 and not as date ?

2) Does 3700-1600 equal 1100 ?


The table
==========
SQL> desc foo;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORA DATE
PRZ NUMBER(15,5)
VOL NUMBER(15,5)


The Data
========
insert into dw.foo values (to_date('09:01:03','hh24:mi:ss'), 10.6,1000);
insert into dw.foo values (to_date('09:01:05','hh24:mi:ss'), 10.5,1500);
insert into dw.foo values (to_date('09:01:30','hh24:mi:ss'), 11.0,1600);
insert into dw.foo values (to_date('09:02:05','hh24:mi:ss'), 10.8,2600);
insert into dw.foo values (to_date('09:02:20','hh24:mi:ss'), 10.75,3500);
insert into dw.foo values (to_date('09:02:40','hh24:mi:ss'), 11.01,3700 );
commit ;



The Query
==========


select to_char(ora,'hh24:mi') time ,
minprz , maxprz , first_prz ,
last_vol - lag(last_vol,1,0) over( order by ora ) voldiff
from (
select trunc(ora,'mi') ora ,
min(prz) keep (dense_rank first order by ora ) first_prz ,
max(prz) maxprz ,
min(prz) minprz ,
max(vol) keep (dense_rank last order by ora ) last_vol
from dw.foo
group by trunc(ora,'mi')
);



The output
==========


TIME MINPRZ MAXPRZ FIRST_PRZ VOLDIFF
----- ---------- ---------- ---------- ----------
09:01 10.5 11 10.6 1600
09:02 10.75 11.01 10.8 2100

Srini



Re: complex sql query [message #193839 is a reply to message #193838] Tue, 19 September 2006 11:20 Go to previous messageGo to next message
antana
Messages: 3
Registered: September 2006
Junior Member
thank's srinivnp

1) it's an old table type, but i can change with date type
2) oops !!!! it's a write error !!!! , 2100 it's the correct one.
Re: complex sql query [message #246900 is a reply to message #193839] Fri, 22 June 2007 07:18 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

u can also do by
using
lag and lead function
with query from query
Re: complex sql query [message #246910 is a reply to message #246900] Fri, 22 June 2007 07:50 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a solution instead of words.
But I doubt they are interested 9 months later.

Regards
Michel
Previous Topic: Package
Next Topic: Records from sql server to Oracle
Goto Forum:
  


Current Time: Sat Dec 10 02:52:58 CST 2016

Total time taken to generate the page: 0.07591 seconds