3.10.8 ML_SCORE

ML_SCORE scores a model by generating predictions using the feature columns in a labeled dataset as input and comparing the predictions to ground truth values in the target column of the labeled dataset. The dataset used with ML_SCORE should have the same feature columns as the dataset used to train the model but the data should be different; for example, you might reserve 20 to 30 percent of your labeled training data for scoring.

ML_SCORE returns a computed metric indicating the quality of the model.

ML_SCORE Syntax

CALL sys.ML_SCORE(table_name, target_column_name, model_handle, metric, score);

ML_SCORE parameters:

  • table_name: Specifies the fully qualified name of the table used to compute model quality (schema_name.table_name). The table must contain the same columns as the training dataset.

  • target_column_name: Specifies the name of the target column containing ground truth values.

  • model_handle: Specifies the model handle or a session variable containing the model handle.

  • metric: Specifies the name of the classification, regression, or forecasting type metric. Commonly used classification metrics are accuracy, balanced_accuracy, precision, and recall. Commonly used regression metrics are neg_mean_squared_error, neg_absolute_squared_error, and r2. For more information about scoring metrics, refer to scikit-learn.org. For more information about forecasting metrics, refer to sktime.org and statsmodels.org.

    • classification metrics:

      • accuracy: Computes the fraction of labels a model predicts correctly

      • balanced_accuracy: Computes the balanced accuracy for imbalanced datasets

      • precision: Computes precision

      • recall: Computes the recall

      • f1: Computes the F1 score

      • f1_micro: Computes the F1 score (micro-averaged)

      • f1_macro: Computes the F1 score (macro-averaged)

      • f1_weighted: Computes the F1 score (weighted)

      • f1_samples: Computes the F1 score (by multilabel sample)

      • recall_micro: Computes the recall (micro-averaged)

      • recall_macro: Computes the recall (macro-averaged)

      • recall_weighted: Computes the recall (weighted)

      • recall_samples: Computes the recall (by multilabel sample)

      • precision_micro: Computes precision (micro-averaged)

      • precision_macro: Computes precision (macro-averaged)

      • precision_weighted: Computes precision (weighted)

      • precision_samples: Computes precision (by multilabel sample)

    • regression metrics:

      • neg_mean_squared_error: Computes mean squared error regression loss.

      • neg_mean_absolute_error: Computes mean absolute error regression loss.

      • r2: Computes the R2 (coefficient of determination) regression score function.

      • neg_mean_squared_log_error: Computes mean squared logarithmic error regression loss.

      • neg_median_absolute_error: Computes median absolute error regression loss.

    • forecasting metrics:

      • neg_sym_mean_abs_percent_error: Computes the symmetric Mean Absolute Percentage Error (sMAPE) loss.

      • neg_root_mean_squared_percent_error: Compute the Root Mean Squared Percentage Error (RMSPE) loss.

      • neg_mean_abs_scaled_error: Computes the Mean Absolute Scaled Error (MASE) loss.

      • neg_root_mean_squared_error: Computes the Root Mean Squared Error (RMSE) loss.

      • neg_mean_squared_error: Computes the Mean Squared Error (MSE) loss.

      • neg_max_absolute_error: Computes the Maximum Absolute Error loss.

      • neg_mean_absolute_error: Computes the Mean Absolute Error (MAE) loss.

  • score: Specifies the user-defined variable name for the computed score. The ML_SCORE routine populates the variable. User variables are written as @var_name. The examples in this guide use @score as the variable name. Any valid name for a user-defined variable is permitted (e.g., @my_score).

Syntax Example

The following example runs ML_SCORE on the ml_data.iris_train table to determine model quality:

mysql> CALL sys.ML_SCORE('ml_data.iris_validate', 'class', @iris_model, 
'balanced_accuracy', @score);

mysql> SELECT @score;
+--------------------+
| @score             |
+--------------------+
| 0.9583333134651184 |
+--------------------+