如何获得两行之间的时差？

用户名

我有这样的桌子

Task    Event               Time
2       opened          "2018-12-14 16:23:49.058707+01"
2       closed          "2018-12-14 16:24:49.058707+01"
3       opened          "2018-12-14 16:25:49.058707+01"
3       Interrupted     "2018-12-14 16:26:49.058707+01"
3       closed          "2018-12-14 16:27:49.058707+01"

我需要像这样从表中获取数据

Task    Difference
2           1

仅在打开和关闭两个事件时才应提取数据。如果只有2个事件，则应采用abs（关闭-打开）之间的时差。

我无法根据事件列找出解决方法

瓦姆西·普拉巴拉（Vamsi Prabhala）

这可以使用条件聚合来完成。

select task
      ,max(case when event = 'closed' then time end) - max(case when event = 'opened' then time end) as diff 
--The aggregation can also be expressed using FILTER as shown below
--,max(time) FILTER(where event = 'closed') - max(time) FILTER (where event = 'opened') 
from tbl
group by task
having count(distinct case when event in ('opened','closed') then event end) = 2
and count(distinct event) = 2

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。