Provides gap-filling and interpolation (GFI) computation, an important component of time series analytics computation. See Time series analytics for details and examples.
TIMESERIES slice-time AS 'length-and-time-unit-expr' OVER ( [ PARTITION BY (`*`column-expr`*`[,...] ] ORDER BY time-expr ) [ ORDER BY table-column[,...] ]
- A time column produced by the
TIMESERIESclause, which stores the time slice start times generated from gap filling.
Note: This parameter is an alias, so you can use any name that an alias would take.
INTERVAL DAY TO SECONDliteral that specifies the length of time unit of time slice computation. For example:
`TIMESERIES slice_time AS '3 seconds' ...
- Specifies partitioning and ordering for the function.
OVER()also specifies that the time series function operates on a query result set—that is, the rows that are returned after the
GROUP BY, and
HAVINGclauses are evaluated.
PARTITION BY (column-expr`[,...] )`
- Partitions the data by the specified column expressions. Gap filling and interpolation is performed on each partition separately.
- Sorts the data by the
time-expr, which computes the time information of the time series data.
TIMESERIESclause requires an
ORDER BYoperation on the timestamp column.
window-partition-clause is not specified in TIMESERIES OVER(), for each defined time slice, exactly one output record is produced; otherwise, one output record is produced per partition per time slice. Interpolation is computed there.
Given a query block that contains a TIMESERIES clause, the following are the semantic phases of execution (after evaluating the FROM and the optional WHERE clauses):
Perform the same computation as the TIME_SLICE() function on each input record based on the result of
Perform gap filling to generate time slices missing from the input.
Name the result of this computation as
slice_time, which represents the generated "time series" column (alias) after gap filling.
Partition the data by
slice-time. For each partition, do step 4.
Sort the data by
time-expr. Interpolation is computed here.
There is semantic overlap between the TIMESERIES clause and the TIME_SLICE function with the following key differences:
TIMESERIESonly supports the interval qualifier
DAY TO SECOND; it does not allow
YEAR TO MONTH.
TIME_SLICE, the time slice length and time unit expressed in *
length-and-time-unit-expr*must be constants so gaps in the time slices are well-defined.
TIMESERIESperforms gap filling; the
TIME_SLICEfunction does not.
TIME_SLICEcan return the start or end time of a time slice, depending on the value of its fourth input parameter (
TIMESERIES, on the other hand, always returns the start time of each time slice. To output the end time of each time slice, write a
SELECTstatement like the following:
=> SELECT slice_time + <slice_length>;
TIMESERIESclause occurs in a SQL query block, only the following clauses can be used in the same query block:
HAVINGclauses are not allowed. If a
GROUP BYoperation is needed before or after gap-filling and interpolation (GFI), use a subquery and place the
GROUP BYIn the outer query. For example:
=> SELECT symbol, AVG(first_bid) as avg_bid FROM ( SELECT symbol, slice_time, TS_FIRST_VALUE(bid1) AS first_bid FROM Tickstore WHERE symbol IN ('MSFT', 'IBM') TIMESERIES slice_time AS '5 seconds' OVER (PARTITION BY symbol ORDER BY ts) ) AS resultOfGFI GROUP BY symbol;
TIMESERIESclause is present in the SQL query block, the
SELECTlist can include only the following:
For example, the following two queries return a syntax error because
bid1is not a
=> SELECT bid, symbol, TS_FIRST_VALUE(bid) FROM Tickstore TIMESERIES slice_time AS '5 seconds' OVER (PARTITION BY symbol ORDER BY ts); ERROR: column "Tickstore.bid" must appear in the PARTITION BY list of Timeseries clause or be used in a Timeseries Output function => SELECT bid, symbol, AVG(bid) FROM Tickstore GROUP BY symbol; ERROR: column "Tickstore.bid" must appear in the GROUP BY clause or be used in an aggregate function
For examples, see Gap filling and interpolation (GFI).