在SQL中通过日期和时间执行聚合 [英] Performing aggregation through date and time in SQL

问题描述

我有一个数据集，其中包含以2分钟为频率的几周观察结果。
我想将时间间隔从2分钟增加到5分钟。问题在于，观察的频率并不总是相同的。我的意思是，从理论上讲，每10分钟应进行5次观察，但通常情况并非如此。
请让我知道如何根据平均函数以及观察时间和日期来汇总观察。
换句话说，基于每5分钟的汇总，而每5分钟的时间间隔的观察次数不同。
而且，我有时间戳格式的日期和时间。

I have a data-set which contains observations for several weeks with 2 minutes frequency. I want to increase the time interval from 2 minute to 5 minute. The problem is that, frequency of the observations are not always the same. I mean, theoretically, every 10 minute there should be 5 observation but usually it is not the case. Please let me know how I can aggregate the observations based on average function and with respect to the time and date of the observations. In other words aggregation based on every 5 minutes while number of observations are not the same for each 5 minute time interval. Moreover, I have date and time in timestamp format.

示例数据：

1 2007-09-14 22:56:12 5.39
2 2007-09-14 22:58:12 5.34
3 2007-09-14 23:00:12 5.16
4 2007-09-14 23:02:12 5.54
5 2007-09-14 23:04:12 5.30
6 2007-09-14 23:06:12 5.20

预期结果：

1 2007-09-14 23:00 5.29
2 2007-09-14 23:05 5.34

推荐答案

此问题可能为您的问题提供了很好的解决方案，展示了将数据有效地聚合到时间窗口中的方法。

The answers to this question likely provide good solutions to your problem, showing ways to efficiently aggregate data into time windows.

本质上，将 avg 聚合与：

GROUP BY floor(extract(epoch from the_timestamp) / 60 / 5)

这篇关于在SQL中通过日期和时间执行聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！