What is the equivalent of this query
select sum("count") from "measurement_name" where time<now() and time>now()-4d group by time(100s),"source"
in Flux? I have tried
from(bucket:"metrics/default_metrics")
|> range(start: -4d)
|> filter(fn: (r)=> r._measurement == "measurement_name")
|> group(columns: ["source"])
|> window(every: 100s)
|> drop(columns:["_start","_stop","_measurement","column_a","column_b"])
|> yield()
and
from(bucket:"metrics/default_metrics")
|> range(start: -4d)
|> filter(fn: (r)=> r._measurement == "measurement_name")
|> window(every: 100s)
|> group(columns: ["source"])
|> drop(columns:["_start","_stop","_measurement","column_a","column_b"])
|> yield()
but they all seem to yield different results
This is grouping by time_interval = 100s and source. Supposedly, the grouping by time (and the sum aggregation implicitly?) is done using the window function from Flux but the result from the InfluxQL query (select...) are:
name: measurement_name
tags: source=source_name
time sum
---- ---
1601022500000000000 39
1601022600000000000 191
1601022700000000000 232
1601022800000000000 145
1601022900000000000 207
1601023000000000000 277
1601023100000000000 160
1601023200000000000 228
1601023300000000000 253
1601023400000000000 167
while the one coming from the Flux queries is
Table: keys: [source]
source:string _time:time _value:int _field:string
---------------------- ------------------------------ -------------------------- --------
source_name 2020-09-25T11:46:51.390000000Z 6 count
source_name 2020-09-25T11:46:54.124000000Z 5 count
source_name 2020-09-25T11:46:57.616000000Z 6 count
source_name 2020-09-25T11:46:57.999000000Z 9 count
source_name 2020-09-25T11:46:58.064000000Z 3 count
source_name 2020-09-25T11:46:58.307000000Z 6 count
source_name 2020-09-25T11:47:01.011000000Z 8 count
source_name 2020-09-25T11:47:03.634000000Z 6 count
source_name 2020-09-25T11:47:03.700000000Z 8 count
source_name 2020-09-25T11:47:04.144000000Z 8 count
The end goal is to plot this out in Grafana.
Is there also maybe a way to convert back and forth between these two paradigms? Whenever it's possible ofcourse
You need to include the sum() function explicitly. I suggest using aggregateWindow() too.
from(bucket:"metrics/default_metrics")
|> range(start: -4d)
|> filter(fn: (r)=> r._measurement == "measurement_name")
|> group(columns: ["source"])
|> aggregateWindow(every: 100s, fn: sum)
|> drop(columns:["_start","_stop","_measurement","column_a","column_b"])
|> yield()
Generally, group does not preserve sort order but aggregateWindow sorts by time before doing its work. That's something to look for. Additionally, the time bounds might not line up exactly between the Flux and InfluxQL queries. I expect them to, but double check it.