Predict Store Sales Data With MindsDB using Machine Learning and SQL

Kheff — Thu, 13 Oct 2022 16:07:27 +0000

## Introduction
MindsDB is the next step we beginners can take towards learning data science and machine learning. MindsDB has embedded their software with AI and Machine Learning in order to assist us with recognizing the patterns of data, predict trends and train machine learning model in minutes.

In this tutorial, We will explore how we can use MindsDB Cloud to train a Time Series Model and predict the Store Sales Data. You can download the dataset which we will use in this tutorial here (The file name is Store 632.csv).

Before We Start!!

If you don't have a MindsDB Cloud yet, you can create a free trial account here.

Once you're logged in, you will be directed to MindsDB Cloud Editor.

Adding Data to the Cloud

Step 1: On the top left corner of your screen, you will find 2 buttons 'Query' and 'Add Data'. Click the Add Data in order to import data into your MindsDB Cloud Editor

Step 2: In the Datasource page select Files and choose Import File.

Step 3: Select the file you want to import and give a Table name. Once done, click Save and Continue.

Step 4: Once the file is uploaded, you will see 2 queries in the MindsDB Cloud Editor and execute them to see if the is created with proper data.

Training a Predictor Model

Training a Predictor Model has never been easier, with MindsDB you just need basic SQL knowledge to get the job done.

Step 1: Use CREATE PREDICTOR syntax, and specify what query to train FROM and what to PREDICT. Below is a template.

CREATE PREDICTOR mindsdb.[predictor_name]
FROM [integration_name]
    (SELECT [sequential_column], [partition_column], [other_column], [target_column] FROM [table_name])
PREDICT [target_column]

ORDER BY [sequential_column]
GROUP BY [partition_column]

WINDOW [int]
HORIZON [int]

Now let plugin the parameters into the query and execute it. After execution, we will get a message whether it was successful or not.

CREATE PREDICTOR mindsdb.nest
FROM files.stores_sales
    (SELECT * FROM files.store_sales)
PREDICT `Dollar Sales`
ORDER BY Date
GROUP BY SKU
WINDOW 20
HORIZON 7;

Step 2: If your dataset is large, the Predictor may take a while to train. While it is training, you can check it's status by running this query.

SELECT * 
FROM mindsdb.predictors
WHERE name='[predictor_name]'

Making Predictions

We can make predictions by simply treating it as a query.

SELECT `Dollar Sales`,
        `Dollar Sales_explain`
From mindsdb.nexte
WHERE `Unit Sales` = 2;

You can also make a bulk prediction by join the table with your model.

SELECT t.`Dollar Sales` as real_p,
        m.`Dollar Sales` as predicted_p,
        t.`Unit Sales`, t.Date, t.SKU
FROM files.store_sales as t
JOIN mindsdb.example_model as m limit 100;

Conclusion

Congratulation, you have just trained your own Time Series Predictive Model. To recap our tutorial, we setup our own MindsDB Cloud account, uploaded our datasets, created and trained our predictive model and make predictions with our uploaded data.

Now, I would recommend you to test out other datasets with MindsDB and improve your machine learning and data science skill.''

Remember, Always to have fun coding!