TS_FIRST_VALUE

Processes the data that belongs to each time slice.

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

Behavior type

Immutable

Syntax

TS_FIRST_VALUE ( expression [ IGNORE NULLS ] [, { 'CONST' | 'LINEAR' } ] )

Parameters

expression
An INTEGER or FLOAT expression on which to aggregate and interpolate.
IGNORE NULLS
The IGNORE 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'
Specifies the interpolation value as constant or 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.

Requirements

You must use an ORDER BY clause with a TIMESTAMP column.

Multiple time series aggregate functions

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);

Examples

See Gap Filling and Interpolation in Analyzing Data.

See also