问如何在滚动的1小时窗口中聚合结果
EN

Stack Overflow用户

提问于 2019-05-23 12:53:37

回答 2查看 65关注 0票数 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

我可以使用这个(与小mods)来找到每个位置最忙的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 supported only with UNBOUNDED”的错误。“‘RANGE”对我来说是新的，所以我现在正在读一些东西。

更新-好的，看来'RANGE‘需要版本11。有人知道有没有变通的办法吗？

postgresql

sql

回答 2

Stack Overflow用户

发布于 2019-05-23 13:16:56

可以将窗口函数与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;

票数 3

Stack Overflow用户

发布于 2019-05-23 13:19:50

对于Postgres 11，这是可行的(因为我没有样本数据来测试，这只是一个草图)

SELECT
    COUNT(*) OVER (ORDER BY arrivaltime RANGE BETWEEN interval '30 minutes' PRECEDING AND interval '30 minutes' FOLLOWING)
FROM
    arrival
ORDER BY count DESC