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

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

Examples

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

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

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

The returned results look like this:

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

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) average
FROM prices
SAMPLE BY 1h
FILL(PREV);

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

This query demonstrates using a LINEAR value for interpolation:

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

The results of this query look like the following:

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) average
FROM prices
SAMPLE BY 1h
FILL(100.5, 10, 1);

The results of this query look like the following:

This query demonstrates using NULL as a fillOption:

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

The results of this query look like the following: