Hello everyone,
I am trying to find a way to calculate the value of every datapoint as a percent of its daily mean (i.e. this point was 135% of the daily mean, that one was 23%), and its standard score. I am using InfluxDB v1.4 and Grafana v5.1.4.
The two queries that I am using for the following graph are:
SELECT mean("value") FROM "generic"."InfluxDB" WHERE $timeFilter GROUP BY time(1d) fill(null)
and
SELECT "value" FROM "generic"."InfluxDB" WHERE $timeFilter
Intuitively, I thought that the query which would give me the percent of daily mean was going to be
SELECT ("value") / mean("value") FROM "generic"."InfluxDB" WHERE $timeFilter GROUP BY time(1d) fill(null)
- for standard score it would be
("value" - mean("value")) / stddev("value")
However, InfluxDB, like most databases, does not support mixing aggregated data and unaggregated data in the same query.
Does anybody know how to go about this?
I have looked into subqueries a bit, which looked promising but haven’t gotten me much further.
Any guidance is appreciated.
Regards,
Toby
In theory, mean("value") and stddev("value") are “constants”, so you can precalculate them as dashboard variables and then use their values in graph query: SELECT ("value" - $mean) / $stddev ... - but it has some drawbacks.
Thanks for the feedback.
This workaround works well if I only have one or two values that I’m tracking in a dashboard. But, as you mentioned drawbacks, it scales very poorly. For example I also want to group this data by a tag, and then have each group have its own standard score. This tag list, in my case, is very long (several hundreds) and creating two template variables for each tag value is…impractical, to say the least.
Any ideas?
Try to create new measurement or update existing one with the fields, where you store also daily mean and stdev - then you can do a calculation at the InfluxDB level. If you want, then code your own Grafana panel - there are some similar panels, but not for graphs - https://grafana.com/plugins/blackmirror1-singlestat-math-panel
Just a question, how were you able to plot the mean line? (Assuming that the green line in your photo is the mean)
