如何在滚动的1小时窗口中聚合结果
如何在滚动的1小时窗口中聚合结果
提问于 2019-05-23 12:53:37
我有一个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。有人知道有没有变通的办法吗？
回答 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;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每个有序记录的RANGE window function计数在30分钟之前和之后记录具有时间戳的所有记录。当然，这可以进行调整以更适合您的用例(也许是BETWEEN CURRENT ROW AND interval '1 hour' FOLLOWING)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56275699
复制相关文章
相似问题
腾讯云开发者
