postgresql - Postgresql SQL GROUP BY时间间隔具有任意精度(低至毫秒)

我将测量数据存储到以下结构中:

CREATE TABLE measurements(
measured_at TIMESTAMPTZ,
val INTEGER
);

我已经知道使用

(a)date_trunc('hour',measured_at)
和

(b)generate_series
我可以通过以下方式汇总我的数据:

microseconds,
milliseconds
.
.
.

但是是否有可能在5分钟之内汇总数据，或者说可以说是任意几秒钟？是否可以将测量数据聚合任意几秒钟？

我需要通过不同的时间分辨率汇总的数据，以将其输入到FFT或AR模型中，以查看可能的季节性。

最佳答案

您可以通过添加由generate_series()创建的间隔来生成“存储桶”表。该SQL语句将在您的数据的第一天生成一个五分钟的存储桶表(min(measured_at)的值)。

select
  (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
  (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
from generate_series(0, (24*60), 5) n

将该语句包装在公用表表达式中，就可以像对其作为基表一样对其进行联接和分组。

with five_min_intervals as (
  select
    (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
    (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
  from generate_series(0, (24*60), 5) n
)
select f.start_time, f.end_time, avg(m.val) avg_val
from measurements m
right join five_min_intervals f
        on m.measured_at >= f.start_time and m.measured_at < f.end_time
group by f.start_time, f.end_time
order by f.start_time

按任意秒数分组相似-使用date_trunc()。

更一般地使用generate_series()可以避免猜测五分钟存储桶的上限。实际上，您可能会将其构建为 View 或函数。您可以从基表中获得更好的性能。

select
  (select min(measured_at)::date from measurements) + ( n    || ' minutes')::interval start_time,
  (select min(measured_at)::date from measurements) + ((n+5) || ' minutes')::interval end_time
from generate_series(0, ((select max(measured_at)::date - min(measured_at)::date from measurements) + 1)*24*60, 5) n;

关于postgresql - Postgresql SQL GROUP BY时间间隔具有任意精度(低至毫秒)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12045600/