Simple query language help in InfluxDB - creating an aggregate table

Question

I'm trying to learn the query language of InfluxDB to create an aggregate table, but I'm having issues. Here is my conundrum. Say I have a table like this:

name: temperature

time | value | metric | measurement

X | 25 | degrees(C) | temperature

X | 10 | degrees(C) | temperature

and one like this

name: cpu_usage

time | metric | value | measurement

X | %usage | 73 | cpu_usage

X | %usage | 75 | cpu_usage

How can I get these into a table like this:

name:aggregate

time | measurement | metric | mean value

X | temperature | degrees(C) | 17.5

X | cpu_usage | %usage | 74

basically, I want to create another table with the mean values from different tables. however, i ALSO want to preserve some of the column names from other tables. Right now, I'm having the issue that you can't mix aggregate and non-aggregate functions, so it won't let me do "SELECT metric, measurement, mean(value) FROM cpu_usage INTO aggregate. And, I have no idea how to merge/join tables (I don't think it's supported anymore), so if i were to calculate the average, store it in a temp table, i wouldn't know how to then combine it with the current tables.

Michael Desa · Accepted Answer · 2016-05-23 18:24:30Z

It's hard to tell from your example above whether metric and measurement are tags or fields. Assuming that they are tags so the data you're working with looks like the following

temperature,metric=degrees(C),measurement=temperature value=25 X
temperature,metric=degrees(C),measurement=temperature value=10 X

cpu_usage,metric=%usage,measurement=cpu_usage value=75 X
cpu_usage,metric=%usage,measurement=cpu_usage value=73 X

Then the queries

SELECT mean(value) as usage INTO aggregate FROM cpu_usage GROUP BY *
SELECT mean(value) as temp INTO aggregate FROM temperature GROUP BY *

Should give you the output you're looking for.

That being said, this is not a best practice.

I would recommend doing something more like the following:

Write points that look like:

temperature,metric=degrees(C) value=25 X
temperature,metric=degrees(C) value=10 X

cpu_usage,metric=%usage value=75 X
cpu_usage,metric=%usage value=73 X

And have queries that write the measurements into different retention policies:

SELECT mean(value) as value INTO mydb.aggregate.cpu_usage FROM cpu_usage GROUP BY *
SELECT mean(value) as value INTO mydb.aggregate.temperature FROM temperature GROUP BY *

Collectives™ on Stack Overflow

Simple query language help in InfluxDB - creating an aggregate table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related