Imputation

Fill missing values and transform data for analysis.

Function	Description
fill_nulls_const	Replace NULLs with constant value
fill_nulls_forward	Forward fill (LOCF)
fill_nulls_backward	Backward fill (NOCB)
fill_nulls_mean	Fill with series mean
diff	Differencing transformation

Showing 5 of 5

---

anofox_fcst_ts_fill_nulls_const

Replaces NULL values with a constant value.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column
fill_value	DOUBLE	Yes	Constant value to fill

Output

Returns table with NULLs replaced by specified constant.

Example

-- Fill missing sales with zero
SELECT * FROM anofox_fcst_ts_fill_nulls_const(
    'sales_filled',
    product_id,
    date,
    sales,
    0.0
);

Use when: Missing values represent no activity (e.g., zero sales).

---

anofox_fcst_ts_fill_nulls_forward

Implements forward fill using Last Observation Carried Forward (LOCF).

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column

Output

Returns table with NULLs filled forward from prior values.

Example

-- Forward fill missing values
SELECT * FROM anofox_fcst_ts_fill_nulls_forward(
    'sales_filled',
    product_id,
    date,
    sales
);

Use when: Missing values should inherit from previous observation (e.g., inventory levels, prices).

---

anofox_fcst_ts_fill_nulls_backward

Implements backward fill using Next Observation Carried Backward (NOCB).

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column

Output

Returns table with NULLs filled backward from subsequent values.

Example

-- Backward fill missing values
SELECT * FROM anofox_fcst_ts_fill_nulls_backward(
    'sales_filled',
    product_id,
    date,
    sales
);

Use when: Missing values at the start of series need filling, or when future values are more representative.

---

anofox_fcst_ts_fill_nulls_mean

Fills NULL values with the mean computed per series.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column

Output

Returns table with NULLs replaced by series mean.

Example

-- Fill with series mean
SELECT * FROM anofox_fcst_ts_fill_nulls_mean(
    'sales_filled',
    product_id,
    date,
    sales
);

Use when: Missing values should reflect typical behavior without trend bias.

---

anofox_fcst_ts_diff

Computes differenced values for detrending and stationarity transformation.

Parameters

Parameter	Type	Required	Description
table_name	VARCHAR	Yes	Source table
group_col	ANY	No	Group column
date_col	DATE/TIMESTAMP/INTEGER	Yes	Date column
value_col	DOUBLE	Yes	Value column
order	INTEGER	Yes	Differencing order (must be > 0)

Output

Returns table with original and differenced value columns.

Formula: diff[t] = value[t] - value[t-order]

Example

-- First difference (remove trend)
SELECT * FROM anofox_fcst_ts_diff(
    'sales_data',
    product_id,
    date,
    sales,
    1
);

-- Second difference (remove acceleration)
SELECT * FROM anofox_fcst_ts_diff(
    'sales_data',
    product_id,
    date,
    sales,
    2
);

Use when:

• order=1: Remove linear trend, make series stationary
• order=2: Remove quadratic trend, address acceleration
• Preparing data for ARIMA models

---

Imputation Method Selection

Scenario	Recommended Method	Reason
Sales/demand data	fill_nulls_const(0)	No sale = zero
Prices/rates	fill_nulls_forward	Price persists until changed
Sensor data	fill_nulls_mean	Neutral assumption
Start of series	fill_nulls_backward	Initialize with nearest value
Trending data	diff	Make stationary first

---

Complete Imputation Workflow

-- 1. Fill gaps (creates NULLs)
CREATE TABLE step1 AS
SELECT * FROM anofox_fcst_ts_fill_gaps(
    'sales_by_product',
    product_id,
    date,
    sales,
    '1d'
);

-- 2. Forward fill for most NULLs
CREATE TABLE step2 AS
SELECT * FROM anofox_fcst_ts_fill_nulls_forward(
    'step1',
    product_id,
    date,
    sales
);

-- 3. Backward fill for remaining (start of series)
CREATE TABLE step3 AS
SELECT * FROM anofox_fcst_ts_fill_nulls_backward(
    'step2',
    product_id,
    date,
    sales
);

-- 4. Verify no NULLs remain
SELECT COUNT(*) as null_count
FROM step3
WHERE sales IS NULL;

---

Combining Forward and Backward Fill

For complete coverage, combine both methods:

-- Forward fill, then backward fill remainder
CREATE TABLE fully_imputed AS
SELECT * FROM anofox_fcst_ts_fill_nulls_backward(
    (SELECT * FROM anofox_fcst_ts_fill_nulls_forward(
        'gap_filled_data',
        product_id,
        date,
        sales
    )),
    product_id,
    date,
    sales
);

---