FILL keyword

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

Syntax

Flow chart showing the syntax of the FILL keyword

Options

There are as many fillOption as there are aggreate columns in your query.

fillOptionDescription
NONEWill not fill. In case 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 the constant defined (replace the x by the value you want. For example fill 100.05

Examples

Consider the following prices table

timestampprice
ts1p1
ts2p2
ts3p3
......
tsnpn

We could run the following to get the minimum, maximum and average price per hour using the following query:

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

It would generally return result like this:

timestampminmaxaverage
ts1min1max1avg1
............
tsnminnmaxnavgn

However, in case there was no PRICES data for a given hour, your table would have time chunks missing. In the below example, there is no data to generate aggregates for ts3

timestampminmaxaverage
ts1min1max1avg1
ts2min2max2avg2
ts3nullnullnull
ts4min4max4avg4
............
tsnminnmaxnavgn

Here you can see that the third time chunk is missing. This is because there was no price update in the third hour. Let's see what different fill values would return:

SELECT timestamp, min(price) min, max(price) max, avg(price) avg
FROM PRICES
SAMPLE BY 1h
FILL(null, 0, prev);

would return the following

timestampminmaxaverage
ts1min1max1avg1
ts2min2max2avg2
ts3NULL0avg2
ts4min4max4avg4
............
tsnminnmaxnavgn

And the following:

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

Would return:

timestampminaverage
ts1min1avg1
ts2min2avg2
ts325.5(avg2+avg4)/2
ts4min4avg4
.........
tsnminnavgn