SQL / InfluxQL weighted average

InfluxDB IOx
,
1

Hello,
in case my data points are irregular with their timestamp, would it be possible to calculate a weighted average instead of a simple average, in order to give the values the right weight, depending on how much time they “lasted”?
For example, I collect 10.1 at 00:00:00, and 15.3 at 00:55:00; if I select the average between 00:00:00 and 00:55:00 I would like the calculation to weight 10.1 for 55/60 and 15.3 for 5/60.
Thank you!

2

Hello @Torakiki73,
Unfortunately SQL and InfluxQL doesn’t have this capability.
I recommend using the python client library that has support for pandas and polars and then doing this type of transformation.
For example:

import pandas as pd
from influxdb_client_3 import InfluxDBClient3

client = InfluxDBClient3(token="DATABASE_TOKEN",
                         host="cluster-id.influxdb.io",
                         database="DATABASE_NAME")

query = "SELECT * FROM measurement WHERE time >= now() - INTERVAL '90 days'"
df = client.query(query=query, mode="pandas")

# Calculating weighted average 
def weighted_avg(group):
    group = group.sort_values('time')
    time_diffs = group['time'].diff().dt.total_seconds().fillna(0)
    weighted_values = group['count'] * time_diffs
    return weighted_values.sum() / time_diffs.sum()

weighted_averages = df.groupby('tagvalue').apply(weighted_avg)

weighted_averages

Let me know if that helps.

3

Hello Anais,
thanks a lot for taking the time to answer to my question!
As with the time zone question, retrieving plain data from Influx and then applying transformations on the client side is something I’ll have to test; the ideal situation, of course, would be to have built-in functions that would take care of all that, but sometimes we can’t have it all :slight_smile:
Thanks again Anais, really appreciate your support!

Regards,
Alberto