重新采样时间序列数据 [英] Resample on time series data

问题描述

我有一个带有以毫秒为单位的时间序列列的表，我想对时间序列进行重新采样并将平均值应用于该组。如何在Postgres中实现它？

I have a table with time series column in the millisecond, I want to resample the time series and apply mean on the group. How can I implement it in Postgres?

重新采样表示在一秒或一分钟内汇总所有时间戳。一秒钟或一分钟内的所有行都组成一个组。

"Resample" means aggregate all time stamps within one second or one minute. All rows within one second or one minute form a group.

表结构

date    x    y    z

推荐答案

使用 date_trunc（） 将时间戳截断为给定的时间单位，然后 GROUP BY 该表达式：

Use date_trunc() to truncate timestamps to a given unit of time, and GROUP BY that expression:

SELECT date_trunc('minute', date) AS date_truncated_to_minute
      ,avg(x) AS avg_x
      ,avg(y) AS avg_y
      ,avg(z) AS avg_z
FROM   tbl
GROUP  BY 1;

假设您误导了 date 列类型为 timestamp 。

Assuming your misleadingly named date column is actually of type timestamp.

相关答案以及更多详细信息和链接：

Related answer with more details and links:

• PostgreSQL：按分钟运行查询的行数

• PostgreSQL: running count of rows for a query 'by minute'

这篇关于重新采样时间序列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！