Difference between value and daily mean

,
1

Hello,

I’m trying to build a query that calculates the difference between the current value and the daily mean.

This is one example that I tried:

SELECT "mean_value" - "mean_precio_promedio" AS "difference" FROM (
     SELECT mean("value") AS "mean_precio" FROM "db"."autogen"."serie" WHERE time > :dashboardTime: GROUP BY :interval: ; 
     SELECT mean("value") AS "mean_precio_promedio" FROM "db"."serie" WHERE time > :dashboardTime: GROUP BY time(1d)
)

Is this possible?

I’m using Chronograf to visualize the query.
influxdb:1.3.5
chronograf:1.3.8

Regards,

Silva

2

Hello Silva,
I tried the following and it worked:

create database diff
insert measurement,tag=value value=40 1535046885000000000
insert measurement,tag=value value=30 1535018084000000000
insert measurement,tag=value value=20 1535014484000000000
insert measurement,tag=value value=10 1534935284000000000
insert measurement,tag=value value=5 1534931684000000000
insert measurement,tag=value value=0 1534935284000000000> precision rfc3339
select * from “measurement”
name: measurement
time tag value

2018-08-22T09:54:44Z value 5
2018-08-22T10:54:44Z value 0
2018-08-23T08:54:44Z value 20
2018-08-23T09:54:44Z value 30
2018-08-23T17:54:45Z value 40

Select last(value) - mean(“value”) from “measurement” group by time(1d) fill(none)
name: measurement
time last_mean

2018-08-22T00:00:00Z -2.5
2018-08-23T00:00:00Z 10

However, you might want to look into implementing a continuous query to make a meaningful visualization in Chronograf.

Does this help?

Best,

Anais