A friend created an Arduino project that monitors power consumption in Kw / h. It every 5 minutes inserts the consumption information in the MySQL database.
Every 5 records I need to average them, and then take the average and multiply by 15.
I can do this in both PHP and MySQL, could anyone give me a hint how to do a listing with all averages 5 out of 5?
For a listing with averages multiplied by 15:
SELECT AVG( consumo ) * 15 AS media_consumo
FROM leitura
GROUP BY FLOOR( ( id - 1 ) / 5 )
ORDER BY id
See working in SQL Fiddle .
Of curiosity, the average x 15 gives the same as the sum multiplied by 3:
SELECT SUM( consumo ) * 3
See working in SQL Fiddle .
Note: This type of grouping only makes sense if% s of% s are continuous.
The average calculation is basically the sum of the elements divided by the number of elements.
Example of averaging: (1 + 2 + 3 + 4) / 4 = 2,5 .
In this sense, in PHP you can do this:
function myCalc($data = [], $param = 15) {
return (array_sum($data) / count($data)) * $param;
}
And in the SQL query:
select (SUM(campo_numerico) / COUNT(campo_numerico)) * 15) AS calculo from TABELA order by ID desc LIMIT 5;
or
select (AVG(campo_numerico) * 15) as calculo from TABELA order by ID desc LIMIT 5;