PostgreSQL 用于聚合窗口

Question

我需要聚合一个窗口(在PostgreSQL中(：

我有这个表"位置时间戳"：

seq | location     | timestamp
-------------------------------------
1   | home         | 2018-01-02 18:00
2   | home         | 2018-01-03 08:00
3   | work         | 2018-01-03 09:00
4   | work         | 2018-01-03 16:00
5   | home         | 2018-01-03 17:00
6   | elsewhere    | 2018-01-03 18:00
7   | elsewhere    | 2018-01-03 20:00
8   | home         | 2018-01-03 21:00
9   | home         | 2018-01-03 22:00
10  | home         | 2018-01-03 23:00

我想要这个结果：

location  | min_seq | max_seq | min_timestamp    | max_timestamp
-------------------------------------------------------------------
home      | 1       | 2       | 2018-01-02 18:00 | 2018-01-03 08:00
work      | 3       | 4       | 2018-01-03 09:00 | 2018-01-03 16:00
home      | 5       | 5       | 2018-01-03 17:00 | 2018-01-03 17:00
elsewhere | 6       | 7       | 2018-01-03 18:00 | 2018-01-03 20:00
home      | 8       | 10      | 2018-01-03 21:00 | 2018-01-03 23:00

我尝试使用窗口函数来实现这一点，但这会导致 10 行(表中的行数(而不是所需的聚合 5。

到目前为止，我的尝试(无效(：

SELECT
   location,
   MIN(seq) OVER w min_seq,
   MAX(seq) OVER w max_seq,
   MIN("timestamp") OVER w min_timestamp,
   MAX("timestamp") OVER w max_timestamp
FROM locationtimestamp
WINDOW w AS (PARTITION BY location ORDER BY seq ASC)
ORDER BY seq

Answer 1

一种方法是行号的差异：

select location, min(seq), max(seq), min(timestamp), max(timestamp)
from (select l.*,
             row_number() over (order by timestamp) as seqnum,
             row_number() over (partition by location order by timestamp) as seqnum_l
      from locationtimestamp l
     ) l
group by location, (seqnum - seqnum_l);

也就是说，作品起初是相当神秘的。 盯着子查询的结果，以便更好地理解。