Mysql 5.5聚合时间间隔

Question

我看过很多关于如何指定时间间隔的帖子。我尝试使用它们，但得到了奇怪的结果。这是我的 table :

select value,time from mysensor9 order by time desc;

+-------+---------------------+
| value | time                |
+-------+---------------------+
|    79 | 2016-01-27 22:19:46 |
|    45 | 2016-01-27 22:19:45 |
|     5 | 2016-01-27 22:19:44 |
|    72 | 2016-01-27 22:19:43 |
|    20 | 2016-01-27 22:19:42 |
|    92 | 2016-01-27 22:19:41 |
 .....

我已经用每个月的每一秒的值填充了一个表。

然后我尝试每 5 分钟/小时/天/月聚合表的数据。当我尝试汇总每天的平均值时，我会进行以下查询:

select AVG(value),time from mysensor9 where time > "2015-12-09" group by UNIX_TIMESTAMP(time) div (3600*24) order by time asc;

结果还可以:

+------------+---------------------+
| AVG(value) | time                |
+------------+---------------------+
|    48.7179 | 2015-12-09 02:13:46 |
|    49.4044 | 2015-12-10 02:13:46 |
|    49.5001 | 2015-12-11 02:13:46 |
|    49.4805 | 2015-12-12 02:13:46 |
|    48.9036 | 2015-12-13 02:13:46 |

当我想以 1 小时为间隔进行聚合时，我会进行以下查询:

select AVG(value),time from mysensor9 where time > "2015-12-09" group by UNIX_TIMESTAMP(time) div (3600) order by time asc;

编辑(小时间隔结果):

+------------+---------------------+
| AVG(value) | time                |
+------------+---------------------+
|    49.2355 | 2015-12-09 00:13:46 |
|    48.0028 | 2015-12-09 01:13:46 |
|    49.6316 | 2015-12-09 02:13:46 |
|    47.8449 | 2015-12-09 03:13:46 |
|    49.0166 | 2015-12-09 04:13:46 

|

结果还是不错的。但是当我想使用相同的方法聚合 5 分钟的时间间隔时:

select AVG(value),time from mysensor9 where time > "2015-12-09" group by UNIX_TIMESTAMP(time) div (300) order by time asc;

我明白了:

+------------+---------------------+
| AVG(value) | time                |
+------------+---------------------+
|    44.9865 | 2015-12-09 00:13:46 |
|    50.3310 | 2015-12-09 00:15:00 |
|    50.0135 | 2015-12-09 01:13:46 |
|    47.4843 | 2015-12-09 01:15:00 |
|    51.8514 | 2015-12-09 02:13:46 |

为什么会这样？为什么它不返回这样的东西:

+------------+---------------------+
| AVG(value) | time                |
+------------+---------------------+
|    44.9865 | 2015-12-09 00:13:46 |
|    50.3310 | 2015-12-09 00:18:46 |
|    50.0135 | 2015-12-09 00:23:46 |
|    47.4843 | 2015-12-09 00:28:46 |
|    51.8514 | 2015-12-09 00:33:46 |

Answer 1

当您选择不属于 GROUP BY 的内容时，mysql 会非常随机地为您选择(它将选择分组行中的任何一行)。

您应该选择与分组依据相同的值并将其格式化回日期:

select AVG(value),DATE_FORMAT(FROM_UNIXTIME((UNIX_TIMESTAMP(time) div (300))*300),'%d-%m-%Y %h:%i:%s') as group_time from mysensor9 where time > "2015-12-09" group by UNIX_TIMESTAMP(time) div (300) order by time asc;

您也可以使用 MIN()/MAX() 来始终获取分组行的上限或下限，如果这对您的情况可行，它看起来更优雅一些:

select AVG(value),MIN(time) from mysensor9 where time > "2015-12-09" group by UNIX_TIMESTAMP(time) div (300) order by time asc;

另请注意，根据上下文在组中选择非组列是 MySQL 的行为，其他一些数据库会将此简单地声明为语法错误。