Get the mean of 4 days per 15 minutes

Fluxlang
, , ,
1 
 I have this query

import "date"
days = date.truncate(t: -28d, unit: 1d) from(bucket: "my_data")
|> range(start: days)
|> filter(fn: (r) => r["_measurement"] == "my_portal")
|> filter(fn: (r) => r["_field"] == "EDC")
|> filter(fn: (r) => date.weekDay(t: r._time) == 2)

this will return the data of the last 4 Tuesdays
Each day contains data per 15 minutes
Now I want to combine these 4 days into one day which contains the average of these 4 Tuesdays per 15 minutes
My device sends data every 15 minutes

Schermafbeelding 2023-05-24 155459
Schermafbeelding 2023-05-24 155459885Ă—255 23 KB

I want to get the average of these 4 Tuesday for each 15 minutes
for example
Tuesday 2/5/2023 06:30 value is 0.20
Tuesday 9/5/2023 06:30 value is 0.10
Tuesday 16/5/2023 06:30 value is 0.40
Tuesday 23/5/2023 06:30 value is 0.20
So I will get the average of these 4 days at 06:30 = 0.225
And then at 6:45 and 07:00 and so on
What should I add to the query in order to do that?
ps: I am using node js client to query the data

2

group per day, then use mean function:
add this to your query after the last filter

  |> window(every: 1d)  //groups per day, you may need to use some kind of offset depending on the "time zone" you are working with.
  |> mean(column: "_value") //once you have 1 table per day mean function will return the mean on that sub group
  |> group() //this removes all groups, to combine all previous results on one single table.

one second tough, the previous code will work on the average per day but if think is not what you described, if you want the average of each given day at 15 min you may need to do 2 groups: first group per day second group times in that case. instead of window, extract the time and day from the _time using the date library, on a new column and group by time and date.

3

I don’t want to get the average per day
My final result should be the mean of the 15 minutes of these 4 days
So one single day with 96 rows each represent the average of that time (10:15 represent the average of the last 4 days at 10:15)

4

Eva verbruiksprofiel
Eva verbruiksprofiel2000Ă—1455 115 KB

This shows what I really want

5

This is the way then:

BRB with the code

6 
import "strings"
import "date"

from(bucket: "Bucketname")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_field"] == "fieldname")
  |> filter(fn: (r) => r["tag"] == "tag1" or r["tag"] == "tag2" or r["tag"] == "tag3")
  |> aggregateWindow(every: 15m, fn: last, createEmpty: false)

  
  |> filter(fn: (r) => date.weekDay(t: r._time) == 2)
  //|> map(fn: (r) => ({ r with Time: string(v: date.hour(t: r._time)) + ":" +  string(v: date.minute(t: r._time)) + ":" +  string(v: date.second(t: r._time)), })) 
  |> map(fn: (r) => ({ r with  Time: strings.substring(v: string(v: r._time), start: 11, end:19)})) 
  |> group(columns: ["tag", "Time"], mode:"by") 
  |> mean(column: "_value")    
  |> yield(name: "last")
1 Like
7

image
image882Ă—107 1.98 KB

Thank you man
This works I think but there is only a small problem
This offset the time 15 minutes when showing the average
For example in the pic you see it shows the mean of 10:30 but it is actually the mean of 10:45
Why is that and how to fix it?

8

I gues this was because using “last” method
|> aggregateWindow(every: 15m, fn: last, createEmpty: false)
Commenting this out solved the problem

9

yes, that part actually depends on your data scheme, I let that line because on the bucket I used to test, I have data every 2s so I use an aggregate window of 15 min and mean, which means it will take 15 minutes worth of data and give me the mean and then it stores that value at the last timestamp.

but if you have on data point each 15 minutes that entire line is not necessary.

1 Like