GLMPredict Function Example | Teradata Vantage - GLMPredict Example: Logistic Distribution Prediction - Teradata® Database

This example predicts the admission status of students.

Input

• Input table: admissions_test, which has admissions information for 20 students
• Model: glm_admissions_model, output by "GLM Example: Logistic Regression Analysis with Intercept" in Teradata Vantage™ Machine Learning Engine Analytic Function Reference, B700-4003

SQL Call

CREATE MULTISET TABLE glmpredict_admissions AS (
  SELECT * FROM GLMPredict (
    ON admissions_test PARTITION BY ANY
    ON glm_admissions_model AS Model DIMENSION
    USING
    Accumulate ('id','masters','gpa','stats','programming','admitted')
    Family ('LOGISTIC')
    LinkFunction ('LOGIT')
  ) AS dt
) WITH DATA;

Output

This query returns the following table:

SELECT * FROM glmpredict_admissions ORDER BY 1;

Fitted values can vary in precision, because they depend on the model table output by ML Engine GLM function and fetched to Advanced SQL Engine.

Categorizing fitted_value Column

The fitted_value column gives the probability that a student belongs to one of the output classes. The following figure shows a typical logistic regression graph, mapping the input x-axis against a y probability value between [0,1].

A fitted_value probability greater than or equal to 0.5 implies class 1 (student admitted); a probability less than 0.5 implies class 0 (student rejected).

The following code adds a fitted_category column to glmpredict_admissions and populates it:

ALTER table glmpredict_admissions
  ADD fitted_category int;
  UPDATE glmpredict_admissions SET fitted_category = 1
    WHERE fitted_value > 0.4999;
  UPDATE glmpredict_admissions SET fitted_category = 0
    WHERE fitted_value < 0.4999;

This query returns the following table:

SELECT * FROM glmpredict_admissions ORDER BY 1;

Prediction Accuracy

This query returns the prediction accuracy:

SELECT (SELECT COUNT(id) FROM glmpredict_admissions
  WHERE admitted = fitted_category)/(SELECT count(id)
    FROM glmpredict_admissions) AS prediction_accuracy;