TS_FIRST_VALUE

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). There is one value per time slice per partition.

Behavior Type

Immutable

Syntax

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

Parameters

expression

Argument expression on which to aggregate and interpolate.

expression is data type INTEGER or FLOAT.

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'

(Default CONST) Optionally specifies the interpolation value as either constant or linear.

CONST—New value are interpolated based on previous input records.
LINEAR—Values are interpolated in a linear slope based on the specified time slice.

Notes

The function returns one output row per time slice or one output row per partition per time slice if partition expressions are specified.

Multiple time series aggregate functions can exists in the same query. 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);

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

Example

For detailed examples, see Gap Filling and Interpolation in Analyzing Data.