Partition using Lead in Oracle

Question

I am stuck on a problem to implement LEAD/LAG with partition.

Below is the example and expected Result

create table trd(
     key number,
     book number,
     prd_key number,
     direction varchar2(2),
     trdtime date,
     price number)




insert into trd values(1234,115,133864,'B','17-07-2013 18:18:00',108.859);
insert into trd values(1235,115,133864,'S','17-07-2013 18:18:00',108.859);
insert into trd values(1245,115,133864,'S','17-07-2013 18:18:00',108.859);
insert into trd values(1236,115,133864,'B','15-07-2013 18:18:00',108.872);
insert into trd values(1237,115,133864,'S','15-07-2013 18:18:00',108.866);
insert into trd values(1247,115,133864,'S','15-07-2013 18:18:00',108.866);
insert into trd values(1238,115,133864,'S','14-07-2013 18:18:00',107.86);
insert into trd values(1239,115,133864,'S','14-07-2013 18:17:00',108.86);
insert into trd values(1240,115,133864,'B','14-07-2013 18:12:00',109.86);
insert into trd values(1241,115,133864,'B','14-07-2013 18:17:00',110.86);

I need to return the value something like 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 to embed is :

For each record, Need to get the OPPOSITE direction's and Existing TrdTime > Other records TrdTime. For example: for key 1237, the Direction is S and TrdTime is 7/15/2013 6:18:00 PM. There are following records returned for this record: 1240 and 1241 both having opposite side 'B' and existing record TrdTime > these two records. But the TrdTime of 1241 is selected since it is ordered by nearest and highest TrdTime.

How Can I implement this functionality.

I was thinking of doing it using LEAD function and partition.

I cannot use Cursors since tables are not indexed and there are over 5 mil records. I do not want to do self join either sicne it is very time consuming.

Any suggestions please.

Answer 1

As one of the approaches, we can do the following:

with cte(key, book, prd_key, direction, trdtime, price, grp) as(
  select t.*
      , dense_rank() over(order by t.trdtime desc)
    from trd t
)
select q.key
     , q.book
     , q.prd_key
     , q.direction
     , q.trdtime
     , q.price
     , grp
     , (select max(c.price)
           from cte c
          where q.direction <> c.direction
            and c.grp = (select min(grp) 
                          from cte l 
                         where l.direction <> q.direction 
                           and l.grp > q.grp
                         )
        ) as next_price
  from 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 is divided into groups using dens_rank() analytic function:

  select t.*
       , dense_rank() over(order by t.trdtime desc)
    from 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 we select Next_price as max(price) of a nearest group, which include opposite direction.