Time-Weighted Averages

[inselbuch]

What's the functionality you would like to add

To produce evenly spaced values of arbitrary intervals. Time-weighted averages weight each value within the period by the length of time the value held true. So the initial value in a period must weight the immediately preceding the period as well.

How would the function be used

select twa(value, interval '10 minute')
from table
where time between '0:00' and now();

select twa(value, interval '15 minute')
from table
where time between '0:00' and now();

-Arithmetic averages (sum of samples/number of sample) can give wildly misleading results (e.g., a single, ephemeral, spike can alter the meaning dramatically)

• When using values from multiple measurements it is handy to have them aligned in time
• The arithmetic average of Time-weighted averages is valid because the samples are equidistant
• Time-weighted averages can be used statistically (whereas arithmetic averages cannot)
  -Arithmetic averages of generated, evenly spaced values (buckets) over large periods, is memory-intensive
  -True time-weighted algorithm is accurate to the precision of the time stamp, not the granularity of the selected bucket size.

What scale is this useful at?

All scales when you are seeking the truth.

[JLockerman]

Thank you for opening this issue @inselbuch!

Let me see if I understand the functionality: if you have a dataset like

 time | value
------+-------
00:00 |   1
00:05 |   2
00:07 |   3
00:10 |   1
00:20 |   4

Assuming we store all only those times at which value the time-weighted average over 10 minutes would be

(0.5 * 1) + (0.2 * 2) + (0.3 * 3) = 1.8

for the interval [00:00, 00:10) and

(1 * 1) = 1.8

for the interval [00:10, 00:20). Is that correct?

[inselbuch]

yes, but if the first value is at 00:01 you must include the previous value weighted for the first period (minutes i think you implied)

…

Sent from my iPad

On Jan 22, 2021, at 12:58 PM, JLockerman ***@***.***> wrote:  Thank you for opening this issue @inselbuch! Let me see if I understand the functionality: if you have a dataset like time | value ------+------- 00:00 | 1 00:05 | 2 00:07 | 3 00:10 | 1 00:20 | 4 Assuming we store all only those times at which value the time-weighted average over 10 minutes would be (0.5 * 1) + (0.2 * 2) + (0.3 * 3) = 1.8 for the interval [00:00, 00:10) and (1 * 1) = 1.8 for the interval [00:10, 00:20). Is that correct? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[inselbuch]

Sample code:

https://github.com/inselbuch/twa/blob/master/twa.py

[JLockerman]

Am experimental version of this is available in 0.1.0, and should also be available on Forge soon.

[JLockerman]

This was released in 1.0.0