如何限制timescaleDB查询使用的桶大小?

Question

我有一个postgres timescaleDB数据库与时间序列数据。表流中的数据大约每500ms采样一次。.

我需要每1秒获取的数据．我试着用time_bucket()函数来做。

这是我的测试查询:

SELECT time_bucket('1 second', time) AS bucket, value AS val 
FROM flows fl 
WHERE
fl.time > '2021-08-31 06:14:00+00' AND 
fl.time <= '2021-08-31 06:18:00+00' AND 
fl.sensor_id = 2 
ORDER BY fl.time ASC;

返回的数据如下:

|bucket                  |val                  |
| ---------------------- | ------------------- |
| 2021-08-31 06:14:00+00 | 9.75071040883207    |
| 2021-08-31 06:14:00+00 | 10.008532745208633  |
| 2021-08-31 06:14:01+00 | 9.953632354528265   |
| 2021-08-31 06:14:01+00 | 9.833033340905137   |
| 2021-08-31 06:14:02+00 | 9.77205680132453    |
| 2021-08-31 06:14:02+00 | 10.197350449765523  |
| ...                    | ...                 |

可以看到，每个bucket有两行。数值来自每500ms收集一次的样本。

如何确保每个桶只有一个值？(在我的例子中:每秒一个值)

我还尝试了对值的聚合函数(avg)，但这并没有改变结果。

Answer 1

对于time_bucket函数，为了使存储正常工作，必须以某种方式聚合value列，并提供group by语句。例如，像这样的代码应该正确地存储时间

SELECT time_bucket('1 second', time) AS bucket, 
sum(value) AS val 
FROM flows fl 
WHERE
time_bucket('1 second', time) > '2021-08-31 06:14:00+00' AND 
time_bucket('1 second', time) <= '2021-08-31 06:18:00+00' AND 
fl.sensor_id = 2 
GROUP BY bucket, sensor_id
ORDER BY bucket ASC;

希望这对你有用!

披露:我是Timescale团队的一员😊