在MySQL中以LIFO方式查找经过的时间

Question

我们得到了一个表，目标是找出给定任务的运行时间。

Task | Event | Timestamp
-------------------------
1    | begin |    1
1    | end   |    2
2    | begin |    3
2    | end   |    4
2    | begin |    5
2    | begin |    6
2    | begin |    7
2    | end   |    8
2    | end   |    9
2    | end   |    10

查询结果应该如下所示：

Task | Begin | End | Elapsed
----------------------------
1    |   1   |  2  |   1
2    |   3   |  4  |   1
2    |   5   |  10 |   5
2    |   6   |  9  |   3
2    |   7   |  8  |   1

对于给定的任务，最后一个开始应该与第一个结束相关联。因此对于任务2，5＝>10、6＝>9和7＝>8.我们可以假设，对于给定的任务，有相等的开始和结束事件。有人知道我们如何使用MySQL来实现这一点吗？

Answer 1

SELECT task, timestamp1 `Begin`, timestamp2 `End`, timestamp2 - timestamp1 Elapsed
FROM (
SELECT (@event1 := CASE WHEN `event` = 'begin'
THEN @event1 + 1
ELSE @event1 - 1 
END) + (`event` = 'end') event_num,
@cluster1 := @cluster1 + (`event` = 'begin') * (@event1 = 1) cluster,
task, 
`event` event1,
`timestamp` timestamp1
FROM test
CROSS JOIN (SELECT @cluster1:=0, @event1:=0) init_vars
ORDER BY task, `timestamp`
) t1
JOIN (
SELECT (@event2 := CASE WHEN `event` = 'begin'
THEN @event2 + 1
ELSE @event2 - 1 
END) + (`event` = 'end') event_num,
@cluster2 := @cluster2 + (`event` = 'begin') * (@event2 = 1) cluster,
task, 
`event` event2,
`timestamp` timestamp2
FROM test
CROSS JOIN (SELECT @cluster2:=0, @event2:=0) init_vars
ORDER BY task, `timestamp`
) t2 USING (event_num, cluster, task)
WHERE event1 = 'begin'
AND event2 = 'end'

https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=5fd4d962794c8b31e5dd9d626410895f

PS。在版本8+上给出错误的输出。