MYSQL平均日期时间

debugcn 发表于 Dev

Xtern

我有以下SQL语句，它正确生成了相关的输出（我想每3分钟对值进行分组）：

SELECT date_time date, UNIX_TIMESTAMP(date_time) AS time_value, 
FLOOR((MINUTE(date_time) + (HOUR(date_time)*60))/3) AS minute_value, ph1_active_power AS p1 
FROM powerpro1 
GROUP BY date_time

产生的输出：

+-----------+---------------------+------------+--------------+---------+
| record_no |        date         | time_value | minute_value |   p1    |
+-----------+---------------------+------------+--------------+---------+
|         1 | 2014-12-01 00:00:00 | 1417372200 |            0 | 73.0767 |
|         2 | 2014-12-01 00:01:00 | 1417372260 |            0 | 73.0293 |
|         3 | 2014-12-01 00:02:00 | 1417372320 |            0 | 72.9818 |
|         4 | 2014-12-01 00:03:00 | 1417372380 |            1 | 72.9343 |
|         5 | 2014-12-01 00:04:00 | 1417372440 |            1 | 72.8868 |
|         6 | 2014-12-01 00:05:00 | 1417372500 |            1 | 72.8392 |
|         7 | 2014-12-01 00:06:00 | 1417372560 |            2 | 72.7916 |
|         8 | 2014-12-01 00:07:00 | 1417372620 |            2 | 72.744  |
|         9 | 2014-12-01 00:08:00 | 1417372680 |            2 | 72.6963 |
|        10 | 2014-12-01 00:09:00 | 1417372740 |            3 | 72.6486 |
|        11 | 2014-12-01 00:10:00 | 1417372800 |            3 | 72.6009 |
|        12 | 2014-12-01 00:11:00 | 1417372860 |            3 | 72.5531 |
|        13 | 2014-12-01 00:12:00 | 1417372920 |            4 | 72.5053 |
|        14 | 2014-12-01 00:13:00 | 1417372980 |            4 | 72.4575 |
|        15 | 2014-12-01 00:14:00 | 1417373040 |            4 | 72.4096 |
|        16 | 2014-12-01 00:15:00 | 1417373100 |            5 | 72.3617 |
|        17 | 2014-12-01 00:16:00 | 1417373160 |            5 | 72.3137 |
|        18 | 2014-12-01 00:17:00 | 1417373220 |            5 | 72.2657 |
|        19 | 2014-12-01 00:18:00 | 1417373280 |            6 | 72.2177 |
|        20 | 2014-12-01 00:19:00 | 1417373340 |            6 | 72.1697 |
|        21 | 2014-12-01 00:20:00 | 1417373400 |            6 | 72.1216 |
|        22 | 2014-12-01 00:21:00 | 1417373460 |            7 | 72.0734 |
|        23 | 2014-12-01 00:22:00 | 1417373520 |            7 | 72.0253 |
|        24 | 2014-12-01 00:23:00 | 1417373580 |            7 | 71.9771 |
+-----------+---------------------+------------+--------------+---------+

但是，我想获取time_value的平均值和p1的平均值，然后需要按minute_value进行分组。如果我在上面的查询中使用了以下相关更改，

SELECT date_time date, AVG(UNIX_TIMESTAMP(date_time)) AS time_value, FLOOR((MINUTE(date_time) + (HOUR(date_time)*60))/3) AS minute_value, ROUND(AVG(ph1_active_power),4) AS p1 
FROM powerpro1 
GROUP BY minute_value

我得到了错误的输出，如下所述。

+-----------+---------------------+-----------------+--------------+--------+
| record_no |        date         |   time_value    | minute_value |   p1   |
+-----------+---------------------+-----------------+--------------+--------+
|         1 | 2014-12-01 00:00:00 | 1418754688.6364 |            0 | 2.2622 |
|         4 | 2014-12-01 00:03:00 | 1418754868.6364 |            1 | 2.2541 |
|         7 | 2014-12-01 00:06:00 | 1418755048.6364 |            2 | 2.246  |
|        10 | 2014-12-01 00:09:00 | 1418755228.6364 |            3 | 2.2378 |
|        13 | 2014-12-01 00:12:00 | 1418755408.6364 |            4 | 2.2297 |
|        16 | 2014-12-01 00:15:00 | 1418755588.6364 |            5 | 2.2216 |
|        19 | 2014-12-01 00:18:00 | 1418755768.6364 |            6 | 2.2134 |
|        22 | 2014-12-01 00:21:00 | 1418755948.6364 |            7 | 2.2052 |
+-----------+---------------------+-----------------+--------------+--------+

要求的输出：

+-----------+---------------------+--------------+------------+---------+
| record_no |     time_value      | minute_value | time_value |   p1    |
+-----------+---------------------+--------------+------------+---------+
|         2 | 2014-12-01 00:01:00 |            0 | 1417372260 | 73.0293 |
|         5 | 2014-12-01 00:04:00 |            1 | 1417372440 | 72.8868 |
|         8 | 2014-12-01 00:07:00 |            2 | 1417372620 | 72.744  |
|        11 | 2014-12-01 00:10:00 |            3 | 1417372800 | 72.6009 |
|        14 | 2014-12-01 00:13:00 |            4 | 1417372980 | 72.4575 |
+-----------+---------------------+--------------+------------+---------+

可能是什么问题。

任何人都可以利用宝贵的时间和知识来帮助我。

维沙尔·赞鲁基亚（Vishal Zanzrukia）

你可以试试这个吗？

SELECT date_time date, SUM(UNIX_TIMESTAMP(date_time))/COUNT(record_no) AS time_value, FLOOR((MINUTE(date_time) + (HOUR(date_time)*60))/3)*3 AS minute_value, ROUND((SUM(ph1_active_power)/COUNT(record_no)),4) AS p1 
FROM powerpro1 
GROUP BY minute_value

我已经通过以下查询完成了：

SELECT record_no, date_time, 
ROUND(AVG(UNIX_TIMESTAMP(date_time))) AS time_value, 
ROUND(AVG(ph1_active_power),4) AS p1 
FROM powerpro1
WHERE date_time <= '2014-12-20 00:00:00'
GROUP BY date_time DIV 300

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。