我的 PostgreSQL 数据库中有大约 100 万个事件,其格式如下:
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) 对,因为这些是紧邻间隙的事件。
我不在乎查询是否慢,即对于 100 万条记录,如果需要一个小时左右就可以了。然而,数据集将继续增长,所以希望如果它很慢,它也能正常扩展。
我也有 MongoDB 中的数据。
执行此查询的最佳方式是什么?
您可以使用lag() http://www.postgresql.org/docs/current/static/functions-window.html根据stream_id对分区进行窗口函数,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");
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)