Process query for a vector of inputs in a single operation

Question

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?

Does [this question](http://stackoverflow.com/q/2793994/1625955) help you? — Jens, Feb 13 '15 at 08:19
Yes it does!, I'll post the final query when I get it as I want. — Santi Peñate-Vera, Feb 13 '15 at 08:32

score 0 · Answer 1 · answered Feb 13 '15 at 08:54

The answer is this:

SELECT   FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(timestamp_)/(15 * 60))*15 * 60) AS timekey, (AVG(active_power)/1000) as avg_pow, sensor_id
FROM     solar_forecast.data
GROUP BY timekey, sensor_id;

This way I convert the datatime to unixtime, then sort it by 15 min intervals, and then re convert it to datetime.

Process query for a vector of inputs in a single operation

1 Answers1