在巨大的事件流中发现差距？

Question

我在PostgreSQL数据库中有大约一百万个具有以下格式的事件：

id        |   stream_id     |  timestamp
----------+-----------------+-----------------
1         |   7             |  ....
2         |   8             |  ....

大约有50,000个唯一流。

我需要找到所有两个事件之间的时间间隔都超过特定时间段的所有事件。换句话说，我需要找到在特定时间段内没有事件的事件对。

例如：

a b c d   e     f              g         h   i  j k
| | | |   |     |              |         |   |  | |

                \____2 mins____/

在这种情况下，我想找到对（f，g），因为这些是紧挨着缺口的事件。

我不在乎查询是否慢，即在一百万条记录上花费一个小时左右就可以了。但是，数据集将保持增长，因此，如果数据集速度缓慢，则可以合理地扩展。

我在MongoDB中也有数据。

执行此查询的最佳方法是什么？

Answer 1

您可以使用lag()window函数在分区上通过stream_id（由时间戳排序）来执行此操作。该lag()函数使您可以访问分区中的前几行；没有滞后值，它是前一行。因此，如果stream_id上的分区按时间排序，则上一行是该stream_id的上一个事件。

SELECT stream_id, lag(id) OVER pair AS start_id, id AS end_id,
       ("timestamp" - lag("timestamp") OVER pair) AS diff
FROM my_table
WHERE diff > interval '2 minutes'
WINDOW pair AS (PARTITION BY stream_id ORDER BY "timestamp");