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": "samples.mindsdb.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
).
Was this page helpful?