Pairing event rows in SQL

Question

I basically have the same question as specified in the question:

Pairing Send and Receive Data Rows in SQL

But I do not have a user/batch combination that could make things unique, so basically having something like this:

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

There are missing events, so they don't add up nicely and the ordering relies on the DTTM, not the key order, but I need to establish some pairing for each record, ending up with something like:

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

Basically matching the sent-receive pairs, such that they link with the closest one according to the timestamp. Whether 5 points to 7 or NULL is not important as long as the rest is in order. 8 does not have any match (yet), and its opposite (a receive without sent) also exists.

This is slightly above my cognitive level, so any help appreciated :) Should I join receive as cur and subquery a suitable next and then union with the opposite or...?

Answer 1

You could use LAG and LEAD functions.

SQL Fiddle

Oracle 11g R2 Schema Setup:

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
;

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

Answer 2

this is the code (using self joins):

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

EDIT: this should work for '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