sql - 按 float 日期范围分组

Question

我正在使用 PostgreSQL 9.2。
我的表格包含一些设备停止服务的时间。

+----------+----------+---------------------+
| event_id |  device  |         time        |
+----------+----------+---------------------+
|        1 | Switch4  | 2013-09-01 00:01:00 |
|        2 | Switch1  | 2013-09-01 00:02:30 |
|        3 | Switch10 | 2013-09-01 00:02:40 |
|        4 | Switch51 | 2013-09-01 03:05:00 |
|        5 | Switch49 | 2013-09-02 13:00:00 |
|        6 | Switch28 | 2013-09-02 13:01:00 |
|        7 | Switch9  | 2013-09-02 13:02:00 |
+----------+----------+---------------------+

我希望按 +/-3 分钟的时差对行进行分组，如下所示:

+----------+----------+---------------------+--------+
| event_id |  device  |         time        |  group |
+----------+----------+---------------------+--------+
|        1 | Switch4  | 2013-09-01 00:01:00 |      1 |
|        2 | Switch1  | 2013-09-01 00:02:30 |      1 |
|        3 | Switch10 | 2013-09-01 00:02:40 |      1 |
|        4 | Switch51 | 2013-09-01 03:05:00 |      2 |
|        5 | Switch49 | 2013-09-02 13:00:00 |      3 |
|        6 | Switch28 | 2013-09-02 13:01:00 |      3 |
|        7 | Switch9  | 2013-09-02 13:02:00 |      3 |
+----------+----------+---------------------+--------+

我尝试使用窗口函数来实现它，但是在子句中

[ RANGE | ROWS ] BETWEEN frame_start AND frame_end, where frame_start and frame_end can be one of UNBOUNDED PRECEDING value PRECEDING CURRENT ROW value FOLLOWING UNBOUNDED FOLLOWING,

value must be an integer expression not containing any variables, aggregate functions, or window functions

因此，考虑到这一点，我无法指明时间间隔。现在我怀疑窗口函数能否解决我的问题。你能帮帮我吗？

Answer 1

SQL Fiddle

select
    event_id, device, ts,
    floor(extract(epoch from ts) / 180) as group
from t
order by ts

可以使用窗口函数使组号成为从 1 开始的序列，但这是一个不小的成本，我不知道是否有必要。就是这个

select
    event_id, device, ts,
    dense_rank() over(order by "group") as group
from (
    select
        event_id, device, ts,
        floor(extract(epoch from ts) / 180) as group
    from t
) s
order by ts

time 是保留字。选择另一个作为列名。