使用 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。时间桶的开始时间是该周的星期一，午夜时分。