postgresql - 在 Postgres 中分组事件

Question

我有一个由网站上的用户事件生成的事件表:

timestamp | name
7:00 AM   | ...
7:01 AM   | ...
7:02 AM   | ...
7:30 AM   | ...
7:31 AM   | ...
7:32 AM   | ...
8:01 AM   | ...
8:03 AM   | ...
8:05 AM   | ...
8:08 AM   | ...
8:09 AM   | ...

我想聚合事件以提供用户何时处于事件状态的 View 。我将事件定义为事件在 +/- 2 分钟内的时间段。对于以上那意味着:

from    | till
7:00 AM | 7:02 AM
7:30 AM | 7:32 AM
8:01 AM | 8:05 AM
8:08 AM | 8:09 AM

编写将在该方法中聚合的查询的最佳方式是什么？是否可以通过 WINDOW 函数或自连接或是否需要 PL/SQL？

Answer 1

使用两个窗口函数:一个用于计算连续事件之间的间隔(间隙)，另一个用于查找一系列小于或等于 2 分钟的间隙:

select arr[1] as "from", arr[cardinality(arr)] as "till"
from (  
    select array_agg(timestamp order by timestamp)  arr
    from (
        select timestamp, sum((gap > '2m' )::int) over w
        from (
            select timestamp, coalesce(timestamp - lag(timestamp) over w, '3m') gap
            from events
            window w as (order by timestamp)
            ) s
        window w as (order by timestamp)
        ) s
    group by sum
    ) s

   from   |   till   
----------+----------
 07:00:00 | 07:02:00
 07:30:00 | 07:32:00
 08:01:00 | 08:05:00
(3 rows)        

Test it here.