代码之家 › 专栏 › 技术社区 › Niels Bech Nielsen

在SQL中配对事件行

oracle sql

Niels Bech Nielsen · 技术社区 · 7 年前

我的问题基本上与问题中的问题相同:

Pairing Send and Receive Data Rows in SQL

但是我没有一个用户/批处理组合可以使事情变得独特,所以基本上是这样的:

KEY   MODULE    EVENTDTTM   ACTION
-------------------------------------
1     A         01/01       SENT
2     A         02/01       RECEIVE
4     A         03/01       SENT
3     A         04/01       RECEIVE
5     A         05/01       SENT
6     A         06/01       SENT
7     A         07/01       RECEIVE
8     A         08/01       SENT

有一些丢失的事件,因此它们不能很好地相加,排序依赖于DTTM,而不是关键顺序,但我需要为每个记录建立一些配对,最后得到如下结果:

MODULE    CUR_KEY    NEXT_KEY   PREV_KEY
-----------------------------------------------
A         1          2          NULL
A         2          NULL       1
A         3          NULL       4
A         4          3          NULL
A         5          (NULL/7)   NULL
A         6          7          NULL
A         7          NULL       6
A         8          NULL       NULL

基本上匹配发送的接收对,以便根据时间戳与最近的接收对链接。5点对7点还是零并不重要,只要其余的都是有序的。8还没有任何匹配,它的对立面(没有发送的接收)也存在。

这略高于我的认知水平,所以任何帮助都会受到赞赏:) 我应该以cur的身份加入receive并子查询一个合适的next,然后与opposite合并还是…?

2 回复 | 直到 7 年前

Kaushik Nayak 7 年前

你可以用 LAG 和 LEAD 功能。

SQL Fiddle

Oracle 11g R2架构设置 :

CREATE TABLE Table1
    ("KEY" int, "MODULE" varchar2(1), "EVENTDTTM" timestamp, "ACTION" varchar2(7))
;

INSERT ALL 
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (1, 'A', '01-Jan-2018 12:00:00 AM', 'SENT')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (2, 'A', '01-Feb-2018 12:00:00 AM', 'RECEIVE')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (4, 'A', '01-Mar-2018 12:00:00 AM', 'SENT')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (3, 'A', '01-Apr-2018 12:00:00 AM', 'RECEIVE')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (5, 'A', '01-May-2018 12:00:00 AM', 'SENT')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (6, 'A', '01-Jun-2018 12:00:00 AM', 'SENT')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (7, 'A', '01-Jul-2018 12:00:00 AM', 'RECEIVE')
    INTO Table1 ("KEY", "MODULE", "EVENTDTTM", "ACTION")
         VALUES (8, 'A', '01-Aug-2018 12:00:00 AM', 'SENT')
SELECT * FROM dual
;

查询1 :

SELECT   KEY , 
         module , 
         action , 
         CASE 
                  WHEN LEAD(action) OVER ( PARTITION BY module ORDER BY eventdttm ) = 'RECEIVE' 
                  AND      action = 'SENT' THEN LEAD(KEY) OVER ( PARTITION BY module ORDER BY eventdttm ) 
         END AS NEXT_KEY , 
         CASE 
                  WHEN LAG(action) OVER ( PARTITION BY module ORDER BY eventdttm ) = 'SENT' 
                  AND      action = 'RECEIVE' THEN LAG(KEY) OVER ( PARTITION BY module ORDER BY eventdttm ) 
         END AS PREV_KEY 
FROM     table1 
ORDER BY KEY

Results :

| KEY | MODULE |  ACTION | NEXT_KEY | PREV_KEY |
|-----|--------|---------|----------|----------|
|   1 |      A |    SENT |        2 |   (null) |
|   2 |      A | RECEIVE |   (null) |        1 |
|   3 |      A | RECEIVE |   (null) |        4 |
|   4 |      A |    SENT |        3 |   (null) |
|   5 |      A |    SENT |   (null) |   (null) |
|   6 |      A |    SENT |        7 |   (null) |
|   7 |      A | RECEIVE |   (null) |        6 |
|   8 |      A |    SENT |   (null) |   (null) |

DDS 7 年前

这是代码(使用自联接):

select a.key as cur_key, a.module, b.key as next_key c.key as prev_key
from tab a full join tab b on a.key=b.key+1  full join tab c on and c.key = a.key-1

where a.action like 'S%'
and b.action like 'R%'

and c.action like 'S%'
and a.action like 'R%'
group by a.key

编辑: 这应该适用于“Greather”

select a.key as cur_key, a.module, b.key as next_key c.key as prev_key
    from tab a
    where a.key=  (select key 
                   from table t
                    where T.id > a.id
                    -- and (eventual binding condition)
                    and t.action like 'S%'
                    and a.action like 'R%' 
                    order by t.id
                    FETCH FIRST ROW ONLY --equivalent to select top 1
                    ) b
    and   c.key=(select key 
                   from table T
                    where T.id < a.id
                    -- and (eventual binding condition)
                    and a.action like 'S%'
                    and t.action like 'R%'
                    order by t.id desc
                    FETCH FIRST ROW ONLY --equivalent to select top 1 (that was my original idea)
                  ) c
    group by a.key