I am trying to create a daily cumulative snapshot. My dataset does not necessarily have a record for every day though, so I use time(1d) to have InfluxDB generate a record if there is none. All good so far. When I then apply the CUMULATIVE_SUM function, the additional records generated by time(1d) are disappearing - this is very strange … here an example to reproduce the scenario:
I use this dummy dataset for the queries:
# since we use unix timestamp in seconds we have
# to switch to precision in seconds
precision s
INSERT events,user=Ralph amount=70.0 1483272804
INSERT events,user=Ralph amount=-20.0 1483643984
INSERT events,user=Ralph amount=-80.0 1483841484
SELECT * FROM events
name: events
time amount user
---- ------ ----
1483272804 70 Ralph
1483643984 -20 Ralph
1483841484 -80 Ralph
# switching back to readable date format
precision rfc3339
SELECT * FROM events
name: events
time amount user
---- ------ ----
2017-01-01T12:13:24Z 70 Ralph
2017-01-05T19:19:44Z -20 Ralph
2017-01-08T02:11:24Z -80 Ralph
I first tried to get stats for every day:
SELECT SUM(amount) FROM events WHERE time >= '2017-01-01' AND time <= '2017-01-10' GROUP BY time(1d)
name: events
time sum
---- ---
2017-01-01T00:00:00Z 70
2017-01-02T00:00:00Z
2017-01-03T00:00:00Z
2017-01-04T00:00:00Z
2017-01-05T00:00:00Z -20
2017-01-06T00:00:00Z
2017-01-07T00:00:00Z
2017-01-08T00:00:00Z -80
2017-01-09T00:00:00Z
2017-01-10T00:00:00Z
What I’d like to do next is get the cumulative sum:
SELECT CUMULATIVE_SUM(SUM(amount)) FROM events WHERE time >= '2017-01-01' AND time <= '2017-01-10' GROUP BY time(1d)
name: events
time cumulative_sum
---- --------------
2017-01-01T00:00:00Z 70
2017-01-05T00:00:00Z 50
2017-01-08T00:00:00Z -30
As you can see, the fill records are gone from this result, which is unfortunate, however, if we change the inner aggregation function to a COUNT, they are back again (so this seems to be more like a bug?):
SELECT CUMULATIVE_SUM(COUNT(amount)) FROM events WHERE time >= '2017-01-01' AND time <= '2017-01-10' GROUP BY time(1d)
name: events
time cumulative_sum
---- --------------
2017-01-01T00:00:00Z 1
2017-01-02T00:00:00Z 1
2017-01-03T00:00:00Z 1
2017-01-04T00:00:00Z 1
2017-01-05T00:00:00Z 2
2017-01-06T00:00:00Z 2
2017-01-07T00:00:00Z 2
2017-01-08T00:00:00Z 3
2017-01-09T00:00:00Z 3
2017-01-10T00:00:00Z 3
So my question is, how can I keep the fillers with CUMULATIVE_SUM(SUM(amount))?
