Help with cumulative sum please!

,
1

HI all, I need help with a cumulativesum() - I’ve worked tirelessly at this and still can’t get it right. I’m new to flux and having a hard time. This query below outputs a table (also below). I need to do a runningsum of the “discharge cycles” column as a 4th column in this table. Thank you all for any help!

ts = from(bucket: “rdata”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “site”)
|> filter(fn: (r) => r[“point_name”] == “kW”)

ts1 = ts |> aggregateWindow(every: 1d, fn: sum)
ts2 = ts |> aggregateWindow(every: 1d, fn: sum)

join(tables: {s1: ts1, s2: ts2 }, on: [“_time”], method: “inner”)
|> map(fn: (r) => ({ r with dischargemwhs: if r._value_s1 > 0.0 then r._value_s1 / 3600000.0 else 0.0 }))
|> map(fn: (r) => ({ r with chargemwh: if r._value_s2 < 0.0 then r._value_s2 / -3600000.0 else 0.0 }))
|> map(fn: (r) => ({ r with discharge_cycles: r.dischargemwh / 15.0 }))
|> drop(columns: [“_value_s1”, “_value_s2”, “_field_s1”, “_field_s2”, “_measurement_s1”, “_measurement_s2”, “_start_s1”, “_start_s2”, “_stop_s1”, “_stop_s2”, “point_name_s1”, “point_name_s2”, “register_s1”, “register_s2”, “root_s1”, “root_s2”, “site_controller_id_s1”, “site_controller_id_s2”, “site_name_s1”, “site_name_s2”, “source_device_id_s1”, “source_device_id_s2”, “site_controller_s1”, “site_controller_s2”, “source_device_type_s1”, “source_device_type_s2”])

image

2

@Marion_Akagi I simplified your query a little bit and it should be much more performant. You don’t need a join and I condensed the mulitple map() calls down to a single map() call. In that, I explicitly map only the columns you want returned so it drops everything else. It also duplicates the discharge_cycles columns as discharge_cycles_total. I then run cumulativeSum() on the discharge_cycles_total column:

from(bucket: "rdata")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "site")
    |> filter(fn: (r) => r["point_name"] == "kW")
    |> aggregateWindow(every: 1d, fn: sum)
    |> map(
        fn: (r) => {
            dischargemwhs = if r._value > 0.0 then r._value / 3600000.0 else 0.0
            chargemwh = if r._value < 0.0 then r._value / (-3600000.0) else 0.0
            discharge_cycles = dischargemwhs / 15.0

            return {
                time: r._time,
                dischargemwhs: dischargemwhs,
                chargemwh: chargemwh,
                discharge_cycles: discharge_cycles,
                discharge_cycles_total: discharge_cycles,
            }
        },
    )
    |> cumulativeSum(columns: ["discharge_cycles_total"])

Let me know if this solves your problem.

1 Like
3

This is AMAZING. Thank you so much! I’ll use this to modify some of my other queries to run faster. I appreciate you.

4

@scott It’s bringing up a “unsupported input type for sum aggregate: string”. the “kw” value is a number. Do I need to cast or something?

5

I added a row after aggregateWindow to fix the data type error and now it works! Adding my solution here to help others.

|> map(fn:(r) => ({ r with kWFl: float(v: r.kW) }))