Oracle SQL - Find max values within a range of selected values Oracle Database Tips by Donald Burleson

 

Question:  I want to select the last values within selected one minute periods.  Every minute consists of more than 60 data points.

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

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 minute time windows 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.

After using the Mod function I am getting trades happened " 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?
 

 

Answer:  There may be many ways to do this, but I would create a temporary table to hold the time and value.  Then, you could use a "max" to get the top value for the minute period.

You can use a global temporary table or use the SQL-99 "with" clause to do this.

 

Laurent Schneider has an elegant SQL solution to this problem of selecting the "last" value within a five minute range:

 

select
   max(run_no) keep (dense_rank first order by trade_time,run_no,trade_no) run_no,
   max(trade_no) keep (dense_rank first order by trade_time,run_no,trade_no) trade_no,
   max(SYMBOL) keep (dense_rank first order by trade_time,run_no,trade_no) SYMBOL,
   max(INSTRUMENT_TYPE) keep (dense_rank first order by trade_time,run_no,trade_no) INSTRUMENT_TYPE,
   max(EXPIRY_DATE) keep (dense_rank first order by trade_time,run_no,trade_no) EXPIRY_DATE,
   max(OPTION_TYPE) keep (dense_rank first order by trade_time,run_no,trade_no) OPTION_TYPE,
   max(CORPORATE_ACTION_LEVEL) keep (dense_rank first order by trade_time,run_no,trade_no) CORP_ACT_LEVEL,
   max(STRIKE_PRICE) keep (dense_rank first order by trade_time,run_no,trade_no) STRIKE_PRICE,
   max(TRADE_TIME) keep (dense_rank first order by trade_time,run_no,trade_no) TRADE_TIME,
   max(TRADED_PRICE) keep (dense_rank first order by trade_time,run_no,trade_no) TRADED_PRICE,
   max(TRADED_QTY) keep (dense_rank first order by trade_time,run_no,trade_no) TRADED_QTY,
   max(TRADE_DATE) keep (dense_rank first order by trade_time,run_no,trade_no) TRADE_DATE
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
group by trunc(to_char(to_date(TRADE_TIME,'HH24:MI:SS'),'Mi')*5)
order by TRADE_TIME;

Laurent Schneider is the author of the book "Oracle Advanced SQL Programming".