analytic functions - Partition using Lead in Oracle -

i stuck on problem implement lead/lag partition.

below example , expected result

create table trd(      key number,      book number,      prd_key number,      direction varchar2(2),      trdtime date,      price number)     insert trd values(1234,115,133864,'b','17-07-2013 18:18:00',108.859); insert trd values(1235,115,133864,'s','17-07-2013 18:18:00',108.859); insert trd values(1245,115,133864,'s','17-07-2013 18:18:00',108.859); insert trd values(1236,115,133864,'b','15-07-2013 18:18:00',108.872); insert trd values(1237,115,133864,'s','15-07-2013 18:18:00',108.866); insert trd values(1247,115,133864,'s','15-07-2013 18:18:00',108.866); insert trd values(1238,115,133864,'s','14-07-2013 18:18:00',107.86); insert trd values(1239,115,133864,'s','14-07-2013 18:17:00',108.86); insert trd values(1240,115,133864,'b','14-07-2013 18:12:00',109.86); insert trd values(1241,115,133864,'b','14-07-2013 18:17:00',110.86); 

i need return value this:

key    book    prd_key dir  trdtime             price       nextprice 1234    115    133864    b  7/17/2013 6:18:00 pm   108.859  108.866 1235    115    133864    s  7/17/2013 6:18:00 pm   108.859  108.872 1245    115    133864    s  7/17/2013 6:18:00 pm   108.859  108.872 1236    115    133864    b  7/15/2013 6:18:00 pm   108.872  108.86 1237    115    133864    s  7/15/2013 6:18:00 pm   108.866  110.86 1247    115    133864    s  7/15/2013 6:18:00 pm   108.866  110.86 1238    115    133864    s  7/14/2013 6:18:00 pm   107.86   110.86 1239    115    133864    s  7/14/2013 6:17:00 pm   108.86   109.86 1240    115    133864    b  7/14/2013 6:12:00 pm   109.86   null 1241    115    133864    b  7/14/2013 6:17:00 pm   110.86   null 

the logic embed :

for each record, need opposite direction's , existing trdtime > other records trdtime. example: key 1237, direction s , trdtime 7/15/2013 6:18:00 pm. there following records returned record: 1240 , 1241 both having opposite side 'b' , existing record trdtime > these 2 records. trdtime of 1241 selected since ordered nearest , highest trdtime.

how can implement functionality.

i thinking of doing using lead function , partition.

i cannot use cursors since tables not indexed , there on 5 mil records. not want self join either sicne time consuming.

any suggestions please.

as 1 of approaches, can following:

with cte(key, book, prd_key, direction, trdtime, price, grp) as(   select t.*       , dense_rank() over(order t.trdtime desc)     trd t ) select q.key      , q.book      , q.prd_key      , q.direction      , q.trdtime      , q.price      , grp      , (select max(c.price)            cte c           q.direction <> c.direction             , c.grp = (select min(grp)                            cte l                           l.direction <> q.direction                             , l.grp > q.grp                          )         ) next_price   cte q 

result:

key   book   prd_key  direction  trdtime              price    next_price  ---------------------------------------------------------------------------- 1234  115    133864   b          17.07.13 6:18:00 pm  108,859  108,866  1235  115    133864   s          17.07.13 6:18:00 pm  108,859  108,872  1245  115    133864   s          17.07.13 6:18:00 pm  108,859  108,872  1236  115    133864   b          15.07.13 6:18:00 pm  108,872  107,86  1237  115    133864   s          15.07.13 6:18:00 pm  108,866  110,86  1247  115    133864   s          15.07.13 6:18:00 pm  108,866  110,86  1238  115    133864   s          14.07.13 6:18:00 pm  107,86   110,86  1239  115    133864   s          14.07.13 6:17:00 pm  108,86   109,86  1241  115    133864   b          14.07.13 6:17:00 pm  110,86   null  1240  115    133864   b          14.07.13 6:12:00 pm  109,86   null 

sqlfiddle demo

the records divided groups using dens_rank() analytic function:

  select t.*        , dense_rank() over(order t.trdtime desc)     trd t 

result:

key   book   prd_key  direction  trdtime              price    next_price  grp ---------------------------------------------------------------------------- 1234  115    133864   b          17.07.13 6:18:00 pm  108,859  108,866     1 1235  115    133864   s          17.07.13 6:18:00 pm  108,859  108,872     1 1245  115    133864   s          17.07.13 6:18:00 pm  108,859  108,872     1 1236  115    133864   b          15.07.13 6:18:00 pm  108,872  107,86      2 1237  115    133864   s          15.07.13 6:18:00 pm  108,866  110,86      2 1247  115    133864   s          15.07.13 6:18:00 pm  108,866  110,86      2 1238  115    133864   s          14.07.13 6:18:00 pm  107,86   110,86      3 1239  115    133864   s          14.07.13 6:17:00 pm  108,86   109,86      4 1241  115    133864   b          14.07.13 6:17:00 pm  110,86   null        4 1240  115    133864   b          14.07.13 6:12:00 pm  109,86   null        5 

then select next_price max(price) of nearest group, include opposite direction.