CONFUSION_MATRIX

Using an input table, returns a confusion matrix based on observed and predicted values.

You cannot pass any inputs to the OVER() clause.

Important: Before using a machine learning function, be aware that all the ongoing transactions might be committed.

Syntax

CONFUSION_MATRIX ( target, predictions
                   [ USING PARAMETERS [num_classes=C] ])
                        OVER()

Arguments

target

The column in the input table containing the response variable. Must be an integer.

predictions

The column in the input table containing the prediction variables. Must be an integer.

Parameters

num_classes=C

(Optional) The number of classes you want to pass to the function. Must be a positive integer.

The result of CONFUSION_MATRIX is a table with C+1 columns and C rows.

Examples

This example shows how you can execute the CONFUSION_MATRIX function on an input table named mtcars. The response variables appear in the column obs, while the prediction variables appear in the column pred. Because this problem regards classification, both the response variable values and the prediction variable values are either 0 or 1, indicating binary classification.

In the table returned, all 12 cars with a value of 1 in the am column were correctly predicted by PREDICT_LOGISTIC_REG as having a value of 1. Out of the 20 cars that had a value of 0 in the am column, 19 were correctly predicted to have the value 0. One car was incorrectly classified as having the value 1.

=> SELECT CONFUSION_MATRIX(obs, pred USING PARAMETERS num_classes=2) OVER()
	FROM (SELECT am AS obs, PREDICT_LOGISTIC_REG(mpg, cyl, disp, hp, drat, wt, qsec, vs, gear, carb
             USING PARAMETERS model_name='mtcars_log')::INT AS pred
             FROM mtcars) AS prediction_output;
 class | 0  | 1  |                   comment
-------+----+----+---------------------------------------------
     0 | 19 |  0 |
     1 |  1 | 12 | Of 32 rows, 32 were used and 0 were ignored
(2 rows)