Implementing rolling operations for unevenly spaced time series

[jeffw-wherethebitsroam]

Hi,

We have time series data which is unevenly spaced. We would like to implement rolling average functions like the ones mentioned in:

https://pdfs.semanticscholar.org/882e/93570eae184ae737bf0344cb50a2925e353d.pdf

For example, a moving average with a 10 minute window and linear interpolation.

The question I have is about implementation. Looking at some of the code for similar functions (e.g. groupArrayMovingAvg) and they are all implemented as an aggregation function that return an array. Is that the right way to go?

If that is the case, what are size constraints for that array?

Thanks,
Jeff

[filimonov]

There were some related discussions here: #4542

I think arrays is just the simplest way to archve 'good enough' results.

Other possible ideas (as an alternative to arrays)

1. something similar to ORDER BY WITH FILL (see [WIP] WITH TIES and WITH FILL modifiers #5069 ).
2. some special type of 'interpolating' join. ('temporal join' ?)
3. more?

Other DBs:
https://cloud.ibm.com/docs/services/sql-query?topic=sql-query-temporal_align
https://www.vertica.com/blog/time-series-analysis-vertica/
https://blog.timescale.com/blog/sql-functions-for-time-series-analysis/
https://axibase.com/docs/atsd/sql/#interpolation