CODEWITHSUNDEEP
time-seriesinfluxdb
Bazinga777
Bazinga777

Reputation: 5281

Influxdb - how to calculate sum of differences per second a the minute level

I want to query the sum per minute from the result obtained from another query that calculates the difference between subsequent values.

select sum(ph1), sum(ph2), sum(ph2) from (select 
non_negative_difference(day_chan1) as ph1,
non_negative_difference(day_chan2) as ph2, 
non_negative_difference(day_chan3) as ph3 
from electricity) 
group by time(1m) tz('Europe/Dublin')

For example if I get the following from the suqbquery 

time                 ph1 ph2 ph3
----                 --- --- ---
2017-04-02T14:40:38Z 0   0   2
2017-04-02T14:41:38Z 1   1   1
2017-04-02T14:41:39Z 0   0   2
2017-04-02T14:42:38Z 1   1   1
2017-04-02T14:42:39Z 0   1   2

I want to sum them up into 

  time                 ph1 ph2 ph3
    ----                 --- --- ---
    2017-04-02T14:40:00Z 0   0   2
    2017-04-02T14:41:00Z 1   1   3
    2017-04-02T14:42:00Z 1   2   3

but what I get from the query is aggregate function required inside the call to non_negative_difference but if I do the sub query on its own, it returns the results

Upvotes: 1

Views: 1741

Answers (1)

Olaf
Olaf

Reputation: 652

I was also looking a long time for this and I finally found the solution:

select sum(ph1), sum(ph2), sum(ph2) from (select

This is right. Now we want to add an aggregate function inside the non_negative_difference call (as the error also indicates). I assume you want to sum everything.

non_negative_difference(sum(day_chan1)) as ph1,
non_negative_difference(sum(day_chan2)) as ph2, 
non_negative_difference(sum(day_chan3)) as ph3 
from electricity

Now if we don't add the following line the group by function of the inside query will also be 1m. We don't want this since if a value is missing the way influx calculates sum this will result in a very large differnce. So we group this subquery by the smallest interval you have (e.g. 1s)

group by time(1s))

Finally you can group the outer query by the interval you would like the values to be added together.

group by time(1m) tz('Europe/Dublin')

Upvotes: 1

Related Questions