What time-weighted averages are and why you should care | Hacker News Reader

What time-weighted averages are and why you should care

blog.timescale.com

173 pointsibrar7426 comments

Oh yes, no ends of fun with this.

The article mentions "Industrial IoT", but applications of time-weighed averages in industry are much, much older than the term IoT (or "Industry 4.0"). It's the cornerstone of recording data from industrial controllers and sensors, and as a functionality, it's built into most of the relevant industrial tooling (e.g. historians[0]). Support for time-weighed average and other means of retrieving and analyzing such compressed data is included in core industrial protocols like OPC Classic and OPC UA.

As the article says, it enables data compression - but the article is mistaken (or simplifying too much) by saying data points are retained only "when the value changes". In typical implementations, data is retained when rate of change changes (i.e. first derivative). As long as the data points fit on the line, you can recover them all from line's end points with linear interpolation.

There's some more sophistication involved in this, too: for example, when configuring data archiving, you may set up "deadbands" - basically defining how thick the interpolated line is; data points that fall on this thick line will be considered as if they were perfectly centered, and not recorded. On top of that, most systems also track metadata like "sample quality" or "engineering units used" - there are rules specifying when to record a sample if its metadata changed.

All in all, it's an ingenious approach, but it comes with certain consequences:

One - what this article is about - when data is stored in such fashion, you need to use time-weighed aggregations (average or other functions) to compensate for some (usually most) of original data not being recorded.

Two, this technique assumes a single, continuous process being recorded. Imagine you're using a "smart scale" to record your daily weight. After a month, you give that scale to your partner. Right there you introduce a discontinuity; any data query that overlaps the period between your last stored (not measured, but stored under compression) measurement and your partner's first stored measurement, will return nonsense.

These two caveats sound obvious, but I've seen industrial project getting both of these wrong at some point.

[0] - https://en.wikipedia.org/wiki/Operational_historian

NB: I'm the author of the post :)

That's a nifty way of doing things that I didn't know as much about (definitely left out the historians bit as I didn't want to get too into the weeds) and have sometimes encountered the linear interpolation fit coming from them as well as the LOCF type fit, but didn't realize that's how they were sampling under the hood. That's pretty cool. I was mostly giving that example so people would understand why the LOCF option was available for the function as well, and one of the most common places where I've seen it is in (usually slowly changing) sensors in industrial settings that only record when they change.

On the single continuous process, yes, this is one of the most common mistakes people can make when they do this. We find it's best to model that as a change to the relational part of the data and potentially generate a new "id" for the sensor when that sort of discontinuity happens.

"a fully managed TimescaleDB service"

Weighted averaging as a commercial service, all by itself? The SQL solution seems complicated, but this is a task easily performed on time-ordered data.

I had to code such a time-weighted average last year, for a game client. The client gets updates for moving objects when they move a small distance or when some dwell time has elapsed. I needed a smoothed velocity from those irregularly spaced samples. The math had to be worked out, but it was just two lines of C++ in the implementation.[1]

    filtermult = 1.0f - 1.0f / pow(1.0f + 1.0f / fsRegionCrossingSmoothingTime, secs); // filter scale factor
    mFiltered = val * filtermult + mFiltered * (1.0f - filtermult); // low pass filter

[1] https://vcs.firestormviewer.org/phoenix-firestorm/files/tip/...

Timescale is a postgres extension that allows you to store time series data in a postgres database. Besides offering a very performant way to store specialized data it also offers additional functions to work with your time series data.

Besides the postgres extension there is also a database-as-a-service option where you can get started immediately and try the technique described in the blogpost.

>Weighted averaging as a commercial service, all by itself?

No.

The full quote is: "If you’d like to get started with the time_weight hyperfunction - and many more - right away, spin up a fully managed TimescaleDB service"

They're just indicating you can try this with their hosted TimescaleDB service. Not that this a standalone service.

A data analyst is more likely to know SQL rather than C++.

My point is that this is easy to do if you just have the data in sequence. The actual computation could be done in Matlab, or Julia, or R, or Python. SQL isn't a good language for doing computations on time-series data, but you can always write out a sorted data list.

time window functions exist in SQL now and are great.

That looks like a non-linear time-decay function, not a time-weighted average.

Why are you setting filtermult=1-stuff and then immediately doing 1-filtermult?

I don't mean to be demeaning, but the concept illustrated in the last figure is known as trapezoidal integration [1], has been well-know for millennia and is easy to implement.

I fail to see how the advertised "hyperfunctions" would make the process of analyzing such data any easier by adding web-services and SQL.

[1] https://en.wikipedia.org/wiki/Trapezoidal_rule

isn't trapezoidal rule the thing that gets "rediscovered" regularly ? https://academia.stackexchange.com/questions/9602/rediscover...

And that paper was then cited 112 times.

I really do not know if I should laugh or cry...

> the concept illustrated in the last figure is known as trapezoidal integration

And, as a consequence, the concept they call "time-weighted average" is just called "mean value of a function", at least in my country.

Also "hyperfunction" seems to be a terrible name. Unless we're talking about mathematics where the term has very specific meaning, how exactly is it different in programming from any ordinary function? (Possibly an aggregate function in SQL, of course.)

I think it's because TimescaleDB has, for performance reasons, "hypertables", and these functions operate on those.

So that means that only hyperfunctions can operate on these "hypertables"?

No you can use the ordinary SQL snippets listed in the blog post to do the same thing that a hyperfunction does (altough the hyperfunction might have a better performance). A hyperfunction is a function optimized to work on Timescale hypertables (which can be used to store & query time series data in a Postgres database).

NB: I'm the author of the post, and I work at Timescale.

Yep! Though I will say a number of the hyperfunctions are optimized to work on time-series data whether or not there's a hypertable involved, but the name is meant to echo the hypertable concept. In general, hyperfunctions are meant to improve the user experience for working with time-series data inside of SQL.

So there's no strict semantic connection between the "hyper-" in "hyperfunction" and "hyper-" in "hypertable"? Sounds quite confusing to me.

Technically, the hyper in hypertable was originally connected to the fact that they hypercubes as part of the partitioning logic: https://github.com/timescale/timescaledb/blob/master/src/hyp... Over time, that connection sort of got lost in favor of hypertables just being useful for storing and analyzing time-series data. So, these functions are conceptually similar in that they make analyzing time-series data easier, even if they don't involve hypercubes or the like. And I'd bet that most users will use hyperfunctions on their data in hypertables, but you can also use them on data in regular Postgres tables etc.

The article does note that

    "(Folks who have studied calculus may actually remember some of this from when they were learning about integrals and integral approximations!)"

NB: Author of post here. Yeah. In general, we didn't think this was horribly ground-breaking work in terms of it being a novel analysis type, the post was meant to explain it in an accessible way for folks who are new to the concept. So we broke down the trapezoidal rule into its more basic graphical intuition so that folks with less of a math background could follow easily.

Simpson's rule is a more accurate approach to numerical integration without being more computationally expensive: https://en.wikipedia.org/wiki/Simpson%27s_rule

Nevertheless, this was a very interesting article! When the topic of a time-weighted average came up, my first intuition was to give each sample y_n a weight of x_n - x_n-1, that is, the distance/time to the sample that came before. Wouldn't that also suffice?

NB: Article author here.

Interesting idea with Simpson's rule! Perhaps we'll add another method...

The other approach is very similar to the LOCF (last observation carried forward) approach, except that it uses the distance to the next sample instead of distance to the previous. I'm sure there are reasons to do the other way, but for most things that use LOCF they record when the value changes so it makes more sense to carry it forward. And it works pretty well as well. These are all also other integral approximation techniques, and which to use probably depends mostly on use case.

In finance referred to as TWAP

https://en.m.wikipedia.org/wiki/Time-weighted_average_price

Time-weighted average has always seemed like such an odd name to me. I assume that taking a simple average of irregularly spaced points is a very common mistake and so that is likely where this name comes from. But, as another commenter pointed out, it really is just the mean value of a function.

I can't deal with this article.. every paragraph contains a reference to the product these folks are trying to sell..

Reply on news.ycombinator.com