Skip to content

Latest commit

 

History

History
211 lines (167 loc) · 6.66 KB

timeseries.md

File metadata and controls

211 lines (167 loc) · 6.66 KB

Timevector experimental

Description
Timevector Pipelines
Example
API

Description

A timevector is an intermediate representation of a particular value over time used by the extension. It is a space efficient representation used to store the result of analytic functions such as asap_smooth or lttb. Data can also be directly aggregated into a timevector and passed to functions which support this representation. The unnest API can be used to get the data back from a timevector.

Timevector Pipelines

In an attempt to streamline the timevector interface and make them as easy to use as possible, we've provided a custom operator -> for applying common operations to timevector and chaining such operations together. This is much more fully documented in the timevector pipeline elements page.

Usage Example

For this example, let's start with a table containing some random test data.

SET TIME ZONE 'UTC';
CREATE TABLE test(time TIMESTAMPTZ, value DOUBLE PRECISION);
INSERT INTO test
    SELECT time, value
    FROM toolkit_experimental.generate_periodic_normal_series('2020-01-01 UTC'::timestamptz, rng_seed => 11111);
INSERT 0 4032

While still expermental, we'll need to set this before creating our view:

    SET timescaledb_toolkit_acknowledge_auto_drop TO 'true';

Now lets capture this data into a time series which we'll store in a view.

CREATE VIEW series AS SELECT toolkit_experimental.timevector(time, value) FROM test;

We can now use this timevector to efficiently move the data around to other functions.

SELECT time, value::numeric(10,2) FROM
toolkit_experimental.unnest((SELECT toolkit_experimental.lttb(timevector, 20) FROM series));
          time          |       value
------------------------+--------------------
2020-01-01 00:00:00+00 | 1038.44
2020-01-02 04:20:00+00 | 1325.44
2020-01-03 14:00:00+00 |  708.82
2020-01-04 18:30:00+00 | 1328.28
2020-01-05 16:40:00+00 |  802.20
2020-01-07 06:00:00+00 | 1298.02
2020-01-09 11:20:00+00 |  741.08
2020-01-10 18:40:00+00 | 1357.05
2020-01-13 08:30:00+00 |  780.32
2020-01-14 03:40:00+00 | 1408.34
2020-01-15 01:50:00+00 |  895.15
2020-01-16 20:30:00+00 | 1335.22
2020-01-18 07:20:00+00 |  823.08
2020-01-19 18:10:00+00 | 1245.79
2020-01-21 10:00:00+00 |  666.48
2020-01-22 23:10:00+00 | 1182.87
2020-01-24 09:00:00+00 |  736.47
2020-01-26 05:20:00+00 | 1197.26
2020-01-28 08:10:00+00 |  659.63
2020-01-28 23:50:00+00 |  956.29

Command List (A-Z)

Aggregate Functions

Accessor Functions

timevector (point form) 

timevector(
    time TIMESTAMPTZ,
    value DOUBLE PRECISION
) RETURNS Timevector

This will construct and return timevector object containing the passed in time, value pairs.

Required Arguments

Name Type Description
time TIMESTAMPTZ Time column to aggregate.
value DOUBLE PRECISION Value column to aggregate.

Returns

Column Type Description
timevector Timevector A timevector object which can be efficiently used by any of our timevector operations.

Sample Usages

For this example, assume we have a table 'samples' with two columns, 'time' and 'weight'. The following will return that table as a timevector.

SELECT toolkit_experimental.timevector(time, weight) FROM samples;

rollup (summary form) 

rollup(
    series timevector
) RETURNS timevector

This will combine multiple already constructed timevectors. This is very useful for re-aggregating series already constructed using the point form.

Required Arguments

Name Type Description
series timevector Previously constructed timevector objects.

Returns

Column Type Description
timevector timevector A timevector combining all the underlying series.

Sample Usages

This example assumes a table 'samples' with columns 'time', 'data', and 'batch'. We can create a view containing timevector for each batch like so:

CREATE VIEW series AS
    SELECT
        batch,
        toolkit_experimental.timevector(time, data) as batch_series
    FROM samples
    GROUP BY batch;

If we want to operate over the combination of all batches, we can get the timevector for this as follows:

SELECT rollup(batch_series)
FROM series;

unnest 

unnest(
    series timevector
) RETURNS TABLE("time" timestamp with time zone, value double precision)

The unnest function is used to get the (time, value) pairs back out of a timevector object.

Required Arguments

Name Type Description
series timevector The series to return the data from.

Returns

Column Type Description
unnest TABLE The (time,value) records contained in the timevector.

Sample Usage 

SELECT toolkit_experimental.unnest(
    (SELECT toolkit_experimental.timevector(a.time, a.value)
    FROM
        (SELECT time, value
        FROM toolkit_experimental.generate_periodic_normal_series('2020-01-01 UTC'::timestamptz, 45654))
        a)
    )
LIMIT 10;
                 unnest
-----------------------------------------------
 ("2020-01-01 00:00:00+00",1009.8399687963981)
 ("2020-01-01 00:10:00+00",873.6326953620166)
 ("2020-01-01 00:20:00+00",1045.8138997857413)
 ("2020-01-01 00:30:00+00",1075.472021940188)
 ("2020-01-01 00:40:00+00",956.0229773008177)
 ("2020-01-01 00:50:00+00",878.215079403259)
 ("2020-01-01 01:00:00+00",1067.8120522056508)
 ("2020-01-01 01:10:00+00",1102.3464544566375)
 ("2020-01-01 01:20:00+00",952.9509636893868)
 ("2020-01-01 01:30:00+00",1031.9006507123047)