anofox-forecast
Comprehensive reference for the Anofox Forecast DuckDB extension (v0.4.6). Use when working with ts_* or anofox_fcst_ts_* functions, time series forecasting in DuckDB, or the anofox_forecast extension. Provides API signatures, model selection, common workflows, and critical gotchas.
معلومات المصدر
- المستودع
- majiayu000/claude-skill-registry-data
- آخر نشاط في المصدر
- ٢٣ يونيو ٢٠٢٦ في ١١:٠٢
- لغة SKILL.md المكتشفة
- الإنجليزية
- النجوم
- ٢١
- التفرعات
- ٨
خيارات التثبيت
يُحدَّد Prompt الذي يراجع المصدر أولًا بشكل افتراضي. يمكنك التبديل إلى أمر مباشر أو تنزيل نسخة محلية.
مراجعة ملفات المصدر
اقرأ SKILL.md وأي ملفات مرافقة يعرضها SkillsMP قبل أن تقرر التثبيت.
مستكشف الملفات
2 ملفاتعرض SKILL.md
SKILL.md
تعليمات المصدر · معاينة للقراءة فقط- name
- anofox-forecast
- description
- Comprehensive reference for the Anofox Forecast DuckDB extension (v0.4.6). Use when working with ts_* or anofox_fcst_ts_* functions, time series forecasting in DuckDB, or the anofox_forecast extension. Provides API signatures, model selection, common workflows, and critical gotchas.
- version
- 0.4.6
- user-invocable
- false
# Anofox Forecast DuckDB Extension — Cheat Sheet
**Extension:** `anofox_forecast` v0.4.6 | **DuckDB:** v1.4.x+ | **Dual naming:** `ts_*` and `anofox_fcst_ts_*`
## Installation
```sql
LOAD anofox_forecast;
-- All functions available as ts_* and anofox_fcst_ts_* (identical)
```
---
## Critical Gotchas
1. **Seasonality is NOT auto-detected.** You must pass `seasonal_period` explicitly.
Detect first with `ts_detect_periods_by`, then pass to forecasting.
2. **DO NOT chain `_by` table functions in CTEs.** Returns 0 rows silently under parallel execution.
Always `CREATE TABLE` between pipeline steps:
```sql
-- BROKEN (0 rows):
WITH step1 AS (SELECT * FROM ts_fill_gaps_by(...))
SELECT * FROM ts_fill_nulls_const_by('step1', ...);
-- CORRECT:
CREATE TABLE step1 AS SELECT * FROM ts_fill_gaps_by(...);
SELECT * FROM ts_fill_nulls_const_by('step1', ...);
```
3. **Model names are case-sensitive.** `'AutoETS'` works, `'autoets'` errors.
4. **`ts_cv_forecast_by` requires pre-created folds.** Input table must have `fold_id` and `split` columns
(from `ts_cv_folds_by` or `ts_cv_split_by`). Passing raw data throws a clear error.
5. **`ts_forecast_by` requires frequency as 7th positional parameter.** No default — you must specify it:
```sql
-- WRONG: missing frequency
SELECT * FROM ts_forecast_by('sales', id, date, val, 'Naive', 12);
-- CORRECT:
SELECT * FROM ts_forecast_by('sales', id, date, val, 'Naive', 12, '1d');
```
6. **Metric `_by` table macros are deprecated.** Use scalar functions with `GROUP BY`:
```sql
-- Deprecated: SELECT * FROM ts_mae_by(...)
-- Use instead:
SELECT id, ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS mae
FROM results GROUP BY id;
```
7. **Always use `ORDER BY` in `LIST()` for temporal correctness:**
```sql
LIST(value ORDER BY date) -- correct
LIST(value) -- wrong: order not guaranteed
```
---
## Three API Styles
### 1. Table Macros (primary — use these)
Operate on table names as strings. Handle grouping automatically.
```sql
SELECT * FROM ts_forecast_by('sales', product_id, date, revenue, 'AutoETS', 14, '1d',
MAP{'seasonal_period': '7'});
```
### 2. Scalar Functions
Operate on arrays. Use with `LIST()` aggregation and `GROUP BY`.
```sql
SELECT product_id,
ts_mae(LIST(actual ORDER BY date), LIST(forecast ORDER BY date)) AS mae
FROM results GROUP BY product_id;
```
### 3. Aggregate Functions
Return structs. Access fields with `(result).field_name`.
```sql
SELECT product_id, (ts_stats(LIST(value ORDER BY date))).*
FROM sales GROUP BY product_id;
```
---
## Parameter Syntax
### STRUCT (recommended)
```sql
MAP{'seasonal_period': '7'}
MAP{'seasonal_periods': '[7, 365]'}
MAP{'method': 'autoperiod', 'max_period': '28'}
```
All param values are strings (even numbers). Arrays use JSON syntax: `'[7, 365]'`.
### Frequency Strings
| Format | Examples |
|--------|---------|
| Polars style | `'1d'`, `'1h'`, `'30m'`, `'1w'`, `'1mo'`, `'1q'`, `'1y'` |
| DuckDB INTERVAL | `'1 day'`, `'1 hour'` |
| Raw integer | `'1'`, `'7'` (interpreted as days) |
---
## Common Workflows
### 1. Basic Forecast
```sql
-- Forecast 14 days ahead with weekly seasonality
SELECT * FROM ts_forecast_by(
'sales', product_id, date, revenue,
'HoltWinters', 14, '1d',
MAP{'seasonal_period': '7'}
);
```
### 2. Data Preparation Pipeline (CREATE TABLE between steps!)
```sql
-- Step 1: Fill gaps
CREATE TABLE gaps_filled AS
SELECT * FROM ts_fill_gaps_by('raw_data', product_id, date, value, '1d');
-- Step 2: Impute NULLs
CREATE TABLE nulls_filled AS
SELECT * FROM ts_fill_nulls_const_by('gaps_filled', product_id, date, value, 0.0);
-- Step 3: Drop short series
CREATE TABLE clean_data AS
SELECT * FROM ts_drop_short_by('nulls_filled', product_id, 20);
```
### 3. Detect Seasonality → Forecast
```sql
-- Step 1: Detect
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('sales', product_id, date, value, MAP{});
-- Returns e.g. primary_period = 7 (weekly)
-- Step 2: Forecast with detected period
SELECT * FROM ts_forecast_by(
'sales', product_id, date, value,
'AutoETS', 14, '1d', MAP{'seasonal_period': '7'}
);
```
### 4. Cross-Validation & Model Comparison
```sql
-- Step 1: Create folds
CREATE TABLE cv_folds AS
SELECT * FROM ts_cv_folds_by('data', unique_id, ds, y, 3, 12, MAP{});
-- Step 2: Forecast per fold (for each model)
CREATE TABLE cv_naive AS
SELECT * FROM ts_cv_forecast_by('cv_folds', unique_id, ds, y, 'Naive', MAP{});
CREATE TABLE cv_autoets AS
SELECT * FROM ts_cv_forecast_by('cv_folds', unique_id, ds, y, 'AutoETS',
MAP{'seasonal_period': '7'});
-- Step 3: Compare metrics
SELECT 'Naive' AS model,
ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS mae,
ts_rmse(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)) AS rmse
FROM cv_naive GROUP BY ALL
UNION ALL
SELECT 'AutoETS',
ts_mae(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds)),
ts_rmse(LIST(y ORDER BY ds), LIST(yhat ORDER BY ds))
FROM cv_autoets GROUP BY ALL;
```
### 5. Full Production Pipeline
```sql
-- 1. Quality check
SELECT id, (stats).length, (stats).n_nulls, (stats).n_gaps
FROM ts_stats_by('raw', product_id, date, value, '1d');
-- 2. Prep (materialize each step!)
CREATE TABLE step1 AS
SELECT * FROM ts_fill_gaps_by('raw', product_id, date, value, '1d');
CREATE TABLE step2 AS
SELECT * FROM ts_fill_nulls_const_by('step1', product_id, date, value, 0.0);
CREATE TABLE clean AS
SELECT * FROM ts_drop_short_by('step2', product_id, 20);
-- 3. Detect seasonality
SELECT id, (periods).primary_period
FROM ts_detect_periods_by('clean', product_id, date, value, MAP{});
-- 4. Backtest
CREATE TABLE cv_folds AS
SELECT * FROM ts_cv_folds_by('clean', product_id, date, value, 5, 14, MAP{});
CREATE TABLE backtest AS
SELECT * FROM ts_cv_forecast_by('cv_folds', product_id, date, value, 'AutoETS',
MAP{'seasonal_period': '7'});
-- 5. Evaluate
SELECT product_id,
ts_mae(LIST(y ORDER BY date), LIST(yhat ORDER BY date)) AS mae,
ts_rmse(LIST(y ORDER BY date), LIST(yhat ORDER BY date)) AS rmse
FROM backtest GROUP BY product_id;
-- 6. Forecast
CREATE TABLE forecasts AS
SELECT * FROM ts_forecast_by('clean', product_id, date, value,
'AutoETS', 14, '1d', MAP{'seasonal_period': '7'});
-- 7. Conformal intervals
CREATE TABLE calibration AS
SELECT * FROM ts_conformal_calibrate('backtest', value, yhat, {'alpha': 0.1});
SELECT * FROM ts_conformal_apply_by(
'forecasts', product_id, yhat,
(SELECT conformity_score FROM calibration)
);
```
---
## Model Quick Reference (32 Models)
### Automatic Selection (6)
| Model | Optional Params | Best For |
|-------|----------------|----------|
| `AutoETS` | `seasonal_period` | Unknown patterns (default pick) |
| `AutoARIMA` | `seasonal_period` | Unknown patterns, ARIMA family |
| `AutoTheta` | `seasonal_period` | Unknown patterns, Theta family |
| `AutoMFLES` | `seasonal_periods[]` | Multiple seasonalities |
| `AutoMSTL` | `seasonal_periods[]` | Multiple seasonalities |
| `AutoTBATS` | `seasonal_periods[]` | Multiple seasonalities |
### Basic (6)
| Model | Required | Optional | Best For |
|-------|----------|----------|----------|
| `Naive` | — | — | Baseline benchmark |
| `SMA` | — | `window` (def: 5) | Smoothed baseline |
| `SeasonalNaive` | **seasonal_period** | — | Seasonal baseline |
| `SES` | — | `alpha` (def: 0.3) | No trend, no seasonality |
| `SESOptimized` | — | — | Optimized SES |
| `RandomWalkDrift` | — | — | Trend without seasonality |
### Exponential Smoothing (4)
| Model | Required | Optional |
|-------|----------|----------|
| `Holt` | — | `alpha`, `beta` |
| `HoltWinters` | **seasonal_period** | `alpha`, `beta`, `gamma` |
| `SeasonalES` | **seasonal_period** | `alpha`, `gamma` |
| `SeasonalESOptimized` | **seasonal_period** | — |
### Theta Methods (5)
| Model | Optional |
|-------|----------|
| `Theta` | `seasonal_period`, `theta` |
| `OptimizedTheta` | `seasonal_period` |
| `DynamicTheta` | `seasonal_period`, `theta` |
| `DynamicOptimizedTheta` | `seasonal_period` |
| `AutoTheta` | `seasonal_period` |
### State Space & ARIMA (4)
| Model | Required | Optional |
|-------|----------|----------|
| `ETS` | — | `seasonal_period`, `model` |
| `AutoETS` | — | `seasonal_period` |
| `ARIMA` | **p**, **d**, **q** | `P`, `D`, `Q`, `s` |
| `AutoARIMA` | — | `seasonal_period` |
### Multiple Seasonality (6)
| Model | Required | Optional |
|-------|----------|----------|
| `MFLES` | **seasonal_periods[]** | `iterations` |
عرض على GitHubملف SKILL.md هذا كبير جدا، لذلك يعرض SkillsMP القسم الاول فقط هنا. عرض على GitHub
