Using InfluxDB subquery to subtract values

12
votes

I have a Influx database that is getting filled with values. These values are presented by Grafana. What I need is to get the actual values depending on the selected time interval.

Currently I have the following query for a single metric:

SELECT mean("value") FROM "table" WHERE $timeFilter GROUP BY time($interval) fill(null)

What I want is to subtract the lowest value from that interval, so it only counts the values from within that interval. So the graph needs to start at zero. To get the lowest value from that interval I use:

SELECT min("value") FROM "table" WHERE $timeFilter

So I thought combining those two (with a subquery) like this should work:

SELECT mean("value") - (SELECT min("value") FROM "table" WHERE $timeFilter) FROM "table" WHERE $timeFilter GROUP BY time($interval) fill(null)

Unfortunately this doesnt work. The query is not accepted as a subquery.

1
influxdbgrafana
What version of Influx are you using? In principle in 1.2 should be possible to do something like that. – Pigueiras
I am using InfluxDB shell version: 1.2.0. I know subquerys are possible, but as far as the documentation goes only in the 'FROM' section. The documentation does not give examples of other types of subquery's. – Nhz
You are completely right, it looks like it is not possible. I thought in some hack doing templating with grafana, but it doesn't work because it shows the timestamp instead of the min("count"). I guess the best you can get is to calculate the min and the substract the constant from the query :( – Pigueiras
Thanks for the confirmation. What do you mean with 'subtract the constant'? Could you explain how this works? – Nhz
I mean: SELECT mean("value") - 123 FROM ... – Pigueiras

1 Answers

1
votes

This is possible using InfluxDB's built-in functions:

SELECT cumulative_sum(difference((mean("value")))) FROM "table" WHERE $timeFilter GROUP BY time($interval) fill(null)

This takes the difference between consecutive data points (ignoring the absolute value), and cumulatively sums this over the selected time range.

I'm not entirely sure, but this query requires an additional mean() associated with the GROUP BY clause. Perhaps it's better to use max or first instead, depending on your needs.