FILL keyword

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

Syntax#

Flow chart showing the syntax of the FILL keyword

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.

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:

tsprice
2021-01-01T12:00:00.000000Zp1
2021-01-01T13:00:00.000000Zp2
2021-01-01T14:00:00.000000Zp3
......
tsnpn

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-01T12:00:00.000000Zmin1max1avg1
............
tsnminnmaxnavgn

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:

tsminmaxaverage
2021-01-01T12:00:00.000000Zmin1max1avg1
2021-01-01T13:00:00.000000Zmin2max2avg2
2021-01-01T14:00:00.000000Znullnullnull
2021-01-01T15:00:00.000000Zmin4max4avg4
............
tsnminnmaxnavgn

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

Using three fillOptions for filling missing data
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:

tsminmaxaverage
2021-01-01T12:00:00.000000Zmin1max1avg1
2021-01-01T13:00:00.000000Zmin2max2avg2
2021-01-01T14:00:00.000000Znull0avg2
2021-01-01T15:00:00.000000Zmin4max4avg4
............
tsnminnmaxnavgn

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:

tsminaverage
2021-01-01T12:00:00.000000Zmin1avg1
2021-01-01T13:00:00.000000Zmin2avg2
2021-01-01T14:00:00.000000Z25.5(avg2+avg4)/2
2021-01-01T15:00:00.000000Zmin4avg4
.........
tsnminnavgn