使用 time_bucket 聚合时间序列数据

time_bucket 函数帮助您在 超表 中进行分组，以便您可以对任意时间间隔执行聚合计算。它通常与 GROUP BY 结合使用以达到此目的。

本节展示了 time_bucket 函数的使用示例。要了解时间桶的工作原理，请参阅关于时间桶部分。

将数据按时间桶分组，并计算列的汇总值。例如，计算名为 weather_conditions 的表中每日平均温度。该表有一个名为 time 的时间列和一个 temperature 列

SELECT time_bucket('1 day', time) AS bucket,
  avg(temperature) AS avg_temp
FROM weather_conditions
GROUP BY bucket
ORDER BY bucket ASC;

复制

time_bucket 函数返回时间桶的开始时间。在本示例中，第一个时间桶从 2016 年 11 月 15 日午夜开始，并聚合了当天所有的数据

bucket                 |      avg_temp
-----------------------+---------------------
2016-11-15 00:00:00+00 | 68.3704391666665821
2016-11-16 00:00:00+00 | 67.0816684374999347

复制

默认情况下，time_bucket 列显示时间桶的开始时间。如果您希望显示结束时间，可以使用对 time 进行数学运算来移动显示的时间。

例如，您可以计算 5 分钟间隔内的最小和最大 CPU 使用率，并显示间隔的结束时间。示例表名为 metrics。它有一个名为 time 的时间列和一个 CPU 使用率列 cpu

SELECT time_bucket('5 min', time) + '5 min' AS bucket,
  min(cpu),
  max(cpu)
FROM metrics
GROUP BY bucket
ORDER BY bucket DESC;

复制

添加 + '5 min' 会将显示的时间戳更改为时间桶的结束时间。它不会更改时间桶跨越的时间范围。

要更改时间桶跨越的时间范围，请使用 offset 参数，该参数接受 INTERVAL 参数。正偏移量会将时间桶的开始和结束时间向后移动。负偏移量会将时间桶的开始和结束时间向前移动。

例如，您可以计算 5 小时间隔的平均 CPU 使用率，并将所有时间桶的开始和结束时间向后移动 1 小时

SELECT time_bucket('5 hours', time, '1 hour'::INTERVAL) AS bucket,
  avg(cpu)
FROM metrics
GROUP BY bucket
ORDER BY bucket DESC;

复制

时间桶通常与 GROUP BY 一起使用以聚合数据。但是您也可以在单个时间值上运行 time_bucket。这对于测试和学习很有用，因为您可以查看值落入哪个时间桶。

例如，要查看 2021 年 1 月 5 日将落入的 1 周时间桶，请运行

SELECT time_bucket(INTERVAL '1 week', TIMESTAMP '2021-01-05');

复制

该函数返回 2021-01-04 00:00:00。时间桶的开始时间是该周的星期一，在午夜。