Selecting multiple values/aggregators from Influxdb, excluding time

Question

I got a influx db table consisting of

> SELECT * FROM results
name: results
time                artnum   duration 
----                ------   --------   
1539084104865933709 1234     34  
1539084151822395648 1234     81  
1539084449707598963 2345     56  
1539084449707598123 2345     52

and other tags. Both artnum and duration are fields (that is changeable though). I'm now trying to create a query (to use in grafana) that gives me the following result with a calculated mean() and the number of measurements for that artnum:

artnum   mean_duration  no. measurements
------   --------       -----  
1234     58             2
2345     54             2

First of all: Is it possible to exclude the time column? Secondly, what is the influx db way to create such a table? I started with

SELECT mean("duration"), "artnum" FROM "results"

resulting in ERR: mixing aggregate and non-aggregate queries is not supported. Then I found https://docs.influxdata.com/influxdb/v1.6/guides/downsampling_and_retention/, which looked like what I wanted to do. I then created a infinite retention policy (duration 0s) and a continuous query

> CREATE CONTINUOUS QUERY "cq" ON "test" BEGIN 
SELECT mean("duration"),"artnum"
INTO infinite.mean_duration 
FROM infinite.test 
GROUP BY time(1m) 
END

I followed the instructions, but after I fed some data to the db and waited for 1m, `SELECT * FROM "infinite"."mean_duration" did not return anything.

Is that approach the right one or should I continue somewhere else? The very goal is to see the updated table in grafana, refreshing once a minute.

Jan Garaj Jan Garaj · Accepted Answer · 2018-10-09T16:57:50

InfluxDB is a time series database, so you really need the time dimension - also in the response. You will have a hard time with Grafana if your query returns non time series data. So don't try to remove time from the query. Better option is to hide time in the Grafana table panel - use column styles and set Type: Hidden.

InfluxDB doesn't have a tables, but measurements. I guess you need query with proper grouping only, no advance continous queries, etc.. Try and improve this query*:

SELECT 
  MEAN("duration"), 
  COUNT("duration") 
FROM results
  GROUP BY "artnum" fill(null)

*you may have a problem with grouping in your case, because artnum is InfluxDB field - better option is to save artnum as InfluxDB tag.

Selecting multiple values/aggregators from Influxdb, excluding time

1 Answers