How to query cumulativeSum include sum in past data?

InfluxDB 2
,
1

I have a messure balance with data like this.

1
1859×302 14.3 KB

amount field is number of money add more to balance
I want to query cumulative balance from start of month to draw a blance line chart.

from(bucket: "default")
  |> range(start: 2023-06-01)
  |> filter(fn: (r) => r["_measurement"] == "balance")
  |> filter(fn: (r) => r["_field"] == "amount")
  |> cumulativeSum(columns: ["_value"])  
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: true)
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> yield(name: "mean")

But the the returning cumulative data is only evaluated from 2023-06-01.

_time                                        amount
2023-06-02T00:00:00.000Z	1
2023-06-03T00:00:00.000Z	2
2023-06-03T02:09:14.275Z	3

Actually, I need data is total balance like this (include total balance before 2023-06-01)

_time                                        amount
2023-06-02T00:00:00.000Z	3
2023-06-03T00:00:00.000Z	4
2023-06-03T02:09:14.275Z	5

How could I solve this?

2

Hi,
Try to use the below flux query.

from(bucket: “sample”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “sample1” and (r[“tag”] == “tag1”))
|> filter(fn: (r) => r[“field”] == “field1”)
|> filter(fn: (r) => r[“field2”] == “field2”)
|> cumulativeSum()
|> group()

It will work.

3

Thank you for your help.
But your query will return data the same as my query.
I want to each record is total cumulative balance from 0 to that record time, not only cumulative from 2023-06-01.
The range is start from 2023-06-01, but data to calculate cumulative balance should be from 0.

4

I found a solution that return what I want.

from(bucket: “default”)
|> range(start: 0)
|> filter(fn: (r) => r[“_measurement”] == “balance”)
|> filter(fn: (r) => r[“_field”] == “amount”)
|> cumulativeSum(columns: [“_value”])
|> aggregateWindow(every: 1d, fn: sum, createEmpty: true, timeSrc: “_start”)
|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> filter(fn: (r) => r[“_time”] >= 2023-06-01)
|> yield(name: “mean”)

But will this cause a performance issue? Because we need to calculate aggregateWindow from 0.
This query will be overheat if aggregateWindow every 1h

5

Please read the basic concepts, the range is the range on wich you will query the data, you use aggregate window to down sample on windows of time, if you use an aggregate window with sum that operation is down sampling and just returning then sum of all data points in each window.

Performance issue will be somethink like not ussing an agregate function at all and then trying to do cumulative sum. But it is a “performance issue” relative tonthe ammount of data points you have in the bucket, I.E. If it is one datapoint per hr vs if it is 1 datapoint per second