I have the following table from a MySQL database:
Schema name: solar
Table name: data
Columns:
sensor_id int(11) PK
timestamp_ timestamp PK
active_power double
apparent_power double
active_energy double
current_ double
voltage double
frequency double
And this query...
SELECT '2014-03-09 08:00:00' as 'From', '2014-03-09 09:00:00' as 'To', sensor_id, (AVG(active_power)/1000) as 'Average power (W)'
FROM solar.data
WHERE timestamp_ BETWEEN '2014-03-09 08:00:00' and '2014-03-09 09:00:00'
GROUP BY sensor_id
The query processes just one interval (from 8:00 to 8:10) however I would like to process in a single query a complete day in 10 minutes intervals.
In a regular programming language like C one would provide a vector of dates and repeat the query many times, however this SQL query is executed over a record of 13M entries, and it is computationally expensive already for only one date. I imagine that with a smarter SQL query the final result can be obtained much quicker, but I have no idea how.
Is it possible to process a day in 10 minutes intervals obtaining the average of the active_power columns for each interval and each sensor?