Forecast Monthly Expenditures with Nixtla's StatsForecast and MindsDB

In this tutorial, we’ll create a model to forecast expenditures based on historical data using the Nixtla’s StatsForecast engine.

Connect a database

We use a table from our MySQL public demo database, so let’s start by connecting it to MindsDB.

CREATE DATABASE mysql_historical
WITH ENGINE = 'mysql',
PARAMETERS = {
    "user": "user",
    "password": "MindsDBUser123!",
    "host": "db-demo-data.cwoyhfn6bzs0.us-east-1.rds.amazonaws.com",
    "port": "3306",
    "database": "public"
};

Now that we’ve connected our database to MindsDB, let’s query the data to be used in the example:

SELECT *
FROM mysql_historical.historical_expenditures
LIMIT 3;

Deploy a time-series model

Please note that before using the StatsForecast engine, you should create it with the below command:

CREATE ML_ENGINE statsforecast
FROM statsforecast;

You can check the available engines with this command:

SHOW ML_ENGINES;

Let’s create a model table to forecast the expenditures:

CREATE MODEL quarterly_expenditure_forecaster
FROM mysql_historical
  (SELECT * FROM historical_expenditures)
PREDICT expenditure
ORDER BY month
GROUP BY category
WINDOW 12
HORIZON 3
USING ENGINE = 'statsforecast';

We can check the training status with the following query:

DESCRIBE quarterly_expenditure_forecaster;

Make predictions

Once the model status is complete, the behavior is the same as with any other AI table – you can query for batch predictions by joining it with a data table.

SELECT m.month as month, m.expenditure as forecasted
FROM mindsdb.quarterly_expenditure_forecaster as m
JOIN mysql_historical.historical_expenditures as t
WHERE t.month > LATEST
AND t.category = 'food';

The historical_expenditures table is used to make batch predictions. Upon joining the quarterly_expenditure_forecaster model with the historical_expenditures table, we get predictions for the next quarter as defined by the HORIZON 3 clause.

MindsDB provides the LATEST keyword that marks the latest training data point. In the WHERE clause, we specify the month > LATEST condition to ensure the predictions are made for data after the latest training data point.

If we train the model using data from January 2020 until December 2020 (as defined by WINDOW 12), then the predictions come for the first quarter of 2021 (as defined by HORIZON 3).