根据同一表的第二列从单列中查找配对

Question

我有以下场景

来源表:

Col1 Col2 Time  
I1   CRR   T0  
I1   CRH   T1  
I1   CRH   T2  
I1   CRR   T3  
I1   CRH   T4  
I1   CRR   T5  
I1   CRH   T6  
I2   CRH   T7  
I2   CRR   T8  

这里的值对是(CRH,CRR)—CRH是开始事件，CRR是结束事件。我需要在相应的开始事件之前删除所有结束事件(这是根据时间列决定的)，并捕获有效的开始事件和结束事件对。如果在结束事件之前有多个开始事件，那么需要选择最早的一个来建立pair。下面是期望的结果

Col1 Col2 Time Col3 Col4  
I1   CRH   T1  CRR   T3  
I1   CRH   T4  CRR   T5  
I1   CRH   T6   -    -  (since no corresponding end event - this is fine)  
I2   CRH   T7  CRR   T8

我正在使用DB2，任何帮助将不胜感激!

Answer 1

如果您使用的是最新版本的db2，那么您有lag()和lead()函数。

如果是，试试这个:

select col1, col2, time, nextcol2, nexttime
from (select t.*,
             lead(col2) over (partition by col1 order by time) as nextcol2
             lead(time) over (partition by col1 order by time) as nexttime
      from t
     ) t
where not(col2 = 'CRR' and nextcol2 = 'CRH')

如果你没有lead()函数，你可以用相关子查询做类似的事情。

这个评论很清楚地说明了你想要什么。在给定的开始之后，你正在寻找下一个结束。为此，我使用相关子查询来获取下一个时间。下面是查询的结构:

select t.*, tend.col2, tend.time
from (select t.*,
             (select MIN(time) from t t2 where t.col1 = t2.col1 and t2.time > t.time and t2.col2 = 'CRR'
             ) endtime
      from t
      where col2 = 'CRH'
     ) t left outer join
     t tend
     on t.col1 = tend.col1 and t.time = tend.endtime