选择一个区间的几个平均值

Question

所以我的同事正在研究一个可以描述为这样的模式：

+--------------------+-----------+
| DATETIME timestamp | INT value |
+--------------------+-----------+

每5分钟输入一行，其中包含该时刻的值。

这就是它变得棘手的地方。他想在7天的时间间隔内得到每8小时的平均值。

当然，我可以想到一些涉及客户端代码的解决方案，我们想知道是否可以在SQL中做更多的工作。

所以本质上，他想要：

SELECT timestamp, value
  FROM table
 WHERE timestamp >= NOW() - INTERVAL 7 DAYS 
   AND timestamp <= NOW();

然后将其分解为8个小时的区块，并对每个区块的内容进行平均。（每个区块应该有12行，每天应该有3个区块）。

Answer 1

尝试

SELECT avg(`value`)
FROM `table`
WHERE timestamp >= NOW() - INTERVAL 7 DAY AND timestamp <= NOW()
group by concat(date(`timestamp`), case when hour(`timestamp`) between 0 and 7 then 1
                                        when hour(`timestamp`) between 8 and 15 then 2
                                        else 3 end)

Answer 2

如果您不只是在请求中执行它，那么您可以在执行请求之前尝试以下方法来划分间隔：

boundary1 = NOW()
boundary2 = NOW()
FOR i = 0 to 21 //7 days, 3 intervals of 8 hours per days
    boundary1 = boundary2
    boundary2 = boundary1 - seconds(8 hours)
    req = "SELECT timestamp, value FROM table WHERE timestamp >= "+boundary2+" AND timestamp <= "+boundary1
ENDFOR