如何在SQL中按每2小时间隔计算Value列的平均值？

要实现按每2小时窗口（如12:00:00-13:59:59、14:00:00-15:59:59）计算平均值，核心是把timestamp字段归整到对应窗口的起始时间，再按这个归整后的时间分组求平均。以下是几种主流SQL方言的实现方案：

MySQL 实现 #

利用HOUR()函数提取小时数，通过取模运算把时间归到2小时窗口的起始点：

SELECT
  DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') - INTERVAL (HOUR(timestamp) % 2) HOUR AS interval_start,
  AVG(Value) AS avg_value
FROM your_table_name
GROUP BY interval_start
ORDER BY interval_start;

逻辑说明：HOUR(timestamp) % 2得到当前小时除以2的余数，减去该余数对应的小时数，就能把任意时间映射到最近的偶数小时起始点（比如13:46会被调整为12:00，15:31调整为14:00）。

PostgreSQL 实现 #

通过EXTRACT()提取小时数，结合INTERVAL调整时间到窗口起始：

SELECT
  timestamp - INTERVAL '1 hour' * (EXTRACT(HOUR FROM timestamp) % 2) AS interval_start,
  AVG(Value) AS avg_value
FROM your_table_name
GROUP BY interval_start
ORDER BY interval_start;

也可以用date_trunc先截断到小时再调整：

SELECT
  date_trunc('hour', timestamp) - INTERVAL '1 hour' * (EXTRACT(HOUR FROM timestamp) % 2) AS interval_start,
  AVG(Value) AS avg_value
FROM your_table_name
GROUP BY interval_start
ORDER BY interval_start;

SQL Server 实现 #

用DATEPART()提取小时，通过整数除法确定窗口起始小时：

SELECT
  DATEADD(HOUR, (DATEPART(HOUR, timestamp) / 2) * 2, CAST(CAST(timestamp AS DATE) AS DATETIME)) AS interval_start,
  AVG(Value) AS avg_value
FROM your_table_name
GROUP BY DATEADD(HOUR, (DATEPART(HOUR, timestamp) / 2) * 2, CAST(CAST(timestamp AS DATE) AS DATETIME))
ORDER BY interval_start;

逻辑说明：DATEPART(HOUR, timestamp) / 2是整数除法，结果乘2后得到窗口的起始小时（比如13小时会计算为12，15小时计算为14），再和日期拼接成完整的窗口起始时间。

用你的示例数据测试时，上述代码会得到和你期望一致的核心结果，完整输出如下：

2022-09-17 12:00:00    2.5
2022-09-17 14:00:00    2.0
2022-09-17 16:00:00    2.3667
2022-09-17 18:00:00    2.6
2022-09-17 20:00:00    2.7

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