PREDICT (SQL)

A function that applies a specified trained model to predict the result for each input row provided.

Synopsis

PREDICT( model-name)

PREDICT( model-name USE  trained-model-name )

PREDICT( model-name WITH with-clause )

PREDICT( model-name USE  trained-model-name 
    WITH with-clause )

Arguments

model-name	The name of the model.
USE trained-model-name	Optional — The name of a non-default trained model. See details below.
WITH with-clause	Optional — Either: The specific columns to provide as input for your trained model. A list of arguments to make a prediction with. See details below.

Description

PREDICT returns the result of applying a trained machine learning model onto a specified query. This is performed on a row-by-row basis.

USE

If a trained model is not explicitly named by USE, PREDICT uses the default trained model for the specified model definition.

For example, if multiple models are trained:

CREATE MODEL MyModel PREDICTING( label ) FROM data
TRAIN MODEL MyModel AS FirstModel
TRAIN MODEL MyModel AS SecondModel NOT DEFAULT

FirstModel is the default model for MyModel. This means that PREDICT queries would use FirstModel for predictions. To specify use of SecondModel:

PREDICT( MyModel USE SecondModel)

WITH

When using a FROM clause to specify a dataset, the PREDICT function implicitly maps the feature columns of the specified dataset to those in the model. You can use a WITH clause to either:

Specify the mapping of columns between the dataset and your model. For example:
```
SELECT PREDICT(Trained_Model WITH age = year) FROM dataset
```
This query matches the age column from Trained_Model to the year column from dataset.
You can use braces to map multiple columns:
```
SELECT PREDICT(Trained_Model WITH {age = year, income = salary}) FROM dataset
```
The order of these columns in your WITH clause does not matter, and any missing column names are taken from the FROM clause.
Specify a list of arguments to make a prediction with. When using this form of WITH, you do not provide a FROM clause. For example:
```
SELECT PREDICT(Flower_Model WITH (5.1, 3.5, 1.4, 0.2, 'setosa'))
```
This query makes a prediction using Flower_Model on the expression (5.1, 3.5, 1.4, 0.2, 'setosa').
Arguments must be ordered exactly as specified in your CREATE MODEL statement. Missing arguments can be specified by empty commas. For example:
```
SELECT PREDICT(Flower_Model WITH (5.1, , 1.4, , 'setosa'))
```
You can use braces to provide multiple sets of arguments:
```
SELECT PREDICT(Flower_Model WITH ({5.1, 3.5, 1.4, 0.2, 'setosa'}, {6.4, 3.2, 4.3, 1.2, 'versicolor'}))
```

Required Security Privileges

Calling PREDICT requires %USE_MODEL privileges; otherwise, there is a SQLCODE –99 error (Privilege Violation). To assign %USE_MODEL privileges, use the GRANT command.

Examples

CREATE MODEL HousePriceModel PREDICTING( HousePrice ) FROM housing_data_2019
TRAIN MODEL HousePriceModel
SELECT * FROM housing_data_2020 WHERE PREDICT( HousePriceModel ) > 500000

CREATE MODEL PatientReadmission PREDICTING ( IsReadmitted ) FROM patient_data
TRAIN MODEL PatientReadmission
SELECT *, PREDICT( PatientReadmission ) FROM new_patient_data