[WIP] Timeseries Aggregation

[JLockerman]

What's the functionality you would like to add

A generic interface to aggregate of (Time, Value) pairs and manipulate such timeseries.

How would the function be used

SELECT * FROM data;
          time          | value 
------------------------+-------
 2019-01-01 00:01:00-05 |     1
 2019-01-01 00:01:30-05 |     2
 2019-01-01 00:02:00-05 |     2
 2019-01-01 00:02:30-05 |     2
 2019-01-01 00:03:00-05 |     3
 2019-01-01 00:04:00-05 |      
 2019-01-01 00:04:30-05 |      
 2019-01-01 00:05:00-05 |      
 2019-01-01 00:05:30-05 |      
 2019-01-01 00:06:00-05 |     6
 2019-01-01 00:06:30-05 |     6
(11 rows)

-- a timeseries is sorted set of (time, value) pairs
SELECT timeseries(time, value, sample('1 minute') |> interpolate()) FROM data;
                                                                                                    timeseries
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (time, double precision) ("2019-01-01 00:01:00-05",1), ("2019-01-01 00:01:30-05",2), ("2019-01-01 00:02:00-05",2), ("2019-01-01 00:02:30-05",2), ("2019-01-01 00:03:00-05",3), ("2019-01-01 00:04:00-05", NULL), ("2019-01-01 00:04:30-05", NULL), ("2019-01-01 00:05:00-05", NULL), ("2019-01-01 00:05:30-05", NULL), ("2019-01-01 00:06:00-05",6), ("2019-01-01 00:06:30-05",6)
(1 row)

-- it can take a pipeline of transformations to be applied to the underlying data
-- enabling transformations such as bucketing, interpolation and others
SELECT timeseries(time, value, sample('1 minute') |> interpolate()) FROM data;
                                                                                                    timeseries
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (time, double precision) ("2019-01-01 00:01:00-05", 1), ("2019-01-01 00:02:00-05",2), ("2019-01-01 00:03:00-05",3), ("2019-01-01 00:04:00-05",3.6666666666666665), ("2019-01-01 00:05:00-05",4.666666666666666), ("2019-01-01 00:06:00-05",6)
(1 row)

Is your feature request related to a problem? A wishlist item Please describe.

TODO expand other wishlist items this relates to

Open Questions

1. What, if anything, should be the pipeline syntax?

2. We have multiple ways we can relate the pipeline to the aggregate, on is that the aggreagate takes in the pipline as an argument (shown above) this allows us the most flexibility in execution, and may allow us incremental execution in some instances. An alternative is that the pipeline takes in the aggregate as in

   timeseries(time, value) |> sample('1 minute') |> interpolate()

   this may be a more natural syntax, expecially with output aggregations as in

   timeseries(time, value) |> sample('1 minute') |> interpolate()

   in the current syntax that would look something like

   timeseries(time, value, sample('1 minute') |> interpolate())

3. Ordering: to what extent should we force the input to be ordered, versus sort internally to the aggregation?

4. Should we have separate types for regular and irregular timeseries?

[cevian]

Why not the standard syntax
interpolate(sample(timeseries(time,value), '1 minute'))? Is it just ergonomics?

Regarding ordering: I'd say the timeseries should be ordered always but the timeseries constructor could take an option of whether to order itself or not.

another question: should a regular vs irregular timeseries be the same or different types?

[JLockerman]

Why not the standard syntax
interpolate(sample(timeseries(time,value), '1 minute'))? Is it just ergonomics?

@cevian I expanded more in the discussion topic, but to summarize it's a combination of ergonomics and performance; the pipeline APIs also allow us to materialize fewer intermediate values.

Regarding ordering: I'd say the timeseries should be ordered always but the timeseries constructor could take an option of whether to order itself or not.

So internally the timeseries will definitely be ordered, and, unless we're doing incremental computation, it's fairly easy to detect whether the incoming data is ordered during aggregation. One big question though is if we should force the incoming data to be ordered by making timeseries() and ordered-set aggregate. Do you have any thoughts on that? (And do you think there's enough to discuss there that we should move the topic to Discussions?)

another question: should a regular vs irregular timeseries be the same or different types?

By regular vs irregular do you mean fixed time delta between points vs variable delta? I think that question boils down to: do we expect there to be operations that only make sense for regular or irregular time series? If so then they should probably be different types, if there aren't or they just reduce to no-ops than we can leave them the same. Added to Open Questions.

[JLockerman]

Scafodling and operator is merged as of PR #

Next up is downsampling, bucketing, and gapfilling. These should serve as an minimal viable example for the pipelining.

[JLockerman]

Gap handling operations:

1. remove gaps (example: remove NULLs)
2. fill with constant
3. fill with some calculated value