我想从两个事件之间的时间展开解决方案
给定下表，我希望能够找到操作从clear变为create时的时间差。 正如您所看到的，一个项在创建之前可能会清除多次。
此外，事件可能会发生几次为一个用户和我想捕获时间差每一次它发生。
id | user_id | action | created_at
----+---------+--------+----------------------------
5 | 1 | clear | 2016-09-08 11:29:56
6 | 1 | create | 2016-09-08 11:30:00
7 | 2 | create | 2016-09-08 11:30:10
8 | 2 | clear | 2016-09-08 11:30:14
9 | 2 | clear | 2016-09-08 11:30:42
10| 2 | create | 2016-09-08 11:30:43
11| 2 | clear | 2016-09-08 11:30:47
12| 2 | create | 2016-09-08 11:30:55
所需输出（在id 5-6，id 9-10，id 11-12之间变化）:
user_id | time_diff
---------+-----------------
1 | 00:00:03
2 | 00:00:01
2 | 00:00:08
使用下面的代码，我只能提取最近的时间差异，而不是所有的时间差异:
SELECT user_id,
MAX(CASE WHEN action = 'create' THEN created_at END) -
MAX(CASE WHEN action = 'clear' THEN created_at END) AS time_diff
FROM mytable
GROUP BY user_id
HAVING COUNT(*) >= 2
您lead():
select user_id, (next_created_at - created_at)
from (select t.*,
lead(action) over (partition by user_id) as next_action,
lead(created_at) over (partition by user_id) as next_created_at
from t
) t
where action = 'clear' and next_action = 'create'
