Home » SQL & PL/SQL » SQL & PL/SQL » Need help with SQL query (merged) (10 XE 1.2.0)
Need help with SQL query (merged) [message #425384] Thu, 08 October 2009 21:35 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hello Oracle Gurus,

I am having a table with below columns like this.
Code  TranDate    Volume   DayOpen   DayClose
----------------------------------------------
ABC   08-Oct-09  10000      1.5      2.0
ABC   09-Oct-09  34534      2.0      2.10
ABC   10-Oct-09  340000     3.0      5.0

How can I query this table to get all the stocks that has greater volume than the previous day / previous 2 days and also the latest DayClose is greater than previous days DayClose.

Thanks,
Mahesh
Re: Need help with SQL query [message #425389 is a reply to message #425384] Thu, 08 October 2009 22:57 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>How can I query this table to get all the stocks that has greater volume than the previous day / previous 2 days

Perhaps I am just having a senior moment, but I don't know exactly the selection criteria is supposed to be.

The original post lacks detail of expected/desired results using actual sample input data.
Re: Need help with SQL query [message #425398 is a reply to message #425384] Fri, 09 October 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Need help with SQL query [message #425399 is a reply to message #425384] Fri, 09 October 2009 00:40 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
To get next or previous records, you can use LAG/LEAD analytic functions.

regards,
Delna
Re: Need help with SQL query (merged) [message #425443 is a reply to message #425384] Fri, 09 October 2009 03:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This should give you some pointers:
create table test_084 (Code varchar2(3),  TranDate date,   Volume number,   DayOpen number,   DayClose number);

insert into test_084 values ('ABC',  to_date('08-Oct-09','dd-mon-yyyy'), 10000  ,1.5,      2.0);
insert into test_084 values ('ABC',  to_date('09-Oct-09','dd-mon-yyyy'), 34534  ,2.0,      2.10);
insert into test_084 values ('ABC',  to_date('10-Oct-09','dd-mon-yyyy'), 340000 ,3.0,      5.0);

commit;

select code
      ,trandate
      ,volume
      ,lag(volume,1) over (partition by code order by trandate) prev_day_volume
      ,lag(volume,2) over (partition by code order by trandate) prev_2day_volume
      ,dayclose
      ,lag(dayclose,1) over (partition by code order by trandate) prev_dayclose
from   test_084;
Re: Need help with SQL query (merged) [message #425520 is a reply to message #425443] Fri, 09 October 2009 06:59 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Wow Excellent. Got it. Thank you very much..

Sorry for not posting the code for creating the table & data. Next time I will make sure I will also post the table structure & data

Appreciate it.

Mahesh
Re: Need help with SQL query (merged) [message #425544 is a reply to message #425520] Fri, 09 October 2009 10:06 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Can you tell me what types of indexes that I need to use on the columns to improve the performance?

I am having about 30 lag functions.

Thanks,
Mahesh
Re: Need help with SQL query (merged) [message #425545 is a reply to message #425544] Fri, 09 October 2009 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>Can you tell me what types of indexes that I need to use on the columns to improve the performance?

mauve indexes work best with LAG function
Re: Need help with SQL query (merged) [message #425547 is a reply to message #425545] Fri, 09 October 2009 10:40 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
How can create that index, I googled it but didn't get right information.
Re: Need help with SQL query (merged) [message #425550 is a reply to message #425547] Fri, 09 October 2009 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Fri, 09 October 2009 17:22
>Can you tell me what types of indexes that I need to use on the columns to improve the performance?

mauve indexes work best with LAG function

Mahesh P wrote on Fri, 09 October 2009 17:40
How can create that index, I googled it but didn't get right information.


./fa/449/0/

Thanks for that, a good we is starting.

Regards
Michel

Re: Need help with SQL query (merged) [message #425552 is a reply to message #425550] Fri, 09 October 2009 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
http://dilbert.com/strips/comic/1995-11-17
Re: Need help with SQL query (merged) [message #425570 is a reply to message #425552] Fri, 09 October 2009 12:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

./fa/2115/0/ I bookmark it.

Regards
Michel
Re: Need help with SQL query (merged) [message #425816 is a reply to message #425544] Mon, 12 October 2009 06:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Indexes can do very little to improve the performance of Analytic functions - indexes can help you to retrieve a set of rows quicker, but analytic functions do their work on the rowset after it has been fetched.
Once you've tuned the underlying query, other than increasing the amount of memory available per session, there's not a great deal you can do.
Previous Topic: Striping characters (merged)
Next Topic: Excel generation from oralce
Goto Forum:
  


Current Time: Fri Dec 09 11:51:37 CST 2016

Total time taken to generate the page: 0.10025 seconds