oracle - Min(), Max() within date subset -

oracle - Min(), Max() within date subset -

not sure if title fits, here's problem: have next table:

create table opentrades( accountnumber number, snapshottime date, ticket number, opentime date, tradetype varchar2(4), tradesize number, tradeitem char(6), openprice number, currentask number, currentbid number, tradesl number, tradetp number, tradeswap number, tradeprofit number ); alter table opentrades add together constraint opentrades_pk primary key (accountnumber, snapshottime, ticket) using index tablespace mynnidx;

for every (snapshottime, account), want select min(openprice), max(openprice) in such way resultimg min , max relative past only, respect snapshottime. instance, possible (account, tradeitem) pair, may have 10 records with, say, snapshottime=10-jun , openprice between 0.9 , 2.0, 10 more records snapshottime=11-jun , openprice between 1.0 , 2.1, 10 more records snapshottime=12-jun , openprice between 0.7 , 1.9. in such scenario, sought query should homecoming this:

accountnumber snapshottime mymin mymax ------------- ------------ ----- ----- 1234567 10-jun 0.9 2.0 1234567 11-jun 0.9 2.1 1234567 12-jun 0.7 2.1

i've tried this, returns min() , max() within same snapshottime:

select accountnumber, snapshottime, tradeitem, min(openprice), max(openprice) opentrades grouping accountnumber, snapshottime, tradeitem

any help appreciated.

you can utilize analytic versions of min() , max() this, along windowing clauses:

select distinct accountnumber, snapshottime, tradeitem, min(openprice) on (partition accountnumber, tradeitem order snapshottime, openprice rows between unbounded preceding , current row) min_openprice, max(openprice) on (partition accountnumber, tradeitem order snapshottime, openprice desc rows between unbounded preceding , current row) max_openprice opentrades order accountnumber, snapshottime, tradeitem; accountnumber snapshottime tradeitem min_openprice max_openprice ------------- ------------ --------- ------------- ------------- 1234567 10-jun-14 x .9 2 1234567 11-jun-14 x .9 2.1 1234567 12-jun-14 x .7 2.1

sql fiddle.

the partition by calculates value current accountnumber , tradeitem, within subset of rows based on rows between clause; order by means looks @ rows in previous snapshot , lowest (for min) or highest (for max, because of desc) in current snapshot, when calculating appropriate min/max each row.

the analytic result calculated every row. if run without distinct see base of operations info plus same min/max each snapshot (fiddle). don't want of varying info can suppress duplication distinct, or making query row_number() filter on, etc.

oracle range max min