mysql选择并取平均值

Question

我有一个数据库，用于存储温度、传感器编号和输入的时间。了解我对此很陌生，但非常感谢您的帮助!

数据库字段:

temp,tempdate,tempttime,sensorno, timeentered

我想做的是编写一个在 php 中运行的查询，它将选择当天的数据，查看时间并计算每小时的平均温度。因此，它将把属于同一小时的所有数据，即从 00:00:00 到 00:01:00(每个传感器)相加，然后除以存储的值的数量，得到该小时的平均值。

示例数据:

temp tempdate   tempttime sensorno  timeentered
5   2014-12-21  00:00:00    1   2014-12-21 00:00:00
8.5 2014-12-21  00:00:01    2   2014-12-21 00:00:01
9   2014-12-21  00:00:02    3   2014-12-21 00:00:02
4.5 2014-12-21  00:00:02    4   2014-12-21 00:00:02
9   2014-12-21  00:00:03    5   2014-12-21 00:00:03
5.5 2014-12-21  00:00:03    6   2014-12-21 00:00:03
5   2014-12-21  00:01:08    1   2014-12-21 00:01:08
8.5 2014-12-21  00:01:09    2   2014-12-21 00:01:09
9   2014-12-21  00:01:09    3   2014-12-21 00:01:09
5   2014-12-21  00:01:10    4   2014-12-21 00:01:10
9   2014-12-21  00:01:10    5   2014-12-21 00:01:10
5.5 2014-12-21  00:01:11    6   2014-12-21 00:01:11
5   2014-12-21  00:02:16    1   2014-12-21 00:02:16
8.5 2014-12-21  00:02:16    2   2014-12-21 00:02:16
9   2014-12-21  00:02:17    3   2014-12-21 00:02:17
5   2014-12-21  00:02:18    4   2014-12-21 00:02:18
9   2014-12-21  00:02:18    5   2014-12-21 00:02:18
5.5 2014-12-21  00:02:19    6   2014-12-21 00:02:19
5   2014-12-21  00:03:24    1   2014-12-21 00:03:24
8.5 2014-12-21  00:03:24    2   2014-12-21 00:03:24
8.5 2014-12-21  00:03:25    3   2014-12-21 00:03:25
4.5 2014-12-21  00:03:25    4   2014-12-21 00:03:25
9   2014-12-21  00:03:26    5   2014-12-21 00:03:26
5.5 2014-12-21  00:03:26    6   2014-12-21 00:03:26

所以我当前的查询是

use ponddb;
SELECT 
    *
FROM
    pondtemp
WHERE
    timeentered >= CURDATE();

Answer 1

这基本上就是您想要的查询:

SELECT tempdate, hour(temptime) as temphour, sensorno, avg(temp) as avgtemp
FROM pondtemp
WHERE tempdate = CURDATE()
GROUP BY tempdate, hour(temptime), sensorno
ORDER BY tempdate, hour(temptime), sensorno;

我包含了日期和时间，以防您想扩展 where 子句以处理多个日期。