两个事件之间的时间 [英] Time between two events

问题描述

如果有一个跟踪用户某些事件的表。

If have got a table that tracks some events of a user.

 id | user_id | action |         created_at         
----+---------+--------+----------------------------
  5 |       1 | create | 2016-09-08 11:29:56.325691
  6 |       1 | clear  | 2016-09-08 11:30:00.08604
  7 |       2 | create | 2016-09-08 11:30:10.12857
  8 |       2 | clear  | 2016-09-08 11:30:14.238685
  9 |       3 | create | 2016-09-08 11:30:42.192843

总会有创建操作，然后可能执行 clear 操作。

There is always a create action that might be followed by a clear action.

CREATE TYPE user_actions AS ENUM ('create', 'clear');

现在我想查询这两个动作之间的时间差，以获取<$ c之间的time_diff $ c>创建和清除。

Now I want to query the time difference between these two actions to get the time_diff between the create and clear of a user.

现在您可以假设用户没有多个条目（例如，至少一个 create 和最大另一个清除）。

For now you can assume that the user doesn't have multiple entries (e.g. at least one create and maximal another clear).

我想要这样的结果：

 user_id |    time_diff    
---------+-----------------
       1 | 00:00:03.760349
       2 | 00:00:04.110115

推荐答案

您可以使用以下查询：

SELECT user_id,
       MAX(CASE WHEN action = 'clear' THEN created_at END) -
       MAX(CASE WHEN action = 'create' THEN created_at END) AS time_diff 
FROM mytable
GROUP BY user_id 
HAVING COUNT(*) = 2

HAVING 子句过滤出 user_id 仅包含 create 动作的组，例如OP中 id = 9 的记录。

The HAVING clause filters out user_id groups containing only a create action, like record with id=9 in the OP.

此处演示 >

这篇关于两个事件之间的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！