如何在MonetDB中实现时间序列按指定周期Downsampling/Upsampling聚合

云原生阿岚

2026-9-24

你遇到的语法报错是因为MonetDB的EXTRACT函数不支持DATE作为提取单位，且单独拆分时间分量再聚合的方案灵活性很低，无法适配2天、12小时这类自定义周期的下采样需求。

MonetDB提供*date_trunc*函数用于时间戳的周期对齐，是实现时间序列下采样的最优方案，它可以直接按指定周期将时间戳对齐到对应周期的起始点，按对齐后的字段分组聚合即可得到对应周期的统计值。

基础示例：按1小时聚合

SELECT 
  date_trunc('hour', "time") AS time_bucket,
  AVG(ph) AS avg_ph,
  AVG(temperature) AS avg_temperature
FROM datapoints
GROUP BY time_bucket
ORDER BY time_bucket;

该语句会将所有时间戳对齐到所属小时的0分0秒，分组后得到每一个自然小时的聚合结果，不会出现跨天聚合的问题。

自定义周期下采样示例

按2天聚合

SELECT
  date_trunc('day', "time") - (EXTRACT(DAY FROM "time") % 2) * INTERVAL '1 day' AS time_bucket,
  AVG(ph) AS avg_ph
FROM datapoints
GROUP BY time_bucket
ORDER BY time_bucket;

按12小时聚合

SELECT
  date_trunc('hour', "time") - (EXTRACT(HOUR FROM "time") % 12) * INTERVAL '1 hour' AS time_bucket,
  AVG(ph) AS avg_ph
FROM datapoints
GROUP BY time_bucket
ORDER BY time_bucket;

任意秒级周期聚合（以5分钟/300秒为例）

如果需要更灵活的周期，可以先将时间戳转成epoch秒数取整后再转回时间：

SELECT
  TO_TIMESTAMP(FLOOR(EXTRACT(EPOCH FROM "time") / 300) * 300) AS time_bucket,
  AVG(ph) AS avg_ph
FROM datapoints
GROUP BY time_bucket
ORDER BY time_bucket;