GFI examples
This topic illustrates some of the queries you can write using the constant and linear interpolation schemes.
Constant interpolation
The first query uses TS_FIRST_VALUE() and the TIMESERIES clause to place the input records in 3-second time slices and return the first bid value for each symbol/time slice combination (the value at the start of the time slice).
Note
The TIMESERIES clause requires an ORDER BY operation on the TIMESTAMP column.=> SELECT slice_time, symbol, TS_FIRST_VALUE(bid) AS first_bid FROM TickStore
TIMESERIES slice_time AS '3 seconds' OVER (PARTITION BY symbol ORDER BY ts);
Because the bid price of stock XYZ is 10.0 at 3:00:03, the first_bid value of the second time slice, which starts at 3:00:03 is till 10.0 (instead of 10.5) because the input value of 10.5 does not occur until 3:00:05. In this case, the interpolated value is inferred from the last value seen on stock XYZ for time 3:00:03:
slice_time | symbol | first_bid
---------------------+--------+-----------
2009-01-01 03:00:00 | XYZ | 10
2009-01-01 03:00:03 | XYZ | 10
(2 rows)
The next example places the input records in 2-second time slices to return the first bid value for each symbol/time slice combination:
=> SELECT slice_time, symbol, TS_FIRST_VALUE(bid) AS first_bid FROM TickStore
TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);
The result now contains three records in 2-second increments, all of which occur between the first input row at 03:00:00 and the second input row at 3:00:05. Note that the second and third output record correspond to a time slice where there is no input record:
slice_time | symbol | first_bid
---------------------+--------+-----------
2009-01-01 03:00:00 | XYZ | 10
2009-01-01 03:00:02 | XYZ | 10
2009-01-01 03:00:04 | XYZ | 10
(3 rows)
Using the same table schema, the next query uses TS_LAST_VALUE(), with the TIMESERIES clause to return the last values of each time slice (the values at the end of the time slices).
Note
Time series aggregate functions process the data that belongs to each time slice. One output row is produced per time slice or per partition per time slice if a partition expression is present.=> SELECT slice_time, symbol, TS_LAST_VALUE(bid) AS last_bid FROM TickStore
TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);
Notice that the last value output row is 10.5 because the value 10.5 at time 3:00:05 was the last point inside the 2-second time slice that started at 3:00:04:
slice_time | symbol | last_bid
---------------------+--------+----------
2009-01-01 03:00:00 | XYZ | 10
2009-01-01 03:00:02 | XYZ | 10
2009-01-01 03:00:04 | XYZ | 10.5
(3 rows)
Remember that because constant interpolation is the default, the same results are returned if you write the query using the CONST parameter as follows:
=> SELECT slice_time, symbol, TS_LAST_VALUE(bid, 'CONST') AS last_bid FROM TickStore
TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);
Linear interpolation
Based on the same input records described in the constant interpolation examples, which specify 2-second time slices, the result of TS_LAST_VALUE with linear interpolation is as follows:
=> SELECT slice_time, symbol, TS_LAST_VALUE(bid, 'linear') AS last_bid FROM TickStore
TIMESERIES slice_time AS '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);
In the results, no last_bid value is returned for the last row because the query specified TS_LAST_VALUE, and there is no data point after the 3:00:04 time slice to interpolate.
slice_time | symbol | last_bid
---------------------+--------+----------
2009-01-01 03:00:00 | XYZ | 10.2
2009-01-01 03:00:02 | XYZ | 10.4
2009-01-01 03:00:04 | XYZ |
(3 rows)
Using multiple time series aggregate functions
Multiple time series aggregate functions can exists in the same query. They share the same gap-filling policy as defined in the TIMESERIES clause; however, each time series aggregate function can specify its own interpolation policy. In the following example, there are two constant and one linear interpolation schemes, but all three functions use a three-second time slice:
=> SELECT slice_time, symbol,
TS_FIRST_VALUE(bid, 'const') fv_c,
TS_FIRST_VALUE(bid, 'linear') fv_l,
TS_LAST_VALUE(bid, 'const') lv_c
FROM TickStore
TIMESERIES slice_time AS '3 seconds' OVER(PARTITION BY symbol ORDER BY ts);
In the following output, the original output is compared to output returned by multiple time series aggregate functions.
|
==> |
|
Using the analytic LAST_VALUE function
Here's an example using LAST_VALUE(), so you can see the difference between it and the GFI syntax.
=> SELECT *, LAST_VALUE(bid) OVER(PARTITION by symbol ORDER BY ts)
AS "last bid" FROM TickStore;
There is no gap filling and interpolation to the output values.
ts | symbol | bid | last bid
---------------------+--------+------+----------
2009-01-01 03:00:00 | XYZ | 10 | 10
2009-01-01 03:00:05 | XYZ | 10.5 | 10.5
(2 rows)
Using slice_time
In a TIMESERIES query, you cannot use the column slice_time in the WHERE clause because the WHERE clause is evaluated before the TIMESERIES clause, and the slice_time column is not generated until the TIMESERIES clause is evaluated. For example, Vertica does not support the following query:
=> SELECT symbol, slice_time, TS_FIRST_VALUE(bid IGNORE NULLS) AS fv
FROM TickStore
WHERE slice_time = '2009-01-01 03:00:00'
TIMESERIES slice_time as '2 seconds' OVER (PARTITION BY symbol ORDER BY ts);
ERROR: Time Series timestamp alias/Time Series Aggregate Functions not allowed in WHERE clause
Instead, you could write a subquery and put the predicate on slice_time in the outer query:
=> SELECT * FROM (
SELECT symbol, slice_time,
TS_FIRST_VALUE(bid IGNORE NULLS) AS fv
FROM TickStore
TIMESERIES slice_time AS '2 seconds'
OVER (PARTITION BY symbol ORDER BY ts) ) sq
WHERE slice_time = '2009-01-01 03:00:00';
symbol | slice_time | fv
--------+---------------------+----
XYZ | 2009-01-01 03:00:00 | 10
(1 row)
Creating a dense time series
The TIMESERIES clause provides a convenient way to create a dense time series for use in an outer join with fact data. The results represent every time point, rather than just the time points for which data exists.
The examples that follow use the same TickStore schema described in Gap filling and interpolation (GFI), along with the addition of a new inner table for the purpose of creating a join:
=> CREATE TABLE inner_table (
ts TIMESTAMP,
bid FLOAT
);
=> CREATE PROJECTION inner_p (ts, bid) as SELECT * FROM inner_table
ORDER BY ts, bid UNSEGMENTED ALL NODES;
=> INSERT INTO inner_table VALUES ('2009-01-01 03:00:02', 1);
=> INSERT INTO inner_table VALUES ('2009-01-01 03:00:04', 2);
=> COMMIT;
You can create a simple union between the start and end range of the timeframe of interest in order to return every time point. This example uses a 1-second time slice:
=> SELECT ts FROM (
SELECT '2009-01-01 03:00:00'::TIMESTAMP AS time FROM TickStore
UNION
SELECT '2009-01-01 03:00:05'::TIMESTAMP FROM TickStore) t
TIMESERIES ts AS '1 seconds' OVER(ORDER BY time);
ts
---------------------
2009-01-01 03:00:00
2009-01-01 03:00:01
2009-01-01 03:00:02
2009-01-01 03:00:03
2009-01-01 03:00:04
2009-01-01 03:00:05
(6 rows)
The next query creates a union between the start and end range of the timeframe using 500-millisecond time slices:
=> SELECT ts FROM (
SELECT '2009-01-01 03:00:00'::TIMESTAMP AS time
FROM TickStore
UNION
SELECT '2009-01-01 03:00:05'::TIMESTAMP FROM TickStore) t
TIMESERIES ts AS '500 milliseconds' OVER(ORDER BY time);
ts
-----------------------
2009-01-01 03:00:00
2009-01-01 03:00:00.5
2009-01-01 03:00:01
2009-01-01 03:00:01.5
2009-01-01 03:00:02
2009-01-01 03:00:02.5
2009-01-01 03:00:03
2009-01-01 03:00:03.5
2009-01-01 03:00:04
2009-01-01 03:00:04.5
2009-01-01 03:00:05
(11 rows)
The following query creates a union between the start- and end-range of the timeframe of interest using 1-second time slices:
=> SELECT * FROM (
SELECT ts FROM (
SELECT '2009-01-01 03:00:00'::timestamp AS time FROM TickStore
UNION
SELECT '2009-01-01 03:00:05'::timestamp FROM TickStore) t
TIMESERIES ts AS '1 seconds' OVER(ORDER BY time) ) AS outer_table
LEFT OUTER JOIN inner_table ON outer_table.ts = inner_table.ts;
The union returns a complete set of records from the left-joined table with the matched records in the right-joined table. Where the query found no match, it extends the right side column with null values:
ts | ts | bid
---------------------+---------------------+-----
2009-01-01 03:00:00 | |
2009-01-01 03:00:01 | |
2009-01-01 03:00:02 | 2009-01-01 03:00:02 | 1
2009-01-01 03:00:03 | |
2009-01-01 03:00:04 | 2009-01-01 03:00:04 | 2
2009-01-01 03:00:05 | |
(6 rows)