In this I demonstrate how missing values can be interpolated in SQL. The code is written for BigQuery and I provide a user defined function, however, similar solutions may exist for other databases. Although, SQL may not be the first choice to do such tasks, it is possible. Before one can replace missing values, you have to have missing values. At least in my applications, often the whole rows are missing and not just selective fields. In those cases, try to insert the rows before.
The most simple method would be to replace the missing values with the columns average like this
SELECT time, IFNULL( value, AVG(value) OVER() ) value FROM tbl
In this example, missing values of the column value are replaced by the column’s average value.
In the following, I’ll demonstrate a more sophisticated approach. A user defined function is used to interpolate the values as follows: It calculates the mean between the preceeding and following values. If it is the last observation, the last value is replicated and vice versa if it is the first observation. If you use the data afterwards for prediction tasks, you may change this behaviour, because it leaks feature information into the past. Nevertheless, for other use cases it should be fine.
CREATE TEMP FUNCTION arrStripNulls(arr ANY TYPE) AS (( SELECT ARRAY_AGG(a) FROM UNNEST(arr) a WHERE a is not NULL )); CREATE TEMP FUNCTION linearInterpolation(val INT64, prev ARRAY<INT64>, next ARRAY<INT64>) AS (( SELECT CASE WHEN val is not NULL THEN val WHEN prev[OFFSET(0)] is not NULL and next[OFFSET(0)] is not NULL THEN (prev[OFFSET(0)] + next[OFFSET(0)]) / 2 ELSE (arrStripNulls(prev)[SAFE_OFFSET(0)] + arrStripNulls(next)[SAFE_OFFSET(0)]) / 2 END as x ));
These two function can be easily applied to some sample data as follows:
WITH t as (SELECT [ STRUCT(1 as t, 4 as v), STRUCT(2 as t, NULL as v), STRUCT(3 as t, 6 as v), STRUCT(4 as t, NULL as v), STRUCT(5 as t, NULL as v), STRUCT(6 as t, NULL as v), STRUCT(7 as t, 9 as v), STRUCT(8 as t, 3 as v), STRUCT(9 as t, NULL as v), STRUCT(10 as t, 4 as v) ] as x) SELECT tbl.t, tbl.v as raw, linearInterpolation( tbl.v, ARRAY_AGG(tbl.v) OVER (ORDER BY tbl.t desc ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), ARRAY_AGG(tbl.v) OVER (ORDER BY tbl.t asc ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) ) as v_int FROM t, unnest(x) as tbl ORDER BY t asc