Sum mean by tag - possibe?

1

Hello InfluxDB users,

I am using InfluxDB to log data from a RabbitMQ stream, which works great! I just have one question about how to write a specific query and if that is even possible.

The data gathered is continuous data about power usage and solar power generated for a whole site of 16 buildings. The buildings are tagged in the time series, so if I want to get the solar production history for the last day for building X, that would look something like this;

SELECT mean(solar) FROM amqp_consumer WHERE id = 'X' AND time > now() - 1d GROUP BY time(1m)

this works as expected.

However, I would also like to know the solar production for the whole site and this is where I run into problems. Querying the mean for all values does not work, as it obviously averages all the buildings. Querying the sum of all values also does not work, as it gives me a total across time. What I would need is to get the mean per building (as in the query above) and add those together. Something like;

SELECT sum_by_time_slice(mean(solar)) FROM amqp_consumer WHERE time > now() - 1d GROUP BY id, time(1m)

I am currently doing this in the client and that works. I would like to do it directly from InfluxDB, though.

Is this possible?

2

Hi,

From my understanding of the database schema; I think if you further group by “building” it should give you the desired results e.g. below query:

SELECT mean(solar) FROM amqp_consumer WHERE time > now() - 1d GROUP BY time(1m),id

3

The database schema looks as follows (sorry for not mentioning before);

id (tag) - the 2 digit hexadecimal id of the sensor
time - obviously
main_0 - the first phase of the main connection
main_1 - second phase
main_2 - third phase
solar - solar power generating at this timestamp

If I do the query I typed out above for a specific, I get 1440 data points, which is correct; number of minutes in a day. If I use the query without the WHERE id = ‘xx’ and also group by id, I get WAY more data points; 1440 for each sensor in there. I then post-process those to add together the values per sensor for each point in time.

So while your suggestion gets me the right data, it does not yet come in the format I want. I was just wondering if I can achieve that directly using a smart query.