docs/mindsdb_sql/sql/get-single-prediction.mdx
The SELECT statement fetches predictions from the model table. The data is returned on the fly and the result set is not persisted.
But there are ways to save predictions data! You can save your predictions as a view using the CREATE VIEW statement. Please note that a view is a saved query and does not store data like a table. Another way is to create a table using the CREATE TABLE statement or insert your predictions into an existing table using the INSERT INTO statement.
Here is the syntax for fetching a single prediction from the model table:
SELECT target_name, target_name_explain
FROM mindsdb.predictor_name
WHERE column_name = value
AND column_name = value;
Here are some points to keep in mind while writing queries in MindsDB:
1. The `column_name = value` pairs may be joined by `AND` or `OR` keywords.
2. Do not use any quotations for numerical values.
3. Use single quotes for strings.
4. The tables and column names are case sensitive.
On execution, we get:
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| target_name | target_name_explain |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| predicted_value | {"predicted_value": 4394, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 4313, "confidence_upper_bound": 4475} |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
Where:
| Name | Description |
|---|---|
target_name | Name of the column to be predicted. |
target_name_explain | Object of the JSON type that contains the predicted_value and additional information such as confidence, anomaly, truth, confidence_lower_bound, confidence_upper_bound. |
predictor_name | Name of the model used to make the prediction. |
WHERE column_name = value AND ... | WHERE clause used to pass the data values for which the prediction is made. |
Let's predict the rental_price value using the home_rentals_model model for the property having sqft=823, location='good', neighborhood='downtown', and days_on_market=10.
SELECT sqft, location, neighborhood, days_on_market, rental_price, rental_price_explain
FROM mindsdb.home_rentals_model1
WHERE sqft=823
AND location='good'
AND neighborhood='downtown'
AND days_on_market=10;
On execution, we get:
+-------+----------+--------------+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| sqft | location | neighborhood | days_on_market | rental_price | rental_price_explain |
+-------+----------+--------------+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| 823 | good | downtown | 10 | 4394 | {"predicted_value": 4394, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 4313, "confidence_upper_bound": 4475} |
+-------+----------+--------------+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+