平均每2小时学习一次SQL

Question

我有一个SQL表，它有两个字段：时间戳和值。以下是数据示例(取自前一篇文章，见下文(：

2022-09-17 12:31:00     2
2022-09-17 13:46:00     3
2022-09-17 14:01:00     1.7
2022-09-17 14:16:00     2.3
2022-09-17 15:31:00     2
2022-09-17 16:46:00     2.5
2022-09-17 17:01:00     2.2
2022-09-17 17:16:00     2.4
2022-09-17 18:31:00     2.6
2022-09-17 19:46:00     2.6
2022-09-17 20:01:00     2.7

我尝试每2小时(12:00:00到13:59:59，14:00:00到15:59:59…(取一次Value列的平均值。

对于最终输出，我正在寻找这样的东西：

2020-09-17 12:00:00    2.5
2020-09-17 14:00:00    2.0

我发现：采用SQL中的小时平均值但我不知道如何适应它两个小时。

感谢您的帮助

Answer 1

根据您提供的链接，我按照该链接中的表信息构建了查询。我们可以有以下查询，我希望它能有所帮助。

SELECT CAST(FLOOR(`timestamp`) AS DATETIME)  AS `time`, AVG(`value`) AS average
FROM times
GROUP BY  DATE(`timestamp`), FLOOR(HOUR(`timestamp`) / 2)

我在MariaDB 10.3 上测试过

Answer 2

您可以使用以下代码来获得所需的结果：

select concat(Date(`timestamp`),' ',LPAD(FLOOR(HOUR(`timestamp`) / 2)*2,2,'0'),':00:00'), avg(`timestamp`) from times 
group by concat(Date(`timestamp`),' ',LPAD(FLOOR(HOUR(`timestamp`) / 2)*2,2,'0'),':00:00')

或

SELECT concat(Date(`timestamp`),' ',LPAD(FLOOR(HOUR(`timestamp`) / 2)*2,2,'0'),':00:00')  AS `time`, AVG(`value`) AS average
FROM times
GROUP BY  DATE(`timestamp`), FLOOR(HOUR(`timestamp`)/ 2)