如何使用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'