This is the multi-page printable view of this section. Click here to print.
Timeseries aggregate functions evaluate the values of a given set of variables over time and group those values into a window for analysis and aggregation.
One output row is produced per time slice—or per partition per time slice—if partition expressions are present.
Processes the data that belongs to each time slice. A time series aggregate (TSA) function, TS_FIRST_VALUE returns the value at the start of the time slice, where an interpolation scheme is applied if the timeslice is missing, in which case the value is determined by the values corresponding to the previous (and next) timeslices based on the interpolation scheme of const (linear).
TS_FIRST_VALUE returns one output row per time slice, or one output row per partition per time slice if partition expressions are specified
TS_FIRST_VALUE ( expression [ IGNORE NULLS ] [, { 'CONST' | 'LINEAR' } ] )
expressionINTEGER or FLOAT expression on which to aggregate and interpolate.IGNORE NULLSIGNORE NULLS behavior changes depending on a CONST or LINEAR interpolation scheme. See When Time Series Data Contains Nulls in Analyzing Data for details.'CONST' | 'LINEAR'CONST (default): New value is interpolated based on previous input records.
LINEAR: Values are interpolated in a linear slope based on the specified time slice.
You must use an ORDER BY clause with a TIMESTAMP column.
The same query can call multiple time series aggregate functions. They share the same gap-filling policy as defined by the TIMESERIES clause; however, each time series aggregate function can specify its own interpolation policy. For example:
=> 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);
See Gap Filling and Interpolation in Analyzing Data.
Processes the data that belongs to each time slice. A time series aggregate (TSA) function, TS_LAST_VALUE returns the value at the end of the time slice, where an interpolation scheme is applied if the timeslice is missing. In this case the value is determined by the values corresponding to the previous (and next) timeslices based on the interpolation scheme of const (linear).
TS_LAST_VALUE returns one output row per time slice, or one output row per partition per time slice if partition expressions are specified.
TS_LAST_VALUE ( expression [ IGNORE NULLS ] [, { 'CONST' | 'LINEAR' } ] )
expressionINTEGER or FLOAT expression on which to aggregate and interpolate.IGNORE NULLSIGNORE NULLS behavior changes depending on a CONST or LINEAR interpolation scheme. See When Time Series Data Contains Nulls in Analyzing Data for details.'CONST' | 'LINEAR'CONST (default): New value is interpolated based on previous input records.
LINEAR: Values are interpolated in a linear slope based on the specified time slice.
You must use the ORDER BY clause with a TIMESTAMP column.
The same query can call multiple time series aggregate functions. They share the same gap-filling policy as defined by the TIMESERIES clause; however, each time series aggregate function can specify its own interpolation policy. For example:
=> 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);
See Gap Filling and Interpolation in Analyzing Data.