anofox-forecast-eda · diff

v0.15.3 to v2026.09.26

84 added, 20 removed. Audit A to A.

---

name: anofox-forecast-eda

description: >

Exploratory data analysis, data quality, and statistical diagnostics for

- the anofox_forecast DuckDB extension — 34 per-series statistics,

- data-quality scoring, quality-report summaries, 117 tsfresh-compatible

+ the anofox_forecast DuckDB extension — 36 per-series statistics,

+ data-quality scoring, quality-report summaries, 117 tsfresh-inspired

feature extraction, and 7 diagnostic functions covering stationarity

(ADF, KPSS, combined verdict) and residual adequacy (Ljung-Box,

Durbin-Watson, Jarque-Bera, combined report). Use before forecasting to

understand series characteristics (length, gaps, trend, seasonality

strength, intermittency) or to validate model residuals after fitting.

- version: 0.15.3

+ version: 2026.09.26

user-invocable: false

---

# Anofox Forecast — EDA & Data Quality Cheat Sheet

- **Extension:** `anofox_forecast` v0.15.3 | **DuckDB:** v1.4.5 LTS / v1.5.4+ | **Dual naming:** `ts_*` and `anofox_fcst_ts_*`

+ **Extension:** `anofox_forecast` v2026.09.26 (Rust crate `anofox-forecast` v0.15.10) | **DuckDB:** v1.4.5 LTS / v1.5.4+ | **Dual naming:** `ts_*` and `anofox_fcst_ts_*`

Understand your data before modelling it: per-series statistics, data-quality scores, feature vectors for ML.

## Statistics

- ### `ts_stats` (table function) — 34 metrics per series

+ ### `ts_stats` (table function) — 36 metrics per series

```sql

ts_stats(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN,

- frequency VARCHAR) → TABLE

+ frequency VARCHAR, seasonal_period := 0) → TABLE

```

- Returns 34 columns: `length`, `n_nulls`, `n_nan`, `n_zeros`, `n_positive`, `n_negative`, `n_unique_values`, `is_constant`, `n_zeros_start`, `n_zeros_end`, `plateau_size`, `plateau_size_nonzero`, `mean`, `median`, `std_dev`, `variance`, `min`, `max`, `range`, `sum`, `skewness`, `kurtosis`, `tail_index`, `bimodality_coef`, `trimmed_mean`, `coef_variation`, `q1`, `q3`, `iqr`, `autocorr_lag1`, `trend_strength`, `seasonality_strength`, `entropy`, `stability`, plus date-derived `expected_length` and `n_gaps`.

+ Returns 36 columns: `length`, `n_nulls`, `n_nan`, `n_zeros`, `n_positive`, `n_negative`, `n_unique_values`, `is_constant`, `n_zeros_start`, `n_zeros_end`, `plateau_size`, `plateau_size_nonzero`, `mean`, `median`, `std_dev`, `variance`, `min`, `max`, `range`, `sum`, `skewness`, `kurtosis`, `tail_index`, `bimodality_coef`, `trimmed_mean`, `coef_variation`, `q1`, `q3`, `iqr`, `autocorr_lag1`, `trend_strength`, `seasonality_strength`, `entropy`, `stability`, plus date-derived `expected_length`, `n_gaps`, and `n_gap_runs`.

+ **v0.10.0:** `skewness`/`kurtosis` are now textbook scipy `bias=False` G1/

+ excess-G2 (previously mixed a population moment with a sample std-dev); `bimodality_coef`

+ uses the SAS small-sample formula; `entropy` is now Pincus (1991) approximate entropy

+ (`m=2`, `r=0.2*sample sd`; `NULL` for an interior-missing or fewer-than-3-present series) —

+ previously a 10-bin Shannon entropy; `n_gaps` is now the number of **missing periods**

+ (previously gap *occurrences*) — the old occurrence count is the new `n_gap_runs` column.

+ `trend_strength`/`seasonality_strength` are now STL-based (`F_T`/`F_S`, Wang/Hyndman) via a

+ new `seasonal_period` parameter (see "Strength thresholds" below). `is_constant` (here and in

+ `ts_data_quality`) is SCALE-RELATIVE (D-13, G25): `max - min <= 1e-10 * max(1, max|x|)` over

+ present (non-NULL, non-NaN) values — NOT the same thing as `n_unique_values == 1`, which stays

+ an exact bit-identity count. `[1e12, 1e12 + 0.001]` is now constant; `[1.0, 1.0 + 1e-9]` is

+ not (the absolute-epsilon tolerance before this plan was the inverse on both).

```sql

-- Every stat for every series

SELECT * FROM ts_stats('sales', product_id, ds, y, '1d');

- -- Quick sanity check

- SELECT product_id, length, n_nulls, n_gaps, trend_strength, seasonality_strength

+ -- Quick sanity check: n_gaps = missing periods, n_gap_runs = gap occurrences

+ SELECT product_id, length, n_nulls, n_gaps, n_gap_runs, trend_strength, seasonality_strength

FROM ts_stats('sales', product_id, ds, y, '1d')

WHERE length < 30 OR n_gaps > 0

ORDER BY n_gaps DESC;

+ -- Explicit weekly period instead of auto-detection

+ SELECT product_id, trend_strength, seasonality_strength

+ FROM ts_stats('sales', product_id, ds, y, '1d', seasonal_period := 7);

```

### `ts_stats_agg` (aggregate)

For custom `GROUP BY` shapes. Takes `(date, value)` — DO NOT wrap in `LIST(...)`.

```sql

ts_stats_agg(date_col TIMESTAMP, value_col DOUBLE)

→ STRUCT(length UBIGINT, n_nulls UBIGINT, …, mean DOUBLE, …)

```

```sql

SELECT product_id,

ts_stats_agg(ds, y).mean AS mean,

ts_stats_agg(ds, y).seasonality_strength AS seas_str

FROM sales GROUP BY product_id;

```

### `ts_stats_by` — alias for `ts_stats`

### `ts_stats_summary` — aggregate stats across all groups

Summarise the per-group stats into panel-level statistics (mean, std, min, max of each metric).

```sql

SELECT * FROM ts_stats_summary('sales', product_id, ds, y, '1d');

```

## Data quality

### `ts_data_quality` (table function) — per-series score card

```sql

ts_data_quality(source VARCHAR, unique_id_col COLUMN, date_col COLUMN, value_col COLUMN,

n_short INTEGER, frequency VARCHAR) → TABLE

```

- Returns `unique_id` (group column **renamed** — the extension normalises it to `unique_id` in this output, even if the input col was `product_id`), plus `overall_score`, `structural_score`, `temporal_score`, `magnitude_score`, `behavioral_score`, `n_gaps`, `n_missing`, `is_constant`.

+ Returns `unique_id` (group column **renamed** — the extension normalises it to `unique_id` in this output, even if the input col was `product_id`), plus `overall_score`, `structural_score`, `temporal_score`, `magnitude_score`, `behavioral_score`, `n_gaps`, `n_gap_runs`, `n_missing`, `is_constant`, `is_short`.

- `n_short` is the series-length threshold below which a series is flagged short (typical: 14 for daily, 12 for monthly).

+ `n_short` is the minimum "not short" present-value count (typical: 14 for daily, 12 for

+ monthly) — it drives `structural_score`'s length factor and the `is_short` flag.

+ `frequency` (`'1d'`, `'1mo'`, ... or `NULL`/`''` to auto-detect) is used, together with

+ each series' real dates, for `n_gaps`/`n_gap_runs`/`temporal_score`:

+ - `n_gaps` = missing periods (expected timestamps absent under the frequency) — same

+ meaning as `ts_stats`'s `n_gaps`.

+ - `n_gap_runs` = gap occurrences (contiguous runs of missing periods).

+ - `temporal_score` = `clamp(1 - 5 * n_gaps / expected_length, 0, 1)`.

+ - `behavioral_score` scores constancy, robust (Iglewicz-Hoaglin) outliers, and long

+ zero-runs only — a smooth/predictable series carries no autocorrelation penalty.

```sql

SELECT * FROM ts_data_quality('sales', product_id, ds, y, 14, '1d');

-- Rank series by quality (note: output col is `unique_id`, not `product_id`)

- SELECT unique_id, overall_score, structural_score, temporal_score

+ SELECT unique_id, overall_score, structural_score, temporal_score, n_gaps, is_short

FROM ts_data_quality('sales', product_id, ds, y, 14, '1d')

ORDER BY overall_score;

```

### `ts_data_quality_by` — table-macro variant

Grouped macro sibling of `ts_data_quality` — same per-series quality scoring by group. Signature `ts_data_quality_by(source, unique_id_col, date_col, value_col, n_short, frequency)`.

### `ts_data_quality_agg` (aggregate)

- Same as `ts_stats_agg` — takes `(date, value)`, returns a STRUCT.

+ Takes `(date, value)`, returns a STRUCT — no `n_short`/`frequency` arguments: `n_short`

+ is always `30` and frequency always auto-detects from the group's own timestamps.

+ Keeps `NULL`-valued rows (counted in `n_missing`); skips only `NULL`-timestamp rows.

```sql

SELECT product_id,

ts_data_quality_agg(ds, y).overall_score AS q

FROM sales GROUP BY product_id;

```

### `ts_data_quality_summary` — panel-level roll-up

```sql

SELECT * FROM ts_data_quality_summary('sales', product_id, ds, y, 14);

```

### `ts_quality_report` — human-readable report

### `ts_validate_timestamps_summary_by` — per-group timestamp validation

Summarises timestamp regularity per group against an expected grid — flags gaps, duplicates, and irregular spacing before forecasting. Signature `ts_validate_timestamps_summary_by(source, group_col, date_col, expected_timestamps)`.

## Diagnostics & validation (v0.7.0)

**Requires: json extension** (same as detection). Enable auto-load once per session:

```sql

SET autoinstall_known_extensions = 1;

SET autoload_known_extensions = 1;

```

Seven functions in two groups. The combined-verdict functions (`ts_stationarity`, `ts_residual_diagnostics`) are the recommended entry points — they run the component tests internally and return a single adequacy flag.

### Stationarity trio

- #### `ts_adf` / `ts_adf_by` — Augmented Dickey-Fuller unit-root test

+ **Current limitations** (full detail in `docs/api/10-diagnostics.md`): `ts_adf` is currently

+ **non-augmented** (constant-only regression of Δy on y₋₁; `max_lags` only trims the sample,

+ it does not add augmentation terms); the lag reported is chosen by minimum RSS, not AIC;

+ p-values for both `ts_adf` and `ts_kpss` are a coarse step-table lookup — NOT MacKinnon

+ interpolation and NOT cross-validated against statsmodels — and KPSS p-values are an

+ unbounded extrapolation, not clamped to `[0.01, 0.10]`.

+ (fix planned for v0.10.0, Phase 11/17)

+ #### `ts_adf` / `ts_adf_by` — Dickey-Fuller unit-root test (currently non-augmented)

H0: series has a unit root (non-stationary). Reject H0 (p < 0.05) → stationary.

```sql

-- Scalar form: takes LIST(value ORDER BY date) (sourced from stationarity.sql Section 2)

SELECT

(adf).statistic AS t_statistic,

(adf).p_value AS p_value,

(adf).lags AS lags_used,

(adf).is_stationary AS is_stationary,

ROUND((adf).cv_5pct, 3) AS critical_value_5pct

FROM (

SELECT ts_adf(LIST(y ORDER BY ds)) AS adf

FROM sales_data

WHERE product_id = 'mean_revert'

);

-- Grouped macro (sourced from stationarity.sql Section 3)

SELECT product_id,

ROUND((adf).statistic, 4) AS t_statistic,

(adf).p_value,

(adf).lags,

(adf).is_stationary,

ROUND((adf).cv_1pct, 2) AS cv_1pct,

ROUND((adf).cv_5pct, 2) AS cv_5pct,

ROUND((adf).cv_10pct, 2) AS cv_10pct

FROM ts_adf_by('sales_data', product_id, ds, y)

ORDER BY product_id;

```

Optional second arg to scalar form: `ts_adf(LIST(y ORDER BY ds), max_lags INTEGER)` — `-1` = auto (default).

#### `ts_kpss` / `ts_kpss_by` — KPSS level-stationarity test

H0: series IS level-stationary (opposite direction from ADF). Fail to reject (p > 0.05) → stationary.

```sql

-- Scalar form (sourced from stationarity.sql Section 5)

WITH s AS (SELECT i AS ds, sin(i/6.0) + (i%5)*0.01 AS y FROM range(1, 80) t(i))

SELECT (ts_kpss(LIST(y ORDER BY ds))).statistic AS kpss_stat,

(ts_kpss(LIST(y ORDER BY ds))).is_stationary AS is_stationary

FROM s;

-- Grouped macro (sourced from stationarity.sql Section 5)

SELECT product_id, (kpss).statistic, (kpss).is_stationary

FROM ts_kpss_by('sales_data', product_id, ds, y)

ORDER BY product_id;

```

#### `ts_stationarity` / `ts_stationarity_by` — combined four-way verdict (recommended entry point)

Runs ADF + KPSS internally; returns a combined `verdict` field plus the individual flags.

```sql

-- Grouped macro (sourced from stationarity.sql Section 6)

SELECT product_id,

(stationarity).verdict,

(stationarity).adf_is_stationary,

(stationarity).kpss_is_stationary

FROM ts_stationarity_by('sales_data', product_id, ds, y)

ORDER BY product_id;

```

- Verdict interpretation: `'Stationary'` (both agree), `'NonStationary'` (both agree), `'Trend-Stationary'` (KPSS non-stationary + ADF stationary), `'DifferencStationary'` / `'Inconclusive'` (disagreement).

+ Verdict strings (lowercase_with_underscores — not the CamelCase/hyphenated forms shown in

+ older docs): `'stationary'` (both tests agree series is stationary), `'trend_stationary'`

+ (ADF rejects unit root + KPSS rejects level-stationarity), `'difference_stationary'` (both

+ tests point to a unit root), `'non_stationary'` (the remaining combination). **Current

+ limitation:** the `trend_stationary`/`non_stationary` labels are currently inverted relative

+ to the standard statsmodels convention, and a constant series currently returns

+ `verdict = 'non_stationary'` rather than `'stationary'`. Prefer the individual

+ `adf_is_stationary`/`kpss_is_stationary` flags if you need the textbook interpretation.

+ (fix planned for v0.10.0, Phase 17)

---

### Residual-adequacy set

Apply to **residuals** from a fitted model (forecast errors), not to the raw series.

#### `ts_ljung_box` / `ts_ljung_box_by` — autocorrelation test (white-noise check)

H0: residuals are white noise (no autocorrelation). Reject (p < 0.05) → residuals are correlated; model under-fits.

```sql

-- Grouped macro (sourced from residuals.sql)

SELECT series_id, (lb).statistic AS q_stat, (lb).p_value, (lb).lags

FROM ts_ljung_box_by('resids', series_id, ds, e) AS t(series_id, lb)

ORDER BY series_id;

```

#### `ts_durbin_watson` / `ts_durbin_watson_by` — first-order autocorrelation

DW statistic near 2 = no autocorrelation; < 2 = positive; > 2 = negative.

```sql

-- Grouped macro (sourced from residuals.sql)

SELECT series_id, (dw).statistic, (dw).interpretation

FROM ts_durbin_watson_by('resids', series_id, ds, e) AS t(series_id, dw)

ORDER BY series_id;

```

#### `ts_jarque_bera` / `ts_jarque_bera_by` — residual normality

H0: residuals are normally distributed. Reject (p < 0.05) → non-normal (may affect interval coverage).

```sql

-- Grouped macro (sourced from residuals.sql)

SELECT series_id, (jb).statistic, (jb).p_value, (jb).skewness, (jb).excess_kurtosis

FROM ts_jarque_bera_by('resids', series_id, ds, e) AS t(series_id, jb)

ORDER BY series_id;

```

#### `ts_residual_diagnostics` / `ts_residual_diagnostics_by` — combined adequacy report (recommended entry point)

Runs Ljung-Box, Durbin-Watson, and Jarque-Bera internally; returns a single `adequate BOOLEAN` verdict plus component fields.

```sql

-- Grouped macro (sourced from residuals.sql)

SELECT series_id, (rd).lb_p_value, (rd).dw_interpretation, (rd).adequate

FROM ts_residual_diagnostics_by('resids', series_id, ds, e) AS t(series_id, rd)

ORDER BY series_id;

```

### Diagnostics gotchas

- **Stationarity tests need sufficient length**: ADF / KPSS require at least ~20-30 observations for reliable p-values. On very short series, results are inconclusive by default.

- **Apply residual tests to residuals, not raw values**: Pass `(y - yhat)` as the value column, not the original series — `ts_ljung_box` on raw series will almost always reject, which is expected and uninformative.

- **`ts_adf_by` output column names** are the group column name (preserved) plus the `adf` STRUCT. Access sub-fields with `(adf).statistic`, etc.

- **KPSS H0 direction is opposite to ADF**: KPSS non-rejection means stationary; ADF non-rejection means non-stationary. Use `ts_stationarity_by` to avoid this confusion.

---

- ## Feature extraction (117 tsfresh-compatible features)

+ ## Feature extraction (117 tsfresh-inspired features)

### `ts_features_by` — extract all 117 features

```sql

ts_features_by(source VARCHAR, group_col COLUMN, date_col COLUMN, value_col COLUMN) → TABLE

```

- Returns the group column + 116 feature columns: `mean`, `standard_deviation`, `skewness`, `kurtosis`, `length`, `linear_trend_slope`, `autocorrelation_lag1`, …

+ Returns the group column + 117 feature columns: `mean`, `standard_deviation`, `skewness`, `kurtosis`, `length`, `linear_trend_slope`, `autocorrelation_lag1`, …

+ Every feature matches tsfresh 0.21.2 (or a direct numpy/scipy reference for the 9 features with no tsfresh counterpart) on a 9-series fixture suite, or carries a documented, intentional deviation — see [`docs/api/21-feature-reference.md`](../../../../docs/api/21-feature-reference.md)'s Deviation column.

```sql

-- Extract all features

SELECT * FROM ts_features_by('sales', product_id, ds, y);

-- Filter by feature values

SELECT product_id, mean, linear_trend_slope

FROM ts_features_by('sales', product_id, ds, y)

WHERE length > 30 AND autocorrelation_lag1 > 0.5;

```

### `ts_features` (scalar)

- Scalar over `(date, value)`. Same 116 outputs as struct fields.

+ Scalar over `(date, value)`. Same 117 outputs as struct fields.

### `ts_features_list` / `ts_features_table`

Discover the feature catalogue:

```sql

SELECT column_name, feature_name, parameter_suffix

FROM ts_features_list()

WHERE feature_name LIKE '%autocorr%';

```

### `ts_features_config_template` — starter config

Table function (no args) returning a template config row for feature extraction — a scaffold to edit down into a custom feature subset (feeds `ts_features_config_from_json` / `ts_features_config_from_csv` below).

```sql

SELECT * FROM ts_features_config_template();

```

### Custom feature subsets

Configure via JSON or CSV:

```sql

-- Load a JSON config

SELECT * FROM ts_features_by('sales', id, ds, y,

ts_features_config_from_json('{"features": ["mean", "std", "autocorrelation_lag1"]}'));

-- Or CSV

SELECT * FROM ts_features_by('sales', id, ds, y,

ts_features_config_from_csv('mean,std,skewness'));

```

### `ts_features_agg` — aggregate variant

For custom GROUP BY:

```sql

SELECT id, ts_features_agg(ds, y).mean, ts_features_agg(ds, y).autocorrelation_lag1

FROM sales GROUP BY id;

```

## Gotchas

- **`ts_stats` and `ts_data_quality` are TABLE functions** — call from FROM, not SELECT. Older docs incorrectly showed them as scalars over `LIST(...)`.

- **`_agg` variants take `(date, value)` directly** — no `LIST()` wrapping. Wrapping in `LIST(...)` errors as "No function matches …".

- - **Seasonality strength thresholds**: `trend_strength` and `seasonality_strength` in `ts_stats` are ∈ [0, 1] — treat > 0.6 as strong, < 0.2 as weak. Use to gate model selection downstream.

+ - **`trend_strength` / `seasonality_strength` formulas (v0.10.0, STAT-02, D-02)**: both are

+ Wang/Hyndman STL decomposition strengths from an R-faithful STL —

+ `trend_strength = max(0, 1 - Var(R)/Var(T+R))`,

+ `seasonality_strength = max(0, 1 - Var(R)/Var(S+R))` — computed via

+ `stats::stl` (one period), the `forecast::mstl` iteration (several periods,

+ reporting the **maximum** per-period strength), or a `supsmu` trend when no

+ period is usable. The period comes from the new `seasonal_period` parameter

+ (`0` auto-detects, `1` forces non-seasonal, `>= 2` is explicit).

+ `seasonality_strength` is `NULL` when no period is usable (none detected,

+ `seasonal_period := 1`, or `n <= 2 * seasonal_period`) or when the series

+ decomposes to zero seasonal variance (e.g. a constant series).

+ **Rule-of-thumb thresholds** (heuristics, not statistical tests — validate

+ against your own data): **below 0.3** weak, **0.3 to 0.6** moderate, **0.6

+ and above** strong. Example: the audit's 150-point random walk gives

+ `seasonality_strength` about 0.09 (weak, matching "mostly noise");

+ `AirPassengers` gives about 0.94 (strong, matching its well-known yearly

+ cycle).

- **JSON extension is required** by a subset of quality / feature functions. Enable auto-load once per session: `SET autoinstall_known_extensions=1; SET autoload_known_extensions=1;`.

- **`ts_r2(DOUBLE[] actual, DOUBLE[] predicted) → DOUBLE`** is a scalar **regression R² metric**, not an EDA statistic — it lives with the scalar metrics family in `anofox-forecast-backtest`. Use it under `GROUP BY` to score fit quality, e.g. `SELECT ts_r2([1.0,2,3,4], [1.1,2.0,2.9,4.2]);`.

+ - **`ts_features_agg` / `ts_features` / `ts_features_by` share one Rust NULL policy** (Phase 12, D-11): a NULL-valued row is no longer spliced out independently — leading/trailing NULLs are trimmed, an interior NULL is a hard error naming its 1-based position, and an all-NULL series returns NULL (the whole struct for the aggregate, a row of NULL feature columns for the table function). All three surfaces agree on the same series.

## Canonical EDA pipeline

```sql

-- 1. Quality gate

CREATE OR REPLACE TABLE quality AS

SELECT * FROM ts_data_quality('raw', product_id, ds, y, 14, '1d');

-- 2. Series-level stats

CREATE OR REPLACE TABLE stats AS

SELECT * FROM ts_stats('raw', product_id, ds, y, '1d');

-- 3. Filter — keep only high-quality, forecastable series

CREATE OR REPLACE TABLE forecastable AS

SELECT r.*

FROM raw r

- JOIN quality q ON r.product_id = q.product_id

+ JOIN quality q ON r.product_id = q.unique_id -- ts_data_quality renames the group col to unique_id

JOIN stats s ON r.product_id = s.product_id

WHERE q.overall_score >= 0.6

AND s.length >= 30

AND NOT s.is_constant;

-- 4. Extract features for ML (optional)

CREATE OR REPLACE TABLE features AS

SELECT * FROM ts_features_by('forecastable', product_id, ds, y);

```

See also: `anofox-forecast-data-prep` (impute + drop bad series based on these stats), `anofox-forecast-detection` (`trend_strength` / `seasonality_strength` inform detection thresholds), `anofox-forecast-models` (use quality score / features to gate model selection).

Reference docs:

- `docs/api/03-statistics.md`

- `docs/api/20-feature-extraction.md`

- `docs/api/21-feature-reference.md`