Flux: Sum from Sum and Count

1

Hello, trying to sum 2 sums in flux and it just not work :frowning:

Individual values are working just fine but I did not find the way how to sum two function (reject and pass) in flux like that

result = reject + pass

See below my custom functions to calculate individual values

countqty = (counttrigtag) =>
from(bucket: “dbase1”)
|> range($range)
|> filter(fn: (r) =>
r._measurement == “quantity” and r.counttrigtag == counttrigtag and r._field == “countfield”)
|> sum(column: “_value”)

countyel = (lighttrigtag) =>
from(bucket: “dbase1”)
|> range($range)
|> filter(fn: (r) =>
r._measurement == “interval” and r.lighttrigtag == lighttrigtag and r._field == “lightfield”)
|> count(column: “_value”)

reject = countqty(counttrigtag: “reject”)
pass = countyel(lighttrigtag: “yellow”)

2

@salvq Right now, you have two streams of tables, reject and pass. To get a sum need to union the two streams of tables together with union(), then group everything into a single table with group() and calculate a sum with sum()

countqty = (counttrigtag) =>
    from(bucket: “dbase1”)
        |> range($range)
        |> filter(fn: (r) =>
            r._measurement == “quantity” and r.counttrigtag == counttrigtag and r._field == “countfield”)
        |> sum(column: “_value”)

countyel = (lighttrigtag) =>
    from(bucket: “dbase1”)
        |> range($range)
        |> filter(fn: (r) =>
            r._measurement == “interval” and r.lighttrigtag == lighttrigtag and r._field == “lightfield”)
        |> count(column: “_value”)

reject = countqty(counttrigtag: “reject”)
pass = countyel(lighttrigtag: “yellow”)

union(tables: [reject, pass])
  |> group()
  |> sum()
3

@scott so easy :slight_smile: I was on the right path, I did to union but not further :slight_smile:

Appreciate quick help

See updated function, I was thinking about sum as plus therefore did solve all my desired fucntions :slight_smile: How to restructure command below to apply math like multiplication, deduction or divide for example get result of (total-reject)/total ?

Function

calc = (countlfd) =>
from(bucket: "dbase1")
  |> range($range)
  |> filter(fn: (r) =>
    r._measurement == "quantity" and
    r._field == "countfld" and
    r.trigger == countlfd)
|> drop(columns: ["msgid", "green", "red", "yellow"])
|> sum(column: "_value")

reject = calc(countlfd: "reject")
total = calc(countlfd: "cycle")
union(tables: [reject, total])

Result after union

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,string,string,string,string,long
#group,false,false,true,true,true,true,true,true,false
#default,_result,,,,,,,,
,result,table,_start,_stop,_field,_measurement,deviceid,trigger,_value
,,0,2020-01-17T12:55:12.548614811Z,2020-01-17T13:55:12.548614811Z,countfld,quantity,device1,cycle,60
,,1,2020-01-17T12:55:12.548614811Z,2020-01-17T13:55:12.548614811Z,countfld,quantity,device1,reject,16
4

@salvq This is where map() comes in handy. Map rewrites each row in a table based on a function you provide in the map() call. In that function, you can perform these types of mathematical operations.

For this to work in your use case, you need to pivot your unioned stream of tables so that cycle and reject become columns in the same row. After the data is pivoted, you can then use map() to do your mathematical operations. Also, I’d convert your integers to floats to keep the calculations as precise as possible:

// assume the rest of your query above is here
// ...
union(tables: [reject, total])
  |> pivot(rowKey:["_time"], columnKey: ["trigger"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with percent_rejected: (float(v: r.reject) / (float(v: r.reject) + float(v: r.cycle))) * 100.00 }))

You can see other examples in the Mathematic operations guide.

5

@scott thanks for your guidance and example

Finally I ended up with 2 queries which are giving me the same result.

Which one is better (more efficient) or how to evaluate which to pick ?

1st function

join(tables:{reject:reject, total:total}, on:["_start"])
|> map(fn:(r) => ({
_value: ((float(v: r._value_total) - float(v: r._value_reject)) / float(v: r._value_total)) * 100.00 }))

2nd function

union(tables: [reject, total])
|> pivot(rowKey:["_start"], columnKey: ["trigger"], valueColumn: "_value")
|> map(fn: (r) => ({ rejected: ((float(v: r.cycle) - float(v: r.reject)) / float(v: r.cycle)) * 100.00}))
6

@salvq From an optimization perspective, I’d go with the 1st function. join and map can both be pretty memory hungry, but with this approach, you’re only performing two transformations on your data. With the 2nd approach, you’re performing three transformations. union isn’t particularly heavy, but both pivot and map can be.

It’d be interesting to see a side-by-side performance comparison between the two approaches, but my gut says the 1st approach would perform a little better.

7

@scott got it, thanks for help