如何使用SQL对多日小时级数据求平均值（PostgreSQL/TimescaleDB场景）

嘿，这个需求用PostgreSQL/TimescaleDB的日期函数就能完美解决，我给你具体拆解实现步骤和代码：

核心思路 #

我们需要两步完成：

• 从原始时间戳中提取小时部分，以此为依据分组计算平均值
• 将分组后的小时部分与查询当日的日期拼接，生成你想要的目标时间戳

具体SQL代码 #

写法一：用EXTRACT提取小时数 #

SELECT
  -- 拼接查询当日日期与原时间的小时部分，生成目标时间戳
  (CURRENT_DATE + INTERVAL '1 hour' * EXTRACT(HOUR FROM "Timestamp"))::TIMESTAMP AS "Timestamp",
  -- 计算该小时范围内的平均值
  AVG("Value") AS "Value"
FROM your_table_name
-- 指定查询的时间范围
WHERE "Timestamp" BETWEEN '2021-02-10' AND '2021-02-20'
-- 按原时间的小时数分组
GROUP BY EXTRACT(HOUR FROM "Timestamp")
-- 按小时排序，让结果更直观
ORDER BY EXTRACT(HOUR FROM "Timestamp");

写法二：用DATE_TRUNC提取小时时间片段（更直观） #

SELECT
  -- 将查询当日日期与原时间的小时时间片段拼接
  (CURRENT_DATE + DATE_TRUNC('hour', "Timestamp")::TIME)::TIMESTAMP AS "Timestamp",
  AVG("Value") AS "Value"
FROM your_table_name
WHERE "Timestamp" BETWEEN '2021-02-10' AND '2021-02-20'
-- 按原时间的小时级时间片段分组（比如13:00:00）
GROUP BY DATE_TRUNC('hour', "Timestamp")::TIME
ORDER BY DATE_TRUNC('hour', "Timestamp")::TIME;

代码细节解释 #

• CURRENT_DATE：PostgreSQL内置函数，自动获取查询执行时的日期（比如你2021-10-08查询就返回'2021-10-08'，次日查询自动更新为'2021-10-09'）
• EXTRACT(HOUR FROM "Timestamp")：提取原始时间戳的小时数（0-23），作为分组的核心依据
• DATE_TRUNC('hour', "Timestamp")::TIME：将原始时间戳截断到小时级别后转为TIME类型（比如2021-02-17 13:00:00转为13:00:00），用这个分组更直观，排序逻辑也更自然
• 两种写法最终都会生成你期望的结果：比如13点的平均值为2.5，时间戳显示为查询当日的13:00:00

适配TimescaleDB #

如果你的表是TimescaleDB的超表，这个查询完全兼容，而且TimescaleDB会自动利用时间分区优化分组查询的性能，不用担心大数据量下的效率问题。

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