So say I have the following:
Timestamp | Event_Name |
---|---|
10:47:00 | RESUME |
10:37:52 | SUSPEND |
10:26:20 | RESUME |
09:48:27 | SUSPEND |
09:39:13 | RESUME |
09:15:38 | SUSPEND |
09:11:42 | RESUME |
I want to be able to pair them as such:
Suspend | Resume |
---|---|
null | 10:47:00 |
10:37:52 | 10:26:20 |
09:48:27 | 09:39:13 |
09:15:38 | 09:11:42 |
If each pair had another field to group on I would use a listagg window function but I can’t. Lag function doesn’t work either.
Advertisement
Answer
As having a DBMS containing window function, you can use the following query
x
WITH t AS
(
SELECT CASE
WHEN Event_Name = 'SUSPEND' THEN
Timestamp
END AS Suspend,
CASE
WHEN Event_Name = 'RESUME' THEN
Timestamp
END AS Resume,
SUM(CASE
WHEN Event_Name = 'SUSPEND' THEN
1
ELSE
0
END) OVER(PARTITION BY Event_Name ORDER BY Timestamp) AS rn_suspend,
SUM(CASE
WHEN Event_Name = 'RESUME' THEN
1
ELSE
0
END) OVER(PARTITION BY Event_Name ORDER BY Timestamp) AS rn_resume
FROM t
)
SELECT s.suspend, r.resume
FROM (SELECT * FROM t WHERE resume IS NOT NULL) AS r
FULL JOIN (SELECT * FROM t WHERE suspend IS NOT NULL) AS s
ON s.rn_suspend = r.rn_resume
ORDER BY r.rn_resume DESC, s.rn_suspend DESC
where SUM
window function is used to count the each switch for the Event_Name