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

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个块）。

- Evan Teran

---

这是什么数据库？从你的SQL语句看，我猜测是MySQL？ - Mark Byers

抱歉，我忘了提到，是MySQL数据库。 - Evan Teran

你想要相邻的8小时块还是重叠的？比如12:00-08:00，然后08:00-16:00等。还是12:00-08:00，然后12:05-08:05，12:10-08:10等。 - njr101

@njr101：我假设是相邻的。 - Evan Teran

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)

- juergen d

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

- Al_th