Forecasting Intermittent Demand

Handle sparse, irregular, and intermittent data (many zeros or gaps).

What is Intermittent Demand?

Data with frequent zero values or long periods without activity.

Real-World Examples

Domain	Example	Pattern
Inventory	Spare parts	Months of zero sales, then sudden demand
E-commerce	Niche products	Few purchases per month
Manufacturing	Custom orders	Irregular, unpredictable demand
Healthcare	Emergency supplies	Low baseline, spikes during crises

Characteristics

Regular demand:    Sales every day
                   100, 110, 95, 105, 120, ...

Intermittent:      Many zeros with occasional spikes
                   0, 0, 50, 0, 0, 0, 0, 200, 0, 0, ...

Lumpy:            Large fluctuations
                  10, 100, 5, 200, 20, 0, 150, ...

---

Why Standard Models Fail

Traditional models (ARIMA, ETS) struggle with intermittent demand:

Model: AutoETS on intermittent data
Training: [0, 0, 50, 0, 0, 0, 0, 200, 0, ...]
Forecast: [0, 0, 0, 0, 0, 0, ...]
Problem: Model learns to predict zero (safest guess)
Result: Misses actual demand when it occurs

---

Intermittent Demand Models

1. Croston (Classic)

Forecast non-zero occurrences and sizes separately:

Total Demand = Prob(non-zero) × Average Size

Example:

History: [0, 0, 50, 0, 0, 0, 75, 0, 0, 25]

P(non-zero) = 3/10 = 0.3 (30% chance of sale)
Average size = (50 + 75 + 25) / 3 = 50

Forecast = 0.3 × 50 = 15 units/period

When to use:

• Sparse demand with occasional non-zero values
• Stable probability of purchase
• Inventory forecasting

SELECT * FROM TS_FORECAST(
    'spare_parts_demand',
    'date',
    'quantity',
    'Croston',
    30,
    {}
);

2. Croston Optimized

Improved version with better variance estimation:

SELECT * FROM TS_FORECAST(
    'spare_parts',
    'date',
    'qty',
    'CrostonOptimized',
    30,
    {}
);

Improvement: More stable forecast intervals

3. Croston SBA

Syntetos-Boylan adjustment - reduces bias:

SELECT * FROM TS_FORECAST(
    'spare_parts',
    'date',
    'qty',
    'CrostonSBA',
    30,
    {}
);

Best for: Slightly more accurate when demand probability is low

4. ADIDA (Aggregate-Disaggregate Intermittent Demand Algorithm)

Aggregates data to reduce sparsity, then disaggregates:

SELECT * FROM TS_FORECAST(
    'sparse_data',
    'date',
    'demand',
    'ADIDA',
    30,
    {}
);

Best for:

• Very sparse data
• Demand with complex patterns
• When aggregation helps

5. IMAPA (Intermittent Multiple Aggregation Prediction Algorithm)

Multiple aggregation levels for complex intermittency:

SELECT * FROM TS_FORECAST(
    'complex_sparse',
    'date',
    'demand',
    'IMAPA',
    30,
    {'seasonal_period': 7}
);

Best for: Very complex intermittent patterns

6. TSB (Teunter-Syntetos-Babai)

Tailored for intermittent demand with trend:

SELECT * FROM TS_FORECAST(
    'growing_spare_parts',
    'date',
    'demand',
    'TSB',
    30,
    {}
);

Best for: Intermittent demand that's also trending up/down

---

Complete Intermittent Demand Example

Step 1: Create Sample Data

-- Simulate spare parts demand (mostly zeros, occasional spikes)
CREATE TABLE spare_parts AS
SELECT
    DATE '2023-01-01' + INTERVAL (d) DAY AS date,
    CASE
        WHEN RANDOM() < 0.1 THEN CAST(RANDOM() * 200 + 50 AS INT)  -- 10% chance of demand
        ELSE 0
    END AS demand
FROM generate_series(0, 364) t(d)
ORDER BY date;

-- View sample
SELECT * FROM spare_parts LIMIT 30;

Sample output:

date       | demand
-----------|--------
2023-01-01 | 0
2023-01-02 | 0
2023-01-03 | 142
2023-01-04 | 0
2023-01-05 | 0
...
2023-01-25 | 189
2023-01-26 | 0

Step 2: Analyze Sparsity

-- Check demand characteristics
SELECT
    COUNT(*) as total_periods,
    COUNT(CASE WHEN demand = 0 THEN 1 END) as zero_periods,
    ROUND(100.0 * COUNT(CASE WHEN demand = 0 THEN 1 END) / COUNT(*), 1) as zero_pct,
    COUNT(CASE WHEN demand > 0 THEN 1 END) as nonzero_periods,
    ROUND(AVG(CASE WHEN demand > 0 THEN demand ELSE NULL END), 1) as avg_nonzero,
    MAX(demand) as max_demand,
    MIN(CASE WHEN demand > 0 THEN demand ELSE NULL END) as min_nonzero
FROM spare_parts;

Output:

total_periods | zero_pct | nonzero_periods | avg_nonzero | max_demand
--------------|----------|-----------------|-------------|----------
365           | 89.3     | 39              | 125.4       | 249

Interpretation:

• 89.3% zeros → Highly sparse
• Use Croston or ADIDA
• Average demand when it occurs: 125 units

Step 3: Split Data

CREATE TABLE train_spare AS
SELECT * FROM spare_parts WHERE date < '2023-11-01';

CREATE TABLE test_spare AS
SELECT * FROM spare_parts WHERE date >= '2023-11-01';

Step 4: Compare Models

-- Croston
CREATE TABLE fc_croston AS
SELECT 'Croston' as model, * FROM TS_FORECAST(
    'train_spare', 'date', 'demand', 'Croston',
    (SELECT COUNT(*) FROM test_spare), {}
);

-- ADIDA
CREATE TABLE fc_adida AS
SELECT 'ADIDA' as model, * FROM TS_FORECAST(
    'train_spare', 'date', 'demand', 'ADIDA',
    (SELECT COUNT(*) FROM test_spare), {}
);

-- For comparison: naive (shouldn't work well)
CREATE TABLE fc_naive AS
SELECT 'SeasonalNaive' as model, * FROM TS_FORECAST(
    'train_spare', 'date', 'demand', 'SeasonalNaive',
    (SELECT COUNT(*) FROM test_spare), {'seasonal_period': 7}
);

Step 5: Evaluate

-- Compare metrics
WITH joined AS (
    SELECT
        t.date,
        t.demand as actual,
        f.model,
        ROUND(f.point_forecast, 1) as forecast
    FROM test_spare t
    LEFT JOIN (
        SELECT * FROM fc_croston UNION ALL
        SELECT * FROM fc_adida UNION ALL
        SELECT * FROM fc_naive
    ) f ON t.date = f.date_col
)
SELECT
    model,
    ROUND(TS_MAE(LIST(actual), LIST(forecast)), 2) as MAE,
    ROUND(TS_RMSE(LIST(actual), LIST(forecast)), 2) as RMSE,
    ROUND(TS_MAPE(LIST(actual), LIST(forecast)), 2) as MAPE_pct
FROM joined
GROUP BY model
ORDER BY MAE;

Expected output:

model          | MAE  | RMSE | MAPE_pct
---------------|------|------|----------
Croston        | 8.2  | 25.1 | 4.3
ADIDA          | 7.9  | 24.8 | 4.1
SeasonalNaive  | 28.5 | 85.3 | 15.2

---

Handling Different Intermittency Levels

Low Sparsity (10-30% zeros)

-- Can use standard models
SELECT * FROM TS_FORECAST(..., 'AutoETS', ...);

Moderate Sparsity (30-70% zeros)

-- Use Croston or ADIDA
SELECT * FROM TS_FORECAST(..., 'Croston', ...);

High Sparsity (70%+ zeros)

-- Use ADIDA or combine with aggregation
SELECT * FROM TS_FORECAST(..., 'ADIDA', ...);

---

Advanced: Aggregation-Disaggregation

Reduce sparsity through aggregation, then disaggregate:

-- 1. Aggregate daily → weekly (reduces sparsity)
CREATE TABLE weekly_demand AS
SELECT
    DATE_TRUNC('week', date) as week,
    SUM(demand) as weekly_demand
FROM spare_parts
GROUP BY DATE_TRUNC('week', date);

-- 2. Forecast weekly (less sparse)
CREATE TABLE weekly_forecast AS
SELECT * FROM TS_FORECAST(
    'weekly_demand',
    'week',
    'weekly_demand',
    'Croston',
    4,  -- 4 weeks
    {}
);

-- 3. Disaggregate back to daily (optional)
-- Distribute weekly forecast across 7 days based on historical pattern

---

Interpreting Croston Forecasts

Croston forecast = Expected value:

Forecast = 15 units/period

This means:
- 30% chance of demand
- When demand occurs: avg 50 units
- On average: 0.3 × 50 = 15 units

For inventory planning:

Use forecast = 15 as average replenishment quantity
Check safety stock separately
Stock = (forecast × lead_time) + safety_stock

---

Safety Stock with Intermittent Demand

-- Calculate safety stock for high-variability demand
WITH demand_stats AS (
    SELECT
        STDDEV(CASE WHEN demand > 0 THEN demand ELSE NULL END) as stddev_nonzero,
        COUNT(CASE WHEN demand > 0 THEN 1 END)::FLOAT / COUNT(*) as prob_nonzero
    FROM spare_parts
)
SELECT
    2.33 * stddev_nonzero * SQRT(prob_nonzero) as safety_stock_95pct
FROM demand_stats;

---

Special Cases

Seasonal Intermittent Demand

-- Spare parts with seasonal pattern
SELECT * FROM TS_FORECAST(
    'seasonal_spare_parts',
    'date',
    'demand',
    'Croston',
    30,
    {'seasonal_period': 7}  -- Add seasonality
);

Trending Intermittent Demand

-- Demand gradually increasing
SELECT * FROM TS_FORECAST(
    'growing_spare_parts',
    'date',
    'demand',
    'TSB',  -- Handles trend + intermittency
    30,
    {}
);

---

Common Intermittent Demand Issues

Issue: Forecast is Always Zero

-- WRONG: Using AutoETS on sparse data
-- RIGHT: Use Croston, ADIDA, or TSB
SELECT * FROM TS_FORECAST(..., 'Croston', ...);

Issue: Forecast Intervals Are Too Wide

-- Normal for intermittent data
-- Reflects high uncertainty
-- Use for safety stock planning, not point estimates

Issue: Too Many/Too Few Non-Zero Forecasts

-- Croston generates "average" forecast
-- All periods get same forecast value
-- Normal behavior - reflects true demand distribution

---

Inventory Forecasting Workflow

1. Get historical demand
   ↓
2. Check sparsity (% zeros)
   ↓
3. Choose model:
   - 0-30% zeros: AutoETS
   - 30-70% zeros: Croston
   - 70%+ zeros: ADIDA
   ↓
4. Generate forecast
   ↓
5. Calculate replenishment quantity:
   Order = Forecast × Lead Time + Safety Stock
   ↓
6. Monitor actual vs. forecast
   ↓
7. Retrain monthly

---

Next Steps

1. Data Preparation — Handling sparse data
2. Production Deployment — Deploy to production
3. Reference: Models — Model details

---

Key Takeaways

• ✅ Intermittent demand ≠ regular demand (use special models)
• ✅ Croston: First choice for sparse data
• ✅ ADIDA: Better for complex intermittency
• ✅ TSB: For intermittent + trending
• ✅ Check sparsity level (% zeros) first
• ✅ Forecasts reflect average occurrence probability
• ✅ Use for replenishment quantity + safety stock
• ✅ Retrain frequently as demand patterns change