如何使用postgres查找成对的事件？
如何使用postgres查找成对的事件？
提问于 2018-06-01 18:43:27
我有一个事件表：
ts | user | reason
----------------------------+--------+--------
2018-06-01 10:44:15.52+01 | 359999 | START
2018-06-01 10:44:29.521+01 | 359999 | STOP
2018-06-01 10:44:43.52+01 | 359998 | START
2018-06-01 10:44:55.52+01 | 359999 | START
2018-06-01 10:44:59.521+01 | 359998 | STOP
2018-06-01 10:45:07.52+01 | 359999 | STOP
2018-06-01 10:46:16.52+01 | 359999 | START我想找出成对的事件：
user | start | stop
--------+----------------------------+----------------------------
359999 | 2018-06-01 10:44:15.52+01 | 2018-06-01 10:44:29.521+01
359998 | 2018-06-01 10:44:43.52+01 | 2018-06-01 10:44:59.521+01
359999 | 2018-06-01 10:44:55.52+01 | 2018-06-01 10:45:07.52+01
359999 | 2018-06-01 10:46:16.52+01 |什么样的查询可以做到这一点？
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-14 06:00:57
使用window function可以很容易地做到这一点。除了其他功能之外，它们还允许您引用查询结果中的下一行/上一行(通过lead()和lag())。例如：
SELECT "user", ts AS start, next_ts AS stop
FROM (
SELECT *, lead(ts) OVER (PARTITION BY "user" ORDER BY ts) AS next_ts
FROM events
WHERE reason IN ('START', 'STOP')
) AS ts_pairs
WHERE reason = 'START'Stack Overflow用户
发布于 2018-06-01 18:45:14
我刚把它弄好。有没有更有效的方法？
select imei, reason, ts AS start, (
select ts
from events as stops
where stops.ts > starts.ts
and reason = 'STOP'
and stops.user = starts.user
order by ts desc
limit 1
) as stop
from events as starts
where reason = 'START'
order by ts
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50641682
复制相关文章
相似问题
腾讯云开发者
