如何使用postgres查找事件对? [英] How to find pairs of events with postgres?
本文介绍了如何使用postgres查找事件对?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个事件表:
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 |
什么样的查询可以做到这一点?
What sort of query could do this?
推荐答案
您可以使用 窗口函数.除其他外,这些允许您引用查询结果中的下一行/上一行(通过 lead()
和 lag()
).例如:
You can do this pretty easily with a window function. Among other things, these let you reference the next/previous row in a query result (via lead()
and lag()
). For example:
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'
这篇关于如何使用postgres查找事件对?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文