Hi there.
I found a (workaround?/)solution to get the positive value change per day.
I have 2 databases:
- test
- test2
These are my raw values in database test
> select value from "autogen"."pulsethingy"
name: pulsethingy
time value
---- -----
1521370800000000000 50001
1521388800000000000 50002
1521406800000000000 50003
1521424800000000000 50004
1521442800000000000 50005
1521460800000000000 50006
1521478800000000000 50007
1521496800000000000 50008
1521514800000000000 50009
1521532800000000000 50010
1521550800000000000 1
1521568800000000000 2
1521586800000000000 3
1521604800000000000 4
1521622800000000000 5
1521640800000000000 6
1521658800000000000 7
1521676800000000000 8
I calculate the positive value change over time, and store it in database test2
> select non_negative_difference(value) into "test2"."autogen"."pulsethingy" from "autogen"."pulsethingy"
name: pulsethingy
time non_negative_difference
---- -----------------------
1521388800000000000 1
1521406800000000000 1
1521424800000000000 1
1521442800000000000 1
1521460800000000000 1
1521478800000000000 1
1521496800000000000 1
1521514800000000000 1
1521532800000000000 1
1521568800000000000 1
1521586800000000000 1
1521604800000000000 1
1521622800000000000 1
1521640800000000000 1
1521658800000000000 1
1521676800000000000 1
Then I group it by day
> use test2
Using database test2
> select sum("non_negative_difference") from "autogen"."pulsethingy" group by time(1d)
name: pulsethingy
time sum
---- ---
1521331200000000000 2
1521417600000000000 5
1521504000000000000 4
1521590400000000000 4
1521676800000000000 1
Which gives me the positive change per day.
It does not seem possible to use one query (with a subquery) to get to this same result.
> use test
Using database test
> select sum("non_negative_difference") from (select non_negative_difference(value) from "autogen"."pulsethingy") group by time(1d)
ERR: aggregate function required inside the call to non_negative_difference
I do not understand why I have to store the result of the first query in a separate measurement before I can process it further. Is this workaround something I can avoid?