mysql - 按序列号和天数间隔分组的平均温度

Question

所以，我在尝试计算 MySQL 查询中的平均值时遇到了一些困难。我想要做的是从两个表中读取数据，其中一个表很小，并且包含描述房间、序列号、海拔和有关传感器的其他数据的条目。另一个表包含每个序列号的温度输出记录。以下是每个表的一些示例数据。

+------------------+-------+------+-----------+---------+--------+-----------+
| serial           | room  | rack | elevation | type    | system | threshold |
+------------------+-------+------+-----------+---------+--------+-----------+
| 2D0008017075F210 | 2B211 | 5    |         5 | DS18S20 | test   |        68 |
| 1D00080170496D10 | 2B211 | 5    |         5 | DS18S20 | test   |        68 |
| 380008017037ED10 | 2B211 | 5    |         5 | DS18S20 | test   |        68 |
+------------------+-------+------+-----------+---------+--------+-----------+


+------------------+---------------------+---------+
| serial           | dtg                 | reading |
+------------------+---------------------+---------+
| 2D0008017075F210 | 2013-08-02 12:30:28 |    98.6 |
| 1D00080170496D10 | 2013-08-02 12:30:28 |    73.4 |
| 380008017037ED10 | 2013-08-02 12:30:28 |    79.7 |
| 2D0008017075F210 | 2013-08-02 12:30:38 |    98.6 |
| 1D00080170496D10 | 2013-08-02 12:30:38 |    73.4 |
| 380008017037ED10 | 2013-08-02 12:30:38 |    79.7 |
| 2D0008017075F210 | 2013-08-02 12:30:48 |    98.6 |
| 1D00080170496D10 | 2013-08-02 12:30:48 |    73.4 |
| 380008017037ED10 | 2013-08-02 12:30:48 |    79.7 |
| 2D0008017075F210 | 2013-08-02 12:30:58 |    98.6 |
| 1D00080170496D10 | 2013-08-02 12:30:58 |    73.4 |
| 380008017037ED10 | 2013-08-02 12:30:58 |    79.7 |
| 2D0008017075F210 | 2013-08-02 12:31:08 |    98.6 |
| 1D00080170496D10 | 2013-08-02 12:31:08 |    73.4 |
| 380008017037ED10 | 2013-08-02 12:31:08 |    79.7 |
| 2D0008017075F210 | 2013-08-02 12:31:18 |    98.6 |
| 1D00080170496D10 | 2013-08-02 12:31:18 |    73.4 |
| 380008017037ED10 | 2013-08-02 12:31:18 |    79.7 |
+------------------+---------------------+---------+

我有一个 SQL 查询，可以将数据以某种格式提取到表中，然后将其转换为 JSON 并传递到 Web 浏览器进行可视化。它看起来与此类似，但略有不同，因为我使用存储过程来修改时间间隔。

SELECT sensor_data.serial, sensor_data.dtg, sensor_data.reading, sensor.elevation,sensor.room, sensor.system FROM sensor_data, sensor WHERE sensor.serial = sensor_data.serial AND sensor_data.dtg BETWEEN NOW() - INTERVAL 1 MINUTE AND NOW();

结果看起来像这样

+------------------+---------------------+---------+-----------+-------+--------+
| serial           | dtg                 | reading | elevation | room  | system |
+------------------+---------------------+---------+-----------+-------+--------+
| 2D0008017075F210 | 2013-08-02 12:34:39 |    98.6 |         5 | 2B211 | test   |
| 2D0008017075F210 | 2013-08-02 12:34:49 |    98.6 |         5 | 2B211 | test   |
| 2D0008017075F210 | 2013-08-02 12:34:59 |    98.6 |         5 | 2B211 | test   |
| 2D0008017075F210 | 2013-08-02 12:35:09 |    98.6 |         5 | 2B211 | test   |
| 2D0008017075F210 | 2013-08-02 12:35:19 |    98.6 |         5 | 2B211 | test   |
| 2D0008017075F210 | 2013-08-02 12:35:29 |    98.6 |         5 | 2B211 | test   |
| 1D00080170496D10 | 2013-08-02 12:34:39 |    73.4 |         5 | 2B211 | test   |
| 1D00080170496D10 | 2013-08-02 12:34:49 |    73.4 |         5 | 2B211 | test   |
| 1D00080170496D10 | 2013-08-02 12:34:59 |    73.4 |         5 | 2B211 | test   |
| 1D00080170496D10 | 2013-08-02 12:35:09 |    73.4 |         5 | 2B211 | test   |
| 1D00080170496D10 | 2013-08-02 12:35:19 |    73.4 |         5 | 2B211 | test   |
| 1D00080170496D10 | 2013-08-02 12:35:29 |    73.4 |         5 | 2B211 | test   |
| 380008017037ED10 | 2013-08-02 12:34:39 |    79.7 |         5 | 2B211 | test   |
| 380008017037ED10 | 2013-08-02 12:34:49 |    79.7 |         5 | 2B211 | test   |
| 380008017037ED10 | 2013-08-02 12:34:59 |    79.7 |         5 | 2B211 | test   |
| 380008017037ED10 | 2013-08-02 12:35:09 |    79.7 |         5 | 2B211 | test   |
| 380008017037ED10 | 2013-08-02 12:35:19 |    79.7 |         5 | 2B211 | test   |
| 380008017037ED10 | 2013-08-02 12:35:29 |    79.7 |         5 | 2B211 | test   |
+------------------+---------------------+---------+-----------+-------+--------+

我想做的是创建一个语句，可以提取 5 天的记录并返回一个结果集，其中每天都有一行，其中包含上述所有信息以及每天的平均温度。我知道目前的数据都具有相同的温度，但这是在测试环境中，实际上没有任何变化。由于不是 DBA，我有点陷入困境。

Answer 1

您需要格式化日期时间以去掉时间部分，然后按其分组:

SELECT 
    sensor.serial,
    date_format(sensor_data.dtg, '%m/%Y/%d') as dtg_day,
    sensor.elevation,
    sensor.room, 
    sensor.system,
    avg(sensor_data.reading),
    count(1) reading_count
FROM 
    sensor
    NATURAL JOIN sensor_data
GROUP BY
    sensor.serial,
    dtg_day,
    sensor.elevation,
    sensor.room, 
    sensor.system

SQLFiddle here