Time weighed averages

[m2b]

Is your feature request related to a problem?

Data from IoT sources is not always at a equal interval. The standard SQL avg assumes the avg=sum(values)/count(values). If the time between samples differs this is not desired. The are many use cases in industry. This can be handled with hyperfunctions in SQL databases. See https://www.timescale.com/blog/what-time-weighted-averages-are-and-why-you-should-care/

Describe the solution you'd like.

Include a Twavg fucntion that can be applied in SQl

SELECT twavg(value) where timestamp between A and B.

Describe alternatives you've considered.

I would hate to have to write custom functions for this.

Additional context.

Look in Google, there are many industry standard applications requiring this.

[Wafje]

For use of questDB in an OT environment, time weighted average is a musthave.

[nwoolmer]

We do not have a specific function for it yet. But we do support LAG in window functions. The article in the OP shows a way to calculate this using window functions. Have you tried that route?

[bluestreak01]

implemented in #6651 and #6650

[Wafje]

I believe the implementations in #6651 and #6650 do not solve pure Time Weighted Average (TWA). Pure PWA doesnot have an exponential value and the weight get determined by the time between two values, thus not fixed upfront.

TWA gets calculated as the average between two datapoints times the duration between these two datapoints, this is repeated for each value in the aggregation window; and divided by the total weight.

See https://www.tigerdata.com/blog/what-time-weighted-averages-are-and-why-you-should-care for a full explanation.

Could this issue be re-opened?

[m2b]

I second that and also, proper TWA must include edges (interpoloted points as the start and end of the period base on adjacent values).

[Wafje]

@m2b,

As suggested, I used the LAG functionality to get close to the required result. Still have to try some edge cases though.
Not as clean as a twa() function though.
Example:

WITH setup AS (
	SELECT lag(value) OVER (PARTITION BY topic ORDER BY timestamp) as prev_value, 
		cast(timestamp AS long) / 1000 as ts_e, 
		cast(lag(timestamp) OVER (PARTITION BY topic ORDER BY timestamp) AS LONG) / 1000 as prev_ts_e, 
		* 
	FROM  tagvalues
  WHERE topic = $topic AND timestamp < $end AND timestamp > $begin),
nextstep AS (
	SELECT CASE WHEN prev_value is NULL THEN NULL 
		ELSE (prev_value + value) / 2 * (ts_e - prev_ts_e) END as weighted_sum,
		(ts_e - prev_ts_e) as duration,
		* 
	FROM setup)
SELECT
	timestamp,
  topic,
  avg(value), -- the regular average
	sum(weighted_sum) / sum(duration) as time_weighted_average -- our derived average
FROM nextstep
SAMPLE BY 1m FILL(PREV)
ALIGN TO FIRST OBSERVATION