Skip to content

SELECT statement

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:

SELECT target_variable, target_variable_explain FROM model_table 
                                                WHERE when_data='{"column3": "value", "column2": "value"}';

Model table columns

The below list contains the column names of the model table. Note that target_variable_ will be the name of the target variable column.

  • target_variable_original - The original value of the target variable.
  • target_variable_min - Lower bound of the predicted value.
  • target_variable_max - Upper bound of the predicted value.
  • target_variable_confidence - Model confidence score.
  • target_variable_explain - JSON object that contains additional information as confidence_lower_bound, confidence_upper_bound, anomaly, truth.
  • when_data - The data to make the predictions from(WHERE clause params).
  • select_data_query - SQL select query to create the datasource.
  • external_datasource - Name of the pre-existing datasource that the model was built from.
rental_price number_of_rooms number_of_bathrooms sqft location days_on_market initial_price neighborhood rental_price_original rental_price_min rental_price_max rental_price_confidence rental_price_explain when_data select_data_query external_datasource
2450 4 2 800 good 12 2222 downtown nan 2407 2493 0.99 {"predicted_value": 2450, "confidence": 0.99, "confidence_lower_bound": 2407, "confidence_upper_bound": 2493, "anomaly": null, "truth": null} {"sqft": 800, "number_of_rooms": 4, "number_of_bathrooms": 2, "location": "good", "days_on_market" : 12, "neighborhood": "downtown", "initial_price": "2222"} nan nan

SELECT example

The following SQL statement selects all information from the home_rentals_model for the property that has "sqft": 800, "number_of_rooms": 4, "number_of_bathrooms": 2, "location": "good", "days_on_market" : 12, "neighborhood": "downtown", "initial_price": "2222".

SELECT * FROM mindsdb.home_rentals_model 
WHERE when_data='{"sqft": 800, "number_of_rooms": 4, "number_of_bathrooms": 2,
                  "location": "good", "days_on_market" : 12, 
                  "neighborhood": "downtown", "initial_price": "2222"}';

SELECT model_name

The following SQL statement selects only the target variable rental_price as price and the home_rentals_model confidence as accuracy:

SELECT rental_price as price, 
rental_price_confidence as confidence 
FROM mindsdb.home_rentals_model WHERE when_data='{"sqft": 800, "number_of_rooms": 4, "number_of_bathrooms": 2, 
                                            "location": "good", "days_on_market" : 12,  
                                            "neighborhood": "downtown", "initial_price": "2222"}';

SELECT model_name