Predictions for a multivariate time-series example.

[martynaslawinska]

Hello,
I want to predict the amount of sold items of a specific product by a specific shop. But I encounter some errors. Please see the details below.

---------PROBLEM DESCRIPTION---------
I created and trained my predictor successfully. But I get errors when making predictions.

---------DATA---------
I have a table with the following columns:

• sale_id,
• date_of_sale,
• shop (that made this sale),
• product_code (a product that was sold),
• amount (number of items that were sold; it is a column to be predicted),
• total (total sale price; this column is not relevant for making a forecast).

This table is used to create a predictor. I attach here the sales_data.csv file whose data I loaded into this table.

---------STEPS TO REPLICATE---------
Below are the steps that I performed:

1. I created the sales_data table in my database (PostgreSQL), and loaded the data from the sales_data.csv file into the sales_data table.

CREATE TABLE public.sales_data (
	sale_id int,
	date_of_sale date,
	shop text,
	product_code text,
	amount int,
	total money
);
COPY sales_data(sale_id, date_of_sale, shop, product_code, amount, total)
FROM '...\sales_data.csv'
DELIMITER ',' --or DELIMITER ';'
CSV HEADER;

2. I run a query to create a predictor:

CREATE PREDICTOR stock_forecaster
FROM postgres_db
(SELECT shop, product_code, amount, date_of_sale
FROM sales_data)
PREDICT amount
ORDER BY date_of_sale
GROUP BY shop, product_code
WINDOW 10
HORIZON 7;

My predictor was created and trained without any errors/warnings.

3. I run a query to predict the sold amount for specific data:

SELECT amount FROM stock_forecaster
WHERE when_data='{"date_of_sale": "2005-02-03", "shop": "The Everything Store", "product_code": "S10_1678"}';

This query returned an error:

SQL Error [1149] [42000]: Cannot transform. Missing historical values for target column amount (use_previous_target is set to True).

4. I run another query to make predictions for many rows at once:

SELECT tb.shop, tb.product_code, tb.amount as predicted_amount
FROM postgres_db.sales_data AS ta
JOIN mindsdb.stock_forecaster AS tb
WHERE ta.shop = "Easy Store"
AND ta.product_code="S10_1949"
AND ta.date_of_sale>LATEST
LIMIT 7;

This query returned an error:

SQL Error [1149] [42000]: 'group_by_columns'.

---

Please advise how I can fix the errors in steps 3 and 4.
Thank you.

[ZoranPandovski]

Hello @martynaslawinska this is an issue that should be resolved in the next release. The plan is to do that today. I will ping you once we release

[ZoranPandovski]

Can you please share the error? Are you using mindsdb cloud?

[martynaslawinska]

Yes. I have a connection from DBeaver to my MindsDB cloud account.
Please see the attachment where I included some screenshots.
mindsdb_github1902.pdf

[ZoranPandovski]

Thanks. I think the issue is related to multiple columns in the GROUP BY, which we don't support atm. Can you please try with just one column when training predictor as GROUP BY shop?

[martynaslawinska]

Yes. Please see the attachment. The first query returns a different error.

[martynaslawinska]

Here, I removed all the product_code columns from the SQL code, and it seems I get the same error for the first query as it was initially.