其他组的分析函数LAG

I have a query that goes like this.

我有一个像这样的查询。

select m.*, lag (kod) over (partition by drv order by rn) from m;

The DRV column has only 2 possible values. The query obviously returns the "lagged" value from the same drv-group as the current row.

DRV列只有两个可能的值。查询显然返回与当前行相同的drv-group的“滞后”值。

However, I need to select the value of the KOD column which has maximum possible RN smaller than the RN of the current row and the DRV different than the DRV of the current row. Obviously I can write a correlated subquery, but I am looking for something more efficient.

但是,我需要选择KOD列的值,该列的最大可能RN小于当前行的RN,DRV不同于当前行的DRV。显然我可以编写一个相关的子查询,但我正在寻找更高效的东西。

Any advice is much appreciated.

任何建议都非常感谢。

EDIT

As mentioned in the comments, some sample data will make the question clearer.

正如评论中所提到的,一些样本数据将使问题更加清晰。

RN      DRV  KOD    LAG(KOD)
1365    lf   115892 115786
1366    zon  1159   1158
1367    lf   115927 115892
1368    zon  116    1159
1369    zon  1160   116
1370    lf   116029 115927
1371    lf   116043 116029

This is the result of the query given above. I am only interested in the records where DRV='lf'. For example for RN=1367 we have LAG(KOD)=115892 because this is the previous record in the group. Instead of taking the previous records from the same partition/group, I need a query which will return the previous record from the other group - in the case of RN=1367 it must return 1159. This is because for the record in question DRV='lf' so I want to look up KOD in the other partition which is DRV='zon' and pick LAG over order by RN, and this is the record with RN=1366. So, the rule must look like:

这是上面给出的查询的结果。我只对DRV ='lf'的记录感兴趣。例如,对于RN = 1367,我们有LAG(KOD)= 115892,因为这是该组中的先前记录。我不需要从同一个分区/组中获取先前的记录,而是需要一个查询,该查询将从另一个组返回先前的记录 - 在RN = 1367的情况下,它必须返回1159.这是因为对于有问题的记录DRV = 'lf'所以我想在另一个分区中查找KOD,即DRV ='zon'并按RN顺序选择LAG,这是RN = 1366的记录。因此,规则必须如下:

RN      DRV  KOD    NEW_LAG(KOD)
1365    lf  115892  ?
1366    zon 1159    ?
1367    lf  115927  1159
1368    zon 116     ?
1369    zon 1160    ?
1370    lf  116029  1160
1371    lf  116043  1160

Note that I am not interested in the results from records where DRV='zon' that's why I have put a question mark there.

请注意,我对DRV ='zon'的记录结果不感兴趣,这就是为什么我在那里放了一个问号。

1 个解决方案

#1

The LAST_VALUE IGNORE NULL do the job for you

LAST_VALUE IGNORE NULL为您完成工作

with dat as (
select 1365 RN ,   'lf' DRV, 115892 KOD  from dual union all  
select 1366 RN ,   'zon' DRV, 1159 KOD  from dual union all 
select 1367 RN ,   'lf' DRV, 115927 KOD  from dual union all 
select 1368 RN ,   'zon' DRV,116 KOD  from dual union all 
select 1369 RN ,   'zon' DRV, 1160 KOD  from dual union all 
select 1370 RN ,   'lf' DRV, 116029 KOD  from dual union all 
select 1371 RN ,   'lf' DRV, 116043 KOD  from dual),
dat2 as (
select 
  RN, DRV, KOD, 
  LAST_VALUE(case when DRV = 'zon' then  KOD end IGNORE NULLS) over ( order by RN) as LAG_KOD_ZON,
  lag(KOD) over (order by RN) as LAG_KOD 
from dat
)
select  
 RN, DRV, KOD,
 CASE WHEN DRV = 'lf' THEN LAG_KOD_ZON end as LAG_KOD
from dat2;

results in

       RN DRV        KOD    LAG_KOD
---------- --- ---------- ----------
      1365 lf      115892            
      1366 zon       1159            
      1367 lf      115927       1159 
      1368 zon        116            
      1369 zon       1160            
      1370 lf      116029       1160 
      1371 lf      116043       1160