INTERPOLATE
Description
INTERPOLATE can be used to interpolate missing values (i.e. NULL values). It is possible to specify a column based ordering and partitioning. The output type is INT if input column is INT and <interpolation type>
is CONSTANT
. FLOAT otherwise.
Syntax
INTERPOLATE ( column, interpolation type [, ORDER BY ( sort_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] )
column: The input column where values should be interpolated. Supported input types: INT, FLOAT
interpolation type: Specify how the missing values should be interpolated:
CONSTANT: Use same values as previous data point
LINEAR: Use slope between enclosing data points
sort_column: Optional sorting column to specify an order.
sorting: Each of these columns can have an optional tag specifying the ordering of the column. Default is ascending:
ASC: Ascending order
DESC: Descending order
partition_column: Optional partition column to specify groups in which
INTERPOLATE
should operate.
One or more columns can be given to specify an ordering. This tells the INTERPOLATE
function what the preceding/following element actually is. Optionally every column can be tagged as ascending or descending. The partition columns specify groups. The INTERPOLATE
function operates independently within every group. This means when an ordering is given it is applied within every group.
Examples
CONSTANT
[1] Simple example of CONSTANT interpolation of INT values. | ||||||||||||||
| ||||||||||||||
|
[2] Leading NULL values are filled with the first value found. | ||||||||||||||||
| ||||||||||||||||
|
[3] If no data point is found, NULL values cannot be interpolated. | ||||||||||||
| ||||||||||||
|
LINEAR
[4] Simple example of LINEAR interpolation. | ||||||||||||||
| ||||||||||||||
|
[5] For leading and lagging NULL values slope 0 is used (same as CONSTANT interpolation). | ||||||||||||||||
| ||||||||||||||||
|
[6] If no data point is found, NULL values cannot be interpolated. | ||||||||||||
| ||||||||||||
|
Ordering, Partitioning
[7] CONSTANT interpolation with order and partition columns. | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[8] LINEAR interpolation with order and partition columns. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[9] It is possible to combine an arbitrary amount of order columns with an arbitrary amount of partition columns. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|