时间戳

首页 > 解决方案 > 如何针对滚动的 1 小时窗口汇总结果

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

问题描述

我有一个 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 仅支持 UNBOUNDED”的错误。“RANGE”对我来说是新的，所以我目前正在阅读。

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

标签： sqlpostgresql

解决方案

您可以使用带有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;

推荐阅读