Oracle SQL在不同组中的领先滞后 [英] Oracle SQL lead lag across different group

问题描述

假设我有下表.他们的关键只是concat P1，P2，P3.我想比较每天的钥匙.例如，从第1天到第2天，删除abc并添加abe，aby.

Suppose I have the below table. They key is just concat P1, P2, P3. I want to compare between key for each day. for example, from day 1 to day2, abc is removed and abe, aby is added.

P1  P2  P3  DAY KEY

a   b   c   1   abc
a   b   e   2   abe
a   b   y   2   aby
a   b   x   3   abx
a   b   c   3   abc

预期结果集:

KEY OPERATION DAY

abc  ADD      1
abe  ADD      2
aby  ADD      2
abc  REMOVE   2
abx  ADD      3
abc  ADD      3
abe  REMOVE   3
aby  REMOVE   3

如果日期不是连续的，该怎么办.例如:

And what if the day is not sequential. For example:

P1  P2  P3  DAY  KEY

a   b   c   1    abc
a   b   e   2    abe
a   b   y   2    aby
a   b   x   5    abx
a   b   c   5    abc

预期结果是:

KEY OPERATION DAY

abc  ADD      1
abe  ADD      2
aby  ADD      2
abc  REMOVE   2
abx  ADD      5
abc  ADD      5
abe  REMOVE   5
aby  REMOVE   5

推荐答案

您可以在没有窗口功能的情况下执行此操作，

You can do this without window functions, if you want:

select key, 'add', day
from t
where not exists (select 1
                  from t t2
                  where t2.key = t.key and t2.day = t.day - 1
                 )
union all
select key, 'remove', day + 1
from t
where not exists (select 1
                  from t t2
                  where t2.key = t.key and t2.day = t.day + 1
                 )

这篇关于Oracle SQL在不同组中的领先滞后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！