SELECT
statement¶
Description¶
The SELECT
statement is used to get a predictions from the model table. The data is not persistent and is returned on the fly as a result-set. The basic syntax for selecting from the model is:
Syntax¶
SELECT [target_variable], [target_variable]_explain
FROM mindsdb.[predictor_name]
WHERE [column]=[value]
AND [column]=[value];
Grammar matters
Ensure that there are no spaces between the column name, equal sign and value. Ensure to not use any quotations for numerical values and singular quotes for strings
On execution, you should get:
+----------+----------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| [column] | [column] | [target_variable] | [target_variable]_explain |
+----------+----------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| [value] | [value] | [predicted_value] | {"predicted_value": 4394, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 4313, "confidence_upper_bound": 4475} |
+----------+----------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
Where:
Expressions | Description |
---|---|
[target_variable] |
Name of the column to be predicted |
[target_variable] _explain |
JSON object that contains additional information as confidence_lower_bound , confidence_upper_bound , anomaly , truth . |
[predictor_name] |
Name of the model to be used to make the prediction |
WHERE [column]=[value] AND ... |
WHERE clause used to pass the input data to make the prediction |
Example¶
The following SQL statement selects a rental_price
prediction from the home_rentals_model
for a property that has the attributes named after the WHERE
expression:
SELECT 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,
+----------+--------------+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| location | neighborhood | days_on_market | rental_price | rental_price_explain |
+----------+--------------+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| good | downtown | 10 | 4394 | {"predicted_value": 4394, "confidence": 0.99, "anomaly": null, "truth": null, "confidence_lower_bound": 4313, "confidence_upper_bound": 4475} |
+----------+--------------+----------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
Bulk predictions
You can also make bulk predictions by joining a table with your model:
SELECT t.rental_price as real_price,
m.rental_price as predicted_price,
t.sqft, t.location, t.days_on_market
FROM example_db.demo_data.home_rentals as t
JOIN mindsdb.home_rentals_model as m limit 100