如何在PostgreSQL中正确实现时间序列数据重采样计算小时均值

问题原因 #

你编写的SQL逻辑错误出在GROUP BY子句使用了原始的datetime字段，而非截断到小时的时间值。这会导致每个原始5分钟粒度的时间点都会单独分组，最终每个小时内仍会返回多条原始聚合结果，和你预期的每小时单条均值不符。

正确SQL实现 #

将分组和排序的依据都替换为date_trunc处理后的小时时间即可：

SELECT 
    date_trunc('hour', datetime) AS datetime,
    AVG(value) AS value,
    type,
    name
FROM data_table
GROUP BY date_trunc('hour', datetime), type, name
ORDER BY datetime, type, name;

补充说明 #

如果你的数据存在小时级缺失、需要输出连续的小时时序结果，可以先用generate_series生成完整的连续小时序列，再和上述聚合结果做左连接，按需填充空值即可避免时序断层。

内容的提问来源于stack exchange，提问作者user16511234