Home » SQL & PL/SQL » SQL & PL/SQL » Unable to get a single row per result
Unable to get a single row per result [message #248592] Fri, 29 June 2007 11:53 Go to next message
vipin.kumar
Messages: 3
Registered: June 2007
Location: INDIA
Junior Member
Hi,

First of all i am new to this forum so moderators please do not kick me out if i am slightly or waayyyyy out from the required rules and regulation. Embarassed

Secondly here is my question:

I am filtering out high frequency data ( tick by tick data for the near month futures) in order to get the trade that occurred at the 5th minute of the trading day. In National Stock Exchange the trading starts at 09:55:00 and ends at 15:30:00. So i should be able to get 67 data points per trading day.

However since multiple trading occurs at each second so instead of getting 67 data points per day i am getting much, much much more than that. May be it is because of the query i am using to filter the data.

Here is my sql query:

select * from TRADEDETAILS_NSE_FO WHERE SYMBOL='NIFTY' AND INSTRUMENT_TYPE='FUTIDX' AND
abs(MONTHS_BETWEEN(TO_DATE(TRADE_DATE,'YYYYMMDD'),TO_DATE(EXPIRY_DATE,'YYYYMMDD'))) <= 1
AND TRADE_DATE >= 20060202 AND TRADE_DATE <= 20060202  
AND (MOD(TO_NUMBER(TO_CHAR(TO_DATE(TRADE_TIME,'HH24:MI:SS'),'Mi')), 5 ) = 0) 
order by TRADE_TIME


and the results that it gives are over 16000,

How can i modify the results to get the required number of data points.
Re: Unable to get a single row per result [message #248598 is a reply to message #248592] Fri, 29 June 2007 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not sure I understand what you want but if you have 1 row per second and want each one that occurs at multiple of 5:00 then you have to use (assuming trade_time is of date datatype):
mod(to_number(to_char(trade_time,'SSSSS')),300)=0

Btw, why "TRADE_DATE >= 20060202 AND TRADE_DATE <= 20060202" and not "TRADE_DATE = 20060202"? (assuming trade_date is an integer).

Regards
Michel

Re: Unable to get a single row per result [message #248603 is a reply to message #248592] Fri, 29 June 2007 13:14 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
And if TRADE_DATE is an integer, then you'll have to do a TO_CHAR on it in your TO_DATE function first.
Re: Unable to get a single row per result [message #248777 is a reply to message #248598] Sun, 01 July 2007 23:36 Go to previous messageGo to next message
vipin.kumar
Messages: 3
Registered: June 2007
Location: INDIA
Junior Member
Hi Michael,

Thanks for your help. I was able to reduce the data from 16000 to 1000 but it was not at 5 mins interval, which is my first requirement.

So, can anyone help me here?

Thanks in advance,

Vipin Kumar
Re: Unable to get a single row per result [message #248779 is a reply to message #248777] Sun, 01 July 2007 23:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It would be unless you have something in your data that you don't tell us.
As you didn't tell us what is wrong we can't help you.
I had to make many assumptions to write the query and you didn't even tell us if they are correct.

Regards
Michel
Re: Unable to get a single row per result [message #248790 is a reply to message #248779] Mon, 02 July 2007 00:33 Go to previous messageGo to next message
vipin.kumar
Messages: 3
Registered: June 2007
Location: INDIA
Junior Member
Hi,

I thought i was clear with the data type that was being handled.

I think i am wrong, Lets start this again.

Data: The data consist of tick by tick recordings of National Stock Exchange Futures and Options market. That means it records EACH and Every trade that happens ( Thats why we call it tick by tick ). Usually since i am dealing with the near month future contracts they are very liquid, thereby meaning that on each second more than one trade happens. So every minute consists of more than 60 data points ( usually its more than 100 in number ). The trade starts at 9:55 AM and ends at 3:30 pm.

Query:
select * from TRADEDETAILS_NSE_FO WHERE SYMBOL='NIFTY' AND INSTRUMENT_TYPE='FUTIDX' AND


I think this requires no explanation

abs(MONTHS_BETWEEN(TO_DATE(TRADE_DATE,'YYYYMMDD'),TO_DATE(EXPIRY_DATE,'YYYYMMDD'))) <= 1


This searches for the " Near" month future contract, by calculating the difference between the Trade date and the expiry date.

AND TRADE_DATE >= 20060202 AND TRADE_DATE <= 20060202


This is the range of trade date. In this particular case i have reduced it to a single day.

AND (MOD(TO_NUMBER(TO_CHAR(TO_DATE(TRADE_TIME,'HH24:MI:SS'),'Mi')), 5 ) = 0) 
order by TRADE_TIME 


This divides the trade time in 5 mins time window and records the value at 5th minute.

After running the query i get the result as:
RUN_NO	TRADE_NO	SYMBOL	INSTRUMENT_TYPE	EXPIRY_DATE	OPTION_TYPE	CORPORATE_ACTION_LEVEL	STRIKE_PRICE	TRADE_TIME	TRADED_PRICE	TRADED_QTY	TRADE_DATE

3280	3	NIFTY	FUTIDX	20060223	FF	0	0	09:55:20	2974.25	100	20060202


Now at this particular time 9:55:20 i have more than 4 trades so imagine the size of the result. Ideally i should get only 67 value per trading day.

Now coming to help given by michael:

After i modified the query according to your suggestion, the results which i got was very random. Nothing special to point out except the result's count was reduced to 1000.

Let me rephrase my question again:

After using the Mod function i am getting trades happend " AT" the fifth minute., i.e. trades happened in the duration of 60 secs where as i want only one value of trade at the fifth minute. Hence the query: How to get single row per result?

Thanks for reading such a lengthy query Razz

Help,

Vipin Kumar.
Re: Unable to get a single row per result [message #248803 is a reply to message #248790] Mon, 02 July 2007 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you was not clear.
Neither for the datatype (why not DESC of your table?) nor what you wanted.
Now I have no time to analyze your stuff maybe someone else but what I posted would help you find the solution.
The mod function gives you at the 5th minute, if you want during a 5 minutes period just use BETWEEN the start of this period (the mod expression) and the end of if (the start of the next minus 1 second). Easy.

Regards
Michel
Re: Unable to get a single row per result [message #248958 is a reply to message #248592] Mon, 02 July 2007 22:42 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

description of the table would be good.
As I understand your requirement, you want to pick from multiple rows with the same TRADE_TIME just one RANDOM row to your resultset.

That could be done with something like
SELECT <column_list>
FROM (SELECT a.*,
  ROW_NUMBER() OVER (PARTITION BY trade_time ORDER BY rowid) rn
 FROM TRADEDETAILS_NSE_FO a
 WHERE <your conditions>)
WHERE rn = 1
ORDER BY TRADE_TIME;

If you want to take the row based on any condition (eg. greatest TRADE_NO), just replace ROWID in the ORDER BY clause with that column.
For explanation, search for Analytic Functions in SQL User's Guide and Reference (although you did not specify the Oracle version, I hope you are at least at 9i).
Previous Topic: printing on sam eline in a loop
Next Topic: ORA-00600, Errror while creating index
Goto Forum:
  


Current Time: Fri Dec 09 00:24:10 CST 2016

Total time taken to generate the page: 0.09282 seconds