Proposal: enhance processing of null values in time-weighted aggregates

[milgner]

Following up on #323, #548 and several other issues around processing time-weighted averages:

Similar to the already-present treat_null_as_missing argument to the locf function, there are different ways to interpret null values in time-weighted aggregates:

They could either be interpreted as "0" - which can be easily represented by using COALESCE(value, 0.0) in the query - or alternatively mark time ranges for which data should be excluded from calculation.

I propose enhancing the time_weight constructor and associated TimeWeightSummary state with an additional parameter treat_null_as_missing to support null values. At the moment, a null value will yield a null TimeWeightSummary instance. But an additional treat_null_as_missing parameter might yield an TimeWeightSummary instance which can still be filled with additional points.
Finally, the points contained within should have their value changed from f64 to Option<f64> so they can process null values according to the treat_null_as_missing parameter.

In that case, depending on the interpolation, null values should be treated differently:

• for linear interpolation, data can only be interpolated up to the point preceding the null datum
• for locf interpolation, the value can be carried up to the null datum (this is probably the most useful combination)
• for integrals the calculation will remain the same
• when calculating averages, the integral accumulator will then be not divided by the length of the full duration but only by the duration for which there actually was data. There should also be a function along the lines of duration(TimeWeightSummary) which returns that duration (might also be related to Add duration_in(state, start, end) accessor for Timeline Agg #687?).

As I'm still learning a lot about TimescaleDB, I'm not sure whether this might have unwanted impact on other functionality, like rollup or others. So I'm looking for feedback on the concept - but if we find that it goes together with everything else, I'm willing to take a shot at implementing it as I have already put together very similar functionality as a prototype based on pgx - I'd just prefer to not have to maintain & deploy it separately and in addition to the regular TimescaleDB toolkit 😉

[milgner]

After some quick initial feedback to my comment in #323 I'm a bit surprised that there hasn't been any comment on this proposal yet. Maybe it's the wrong place after all? I can also implement these null-aware aggregates in a separate extension for myself, but I had hoped to integrate this with timescaledb-toolkit in order to benefit from existing infrastructure and give back to this great project, too.
Let me know in case something is unclear or if I can do anything else to further this feature.