sql - 如何针对滚动的 1 小时窗口汇总结果

Question

我有一个 Postgres 表，其中包含到达某个位置的车辆的交付记录。 (每次旅行一个独特的记录)。我可以使用类似这样的按小时聚合来监控繁忙时段...

select count(*), LOCATION, date_trunc('hour',ARRIVALTIME)
from ARRIVALS
group by LOCATION, date_trunc('hour',ARRIVALTIME)
order by LOCATION, date_trunc('hour',ARRIVALTIME) desc

我可以使用它(带有小模块)找到每个位置最繁忙的 1 小时时段。然而，这有局限性，它只能按时间顺序汇总，即 1 点到 2 点之间，然后是 2 点到 3 点等。

问题 - 如何找到一天中最繁忙的 60 分钟“滚动窗口”？

以此为例。如果我在 13:30 到 14:30 之间每两分钟有一个到达(没有其他时间)，那么我在上面显示的查询将告诉我第 1 到 2 期间有 15 次交付，第 2 到 3 期间也是如此。我真正想要的是一个查询，它会告诉我最忙的 60 分钟是 13:30 到 14:30 之间的 30 次送货。

我使用的是 postgres 版本 10.5

DROP TABLE arrival;
CREATE TABLE arrival (  location CHAR(15),  arrivalTime TIMESTAMP);
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 09:45:00');
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 09:50:00');
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 09:55:00');
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 09:59:00');
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 10:10:00');
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 10:15:00');
INSERT INTO arrival (location,arrivalTime) VALUES('LONDON','01-Jan-2000 10:25:00');
INSERT INTO arrival (location,arrivalTime) VALUES('PARIS','01-Jan-2000 09:58:00');
INSERT INTO arrival (location,arrivalTime) VALUES('PARIS','01-Jan-2000 10:01:00');
INSERT INTO arrival (location,arrivalTime) VALUES('PARIS','01-Jan-2000 10:02:00');
INSERT INTO arrival (location,arrivalTime) VALUES('PARIS','01-Jan-2000 11:02:00');

到目前为止，这两个建议都出现了“RANGE FOLLOWING is only supported with UNBOUNDED”的错误。 “RANGE”对我来说是新的，所以我目前正在阅读。

更新 - 好的，看来“RANGE”需要版本 11。有人知道是否有解决方法吗？

Answer 1

您可以使用带有 range 窗口说明符的窗口函数:

select a.*,
       count(*) over (order by arrivaltime
                      range between current row and '1 hour'::interval following
                     ) as hourcnt
from arrivals a
order by hourcnt desc
fetch first 1 row only;