Query to count values per day

Fluxlang
, , ,
1

I created a query that will count the number of occurrences of the Offset field in a range of four days aggregated day by day:

from(bucket: "my_bucket")
	|> range(start: -4d, stop: today())
	|> filter(fn: (r) => 
		r[\"_measurement\"] == "measurement1" 
		and r[\"SuitId\"] == "xxxyyyzzzvvv"
		and r._field == "Offset"
	)
	|> group(columns: ["_start"])
	|> aggregateWindow(every: 1d, fn: count)
	|> sort(columns: ["_time"], desc: true)"

the result i get is something like this

_value=90010 _time=today   
_value=50 _time=yesterday    
_value=0 _time=the day before

Witch looks fabulous, except the days are not matching the data. In the table the 90010 corresponds to yesterday and 50 corresponds to the day before. I don’t understand what i’m doing wrong. Can someone point at where i’m wrong. many thanks.

2

@rucaza When you use aggregateWindow(), the aggregate function (count) actually drops the _time column because it isn’t part of the group key and it doesn’t know how to aggregate the time value. By default aggregateWindow() duplicates the _stop column as the new _time column to give each row a _time value for the window the row represents.

You can control which column aggregateWindow() duplicates as the output time with the timeSrc parameter. In your case, I’d suggest setting timeSrc: "_start":

// ...
    |> aggregateWindow(every: 1d, fn: count, timeSrc: "_start")
// ...
3

Hello All,

I am writing to ask how I might create a SQL query to obtain a count of the number of records for each day “column1” has a value of “value1”

The output I would like to see in each row has two columns e.g holiday counting, the date, and then the number of times “value1” appeared in “column1” that for that day. Any help is greatly appreciated.

Thanks in advance,