Hi there.
I want to determine the postive (value) change per day.
I’m tracking the output of a counter in a pulse sensor. Sometimes the counter resets, which messes up my queries when I group by day. I’ll show you what I’ve tried.
Let’s say I have the following data:
> select value from "autogen"."pulsesensor"
name: pulsesensor
time value
---- -----
1520679600000000000 50001
1520697600000000000 50002
1520715600000000000 50003
1520733600000000000 50004
1520751600000000000 50005
1520769600000000000 50006
1520787600000000000 50007
1520805600000000000 50008
1520823600000000000 50009
1520841600000000000 50010
1520859600000000000 1
1520877600000000000 2
1520895600000000000 3
day 1 = [50001, 50002, 50003]
day 2 = [50004, 50005, 50006, 50007, 50008]
day 3 = [50009, 50010, 1, 2]
day 4 = [3]
The counter resets on day 3, and starts counting from 1.
The following query works for day 1, 2 and 4:
> select spread(value) as value from "autogen"."pulsesensor" group by time(1d) ;
name: pulsesensor
time value
---- -----
1520640000000000000 2 # day 1
1520726400000000000 4 # day 2
1520812800000000000 50009 # day 3
1520899200000000000 3 # day 4
Day 3 gives the correct result for a spread function, but I actually want 2 as a result.
I cannot use derivative in combination with group by because then I have to use a nested InfluxQL function, which in turn loses accuracy. Especially on day 3.
I could do something like this:
> select "x" from (select last(value) - first(value) as "x" from "autogen"."pulsesensor" group by time(1d)) where "x" > 0
name: pulsesensor
time x
---- ----
1520640000000000000 2 # day 1
1520726400000000000 4 # day 2
1520899200000000000 3 # day 4
but then I lose day 3.
Now I found cumulative sum, which almost does what I want.
> select cumulative_sum(last(value)) as "x" from "autogen"."pulsesensor" group by time(1d)
name: pulsesensor
time x
---- ----
1520640000000000000 50003 # day 1
1520726400000000000 100011 # day 2
1520812800000000000 100014 # day 3
1520899200000000000 100021 # day 4
1520985600000000000 100029 # ?
If I had simply reset the counter to 0 every time I inserted a new value in the database, it would have worked perfectly. Unfortunately my sensor has no idea of when its values are extracted, so I cannot alter how it functions.
Does anyone know how I could query my data to get the positive value change per day?
TL;DR. I need a query to get the positive value change per day