single query to group data

Question

I'm saving data in MySQL database every 5 seconds and I want to group this data in an average of 5 minutes.

The select is this:

SELECT MIN(revision) as revrev, 
    AVG(temperature), 
    AVG(humidity) 
    FROM dht22_sens t 
    Group by revision div 500 
    ORDER BY `revrev` DESC

Is possible to save data with a single query possibly in the same table?

Check out `insert ... select ...` statement – Shadow Dec 30 '16 at 21:58 — Shadow, Dec 30 '16 at 21:58

score 1 · Accepted Answer · edited May 23 '17 at 12:16

If it is about reducing the number of rows, then I think you have to insert a new row with aggregated values and then delete the original, detailed rows. I don't know any single sql statement for inserting and deleting in one rush (cf also a similar answer from symcbean at StackOverflow, who additionally suggests to pack these two statements into a procedure).

I'd suggest to add an extra column aggregationLevel and do two statements (with or without procedure):

insert into dht22_sens SELECT MIN(t.revision) as revision, 
    AVG(t.temperature) as temperature, 
    AVG(t.humidity) as humidity,
    500 as aggregationLevel
    FROM dht22_sens t
    WHERE t.aggregationLevel IS NULL;

delete from dht22_sens where aggregationLevel is null;

This is perfect, I add aggregatiolevel colum and I add where revision > NOW() - interval 1 day to aggregate only the "old" data. — MadCat, Dec 30 '16 at 22:45

single query to group data

1 Answers1