Oracle中使用Lead的分区

Question

我被一个问题卡住了，无法使用分区实现超前/滞后。

下面是示例和预期结果

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);

我需要返回如下所示的值：

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

要嵌入的逻辑是：

对于每个记录，需要获取相反方向的和现有的TrdTime > Other record TrdTime。例如:对于键1237，方向为S，TrdTime为7/15/2013 6:18:00 PM此记录返回了以下记录: 1240和1241都具有相对的'B‘和现有记录TrdTime >这两个记录。但是选择了1241的TrdTime，因为它是按最近的和最高的TrdTime排序的。

如何实现此功能。

我正在考虑使用LEAD函数和分区来做这件事。

我不能使用游标，因为表没有索引，并且有超过500万条记录。我不想自己加入也不想，这是非常耗时的。

有什么建议请提出来。

Answer 1

作为其中一种方法，我们可以执行以下操作：

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

结果：

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

使用dens_rank()分析函数将记录分组：

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

结果：

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

然后我们选择Next_price作为最近组的max(price)，这包括相反的方向。