Writing a query for total metrics number, aka sum() by all tags, then mean() by time intervals

,
1

Hi,

one of the internal InfluxDB’s performance metrics, numSeries, reports per-database number of series at each point in time:

> select "database", "numSeries" from "database" where time > now() - 1h
name: database
time                 database  numSeries
----                 --------  ---------
2017-03-20T23:51:00Z _internal 108
2017-03-20T23:51:00Z traffic   106829
2017-03-20T23:51:10Z _internal 108
2017-03-20T23:51:10Z traffic   106829
<...>

Now I would like to graph total number of series over time, i. e. mean(...) with grouping by time.

However, I cannot just write SELECT mean("numSeries") from "database" where time > now() - 1h GROUP BY time(1m) as that would give me an average number of series over all databases (which is rather useless). I must rather compute a mean (over time) of sums (over all databases).

I wrote this query:

SELECT sum("numSeries")
FROM (
	SELECT mean("numSeries") as "numSeries"
	FROM "database"
	WHERE time > now() - 1h
	GROUP BY time(1m), "database"
)
WHERE time > now() - 1h
GROUP BY time(1m)

However, I don’t like it because I have to duplicate the WHERE clause and twice GROUP BY time(1m).

So: how do I properly compute mean of sums (or sum of means) in one query?

1 Like