Specifies fill behavior for missing data as part of a SAMPLE BY aggregation queries.

Syntax

Options

The FILL keyword expects a fillOption for each aggregate column. The fill options are applied to aggregates based on order of appearance in the query.

Examples

Consider an example table named prices:

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:

In the below example, there is no price data during the entire third hour. As there are missing values, an average aggregate cannot be calculated for this hour at 2021-01-01T14:00:00.000000Z:

Based on this example, the following FILL strategies can be employed, demonstrating filling with NULL, a constant value, and the previous value:

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

This query returns the following results:

This query demonstrates the remaining fillOptions using a constant value and linear interpolation:

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

The results of this query look like the following: