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
- 최근 소스 활동
- 2026년 6월 23일 11:02
- 감지된 SKILL.md 언어
- 영어
- 스타
- 21
- 포크
- 8
설치 방법
기본적으로 소스를 먼저 확인하는 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에서 보기
