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

发布时间：2020-12-13 16:43:00 所属栏目：百科来源：网络整理

导读：我的测量数据存储在以下结构中： CREATE TABLE measurements(measured_at TIMESTAMPTZ,val INTEGER); 我已经知道使用了 (a)date_trunc(‘hour’，measured_at) 和 (b)generate_series 我可以通过以下方式汇总我的数据： microseconds,milliseconds... 但是可

我的测量数据存储在以下结构中：

CREATE TABLE measurements(
measured_at TIMESTAMPTZ,val INTEGER
);

我已经知道使用了

(a)date_trunc(‘hour’，measured_at)

和

(b)generate_series

我可以通过以下方式汇总我的数据：

microseconds,milliseconds
.
.
.

但是可以将数据汇总5分钟，还是说任意的秒数？是否可以将测量数据聚合任意的秒数？

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

您可以通过添加generate_series()创建的间隔来生成“buckets”表。此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,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()可以避免猜测五分钟桶的上限。实际上，您可能会将其构建为视图或函数。您可能会从基表获得更好的性能。

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!