Average per hour Mysql

Question

Hello, I wonder if you could return only the averages per hour of a table as below:

id | valor |     timestamp    |
1  | 5.4   | 2017-4-20 18:00  |
2  | 3     | 2017-4-20 18:01  |
3  | 2.8   | 2017-4-20 18:40  |
4  | 2     | 2017-4-20 19:00  |
5  | 10    | 2017-4-20 19:05  |
6  | 7     | 2017-4-20 19:20  |
7  | 1     | 2017-4-20 19:55  |
8  | 5     | 2017-4-20 20:00  |
9  | 7     | 2017-4-20 20:11  |
10 | 4     | 2017-4-20 20:50  |
11 | 6     | 2017-4-20 21:00  |
------------------------------

..would have any select to return the hourly averages from the "value" column? I am trying but unsuccessfully...

Answer 1

SELECT AVG( valor ) , HOUR( `timestamp` )
FROM tabela
WHERE DATE_SUB(  `timestamp` , INTERVAL 1 HOUR )
GROUP BY HOUR( `timestamp` )

Upshot

To catch the hourly averages of every day

SELECT AVG( valor ) , HOUR( `timestamp` ),day( `timestamp` )
FROM tabela
WHERE DATE_SUB(  `timestamp` , INTERVAL 1 HOUR )
GROUP BY HOUR( `timestamp` ), day( `timestamp` )


id | valor |     timestamp    |
1  | 5.4   | 2017-4-20 18:00  |
2  | 3     | 2017-4-20 18:01  |
3  | 2.8   | 2017-4-20 18:40  |
4  | 2     | 2017-4-20 19:00  |
5  | 10    | 2017-4-20 19:05  |
6  | 7     | 2017-4-20 19:20  |
7  | 1     | 2017-4-20 19:55  |
8  | 5     | 2017-4-20 20:00  |
9  | 7     | 2017-4-20 20:11  |
10 | 4     | 2017-4-20 20:50  |
11 | 6     | 2017-4-20 21:00  |
12 | 4     | 2017-4-21 21:08  |
13 | 6     | 2017-4-21 21:10  |
14 | 3     | 2017-4-21 21:25  |
15 | 6     | 2017-4-21 22:15  |
16 | 4     | 2017-4-21 22:25  |
17 | 4     | 2017-4-21 22:35  |
------------------------------

Answer 2

You can use the AVG aggregation function:

SELECT timestamp, AVG(valor)
->        FROM tabela
->        GROUP BY timestamp;

For more details about aggregation functions (in English): https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_avg