Group values not common time value

InfluxDB 1
1

Hello,

I would like to group values ​​not common time value.

For example,
From Monday to Sunday, I have data (averaged by inflow) hour by hour.
I would like to group everything into “a single” day and average the values ​​hour by hour.
(Monday at 9:00 = 10° and Wednesday at 9:00 = 15° then the average for 9:00 is 12.5°)

Or ,
From January to December, I have hour by hour values. I would like to make a “typical day” representative of all these days.

I use the Flux language but if you would like to give me an equivalent in QL I am interested given the depreciation.

Today, I have no idea how this was achieved. I hope I was clear on my explanations and hope to get some help.
Thank you.

2

@Yohan This specific query isn’t possible with InfluxQL. It is possible with Flux and, looking forward to InfluxDB v3, with SQL.

Here’s how you’d do it in Flux:

import "date"

from(bucket: "example-bucket")
    |> range(start: -1mo)
    |> filter(fn: (r) => r._measurement == "example-measurement")
    |> filter(fn: (r) => r._field == "example-field")
    |> map(fn: (r) => ({r with hour: date.hour(t: r._time)}))
    |> group(columns: ["hour"])
    |> mean()
    |> group()
    |> sort(columns: ["hour"])

If you’re using InfluxDB Cloud Serverless, InfluxDB Cloud Dedicated, or InfluxDB Clustered you could use SQL. Otherwise, you’ll have to wait until the open-source version of v3 is available.

3

Thank you for your reply

This is what it looks like in XY Chart :

graphique profil journée
graphique profil journée959×393 26 KB

But I’m not sure this is the expected result.

I want the _values for each day of the period to be grouped together as a sum per hour. Then, the script should perform the daily average for each hour.

But the result looks like this.

Edit :
Edit:

Now it works with this function:

|> aggregateWindow(every: 1h, fn: sum, createEmpty: false)

Can you tell me how I can do the same thing for a week from Monday to Sunday? (With hourly averaged values of every Monday, every Tuesday, etc.)

4

I’m not sure I correctly understand exactly what you’re looking for, but if I do, this is how you’d do it:

import "date"
import "dict"

dayString = (d) => {
    dayStrings =
        [
            0: "Sunday",
            1: "Monday",
            2: "Tuesday",
            3: "Wednesday",
            4: "Thursday",
            5: "Friday",
            6: "Saturday",
        ]

    return dict.get(dict: dayStrings, key: d, default: "Unknown")
}

from(bucket: "example-bucket")
    |> range(start: -1mo)
    |> filter(fn: (r) => r._measurement == "example-measurement")
    |> filter(fn: (r) => r._field == "example-field")
    |> map(fn: (r) => ({r with day: dayString(d: date.weekDay(t: r._time))}))
    |> group(columns: ["day"])
    |> aggregateWindow(every: 1h, fn: mean)
6

With what you gave me earlier, here’s what I did:

journalier
journalier1666×345 100 KB

For reasons of confidentiality, information on the Y axis is concealed.

I think what I was trying to do was to put each of the curves for each day next to each other to form a week.