FILL keyword

Queries using a SAMPLE BY aggregate on data which has missing records may return in a discontinuous series of results. This keyword allows for specifying a fill behavior for query results which have missing aggregates due to missing rows.

To specify a default handling for null values within queries, see the coalesce() function documentation.

Syntax#

Flow chart showing the syntax of the FILL keyword

Options#

The FILL keyword expects a single fillOption strategy which will be applied to each aggregate column. The following restrictions apply:

  • Keywords denoting fill strategies may not be combined. Only one option from NONE, NULL, PREV, LINEAR and constants may be used.
  • If a constant is selected as a fill option, a constant value must be specified for each aggregate column
fillOptionDescription
NONENo fill applied. If there is no data, the time chunk will be skipped in the results. This means your table could potentially be missing intervals.
NULLFills with NULL
PREVFills using the previous value
LINEARFills by linear interpolation of the 2 surrounding points
xFills with a constant value - where x is the desired value, for example FILL(100.05)

Examples#

Consider an example table named prices which has no records during the entire third hour (2021-01-01T13):

tsprice
2021-01-01T10:00:00.000000Zp1
2021-01-01T11:00:00.000000Zp2
2021-01-01T12:00:00.000000Zp3
2021-01-01T14:00:00.000000Zp4
2021-01-01T15:00:00.000000Zp5
......

The following query returns the minimum, maximum and average price per hour:

SELECT ts, min(price) min, max(price) max, avg(price) avg
FROM PRICES
SAMPLE BY 1h;

The returned results look like this:

tsminmaxaverage
2021-01-01T10:00:00.000000Zmin1max1avg1
............

As there are missing values, an average aggregate cannot be calculated for the missing hour:

tsminmaxaverage
2021-01-01T10:00:00.000000Zmin1max1avg1
2021-01-01T11:00:00.000000Zmin2max2avg2
2021-01-01T12:00:00.000000Zmin3max3avg3
2021-01-01T14:00:00.000000Zmin5max5avg5

Based on this example, a FILL strategy can be employed which fills with the previous value using PREV:

SELECT ts, min(price) min, max(price) max, avg(price) avg
FROM PRICES
SAMPLE BY 1h
FILL(PREV);

This query returns the following results where the fourth row is created by using the FILL keyword:

tsminmaxaverage
2021-01-01T10:00:00.000000Zmin1max1avg1
2021-01-01T11:00:00.000000Zmin2max2avg2
2021-01-01T12:00:00.000000Zmin3max3avg3
2021-01-01T13:00:00.000000Zmin3max3avg3
2021-01-01T14:00:00.000000Zmin5max5avg5

This query demonstrates using a LINEAR value for interpolation:

SELECT ts, min(price) min, avg(price) avg
FROM PRICES`
SAMPLE BY 1h
FILL(LINEAR);

The results of this query look like the following:

tsminmaxaverage
2021-01-01T10:00:00.000000Zmin1max1avg1
2021-01-01T11:00:00.000000Zmin2max2avg2
2021-01-01T12:00:00.000000Zmin3max3avg3
2021-01-01T13:00:00.000000Z(min3+min5)/2(max3+max5)/2(avg3+avg5)/2
2021-01-01T14:00:00.000000Zmin5max5avg5

This query demonstrates using a constant value as a fillOption. If a constant value is used as a fillOption, a value must be specified for each aggregate column:

SELECT ts, min(price) min, avg(price) avg
FROM PRICES`
SAMPLE BY 1h
FILL(100.5, 10, 1);

The results of this query look like the following:

tsminmaxaverage
2021-01-01T10:00:00.000000Zmin1max1avg1
2021-01-01T11:00:00.000000Zmin2max2avg2
2021-01-01T12:00:00.000000Zmin3max3avg3
2021-01-01T13:00:00.000000Z100.5101
2021-01-01T14:00:00.000000Zmin5max5avg5

This query demonstrates using NULL as a fillOption:

SELECT ts, min(price) min, avg(price) avg
FROM PRICES`
SAMPLE BY 1h
FILL(NULL);

The results of this query look like the following:

tsminmaxaverage
2021-01-01T10:00:00.000000Zmin1max1avg1
2021-01-01T11:00:00.000000Zmin2max2avg2
2021-01-01T12:00:00.000000Zmin3max3avg3
2021-01-01T13:00:00.000000Znullnullnull
2021-01-01T14:00:00.000000Zmin5max5avg5