Grouping and Aggregating

Version 2.2 includes the ability to group and aggregrate data. This is really useful, it allows you slice data by day and apply operations like MIN, MAX and AVG.

We’re going to work through an example that will show the MINimum temperature and the MAXimum temperature for each day for one of my sensors.

The new attributes are applied to the mqttcogs_data shortcode. The resulting grouping and aggregation is done at the server*.

We’ll dive straight into the example to see how they work.

[ mqttcogs_data
topics="mysensors_out/92/1/1/0/0,mysensors_out/92/1/1/0/0"
from="-30"
group="DAY"
aggregations="MIN,MAX"]

Here’s the corresponding explanation of each of the attributes for this example.

topics: Extracts temperature data for the topic mysensors_out/92/1/1/0/0. It extracts data for the same topic twice as we want to compare max and min values.

from: Restricts data to the last 30 days

group: Groups the data for each topic based on the DAY. Note that grouping can be anything that the MySQL EXTRACT funtion supports.

aggregrations: Applies the aggregation to the grouped data for each topic. This attribute should contain a comma delimited list of aggregations. One for each topic. Note that the aggregation can be anything that the MySQL group by operation supports.

Heres my full shortcode and underneath my graph!

[ mqttcogs_drawgoogle options="{height:200}"]
	[ mqttcogs_data  
		topics="mysensors_out/92/1/1/0/0,mysensors_out/92/1/1/0/0" 
		aggregations="MIN,MAX" 
		from="-30" 
		group="DAY"]
[/mqttcogs_drawgoogle]

The graph shows the MINimum temperature and the MAXimum temperature for each day. Neat!

*Note: It is also possible to group and aggregrate data client-side using the ‘script’ attribute of the mqttcogs_drawgoogle shortcode




No Comments


You can leave the first : )



Leave a Reply

Your email address will not be published. Required fields are marked *