GLMPredict Function Example | Teradata Vantage - 17.05 - GLMPredict Example: Casting Input Column to VARCHAR - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K

Like GLMPredict Example: Logistic Distribution Prediction, this example predicts the admission status of students. In both examples, the input column masters is categorical—the value can be yes or no. In the other example, the value is 'yes' or 'no'. In this example, the value is numerical—1 for yes or 0 for no—therefore, it must be cast to VARCHAR.

Input

  • Input table: admissions_test_2, 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, with the category column modified as follows:
    attribute predictor category
    1 masters '1'
    2 masters '0'
admissions_test_2
id masters gpa stats programming admitted
50 1 3.95000000000000E 000 Beginner Beginner 0
51 1 3.76000000000000E 000 Beginner Beginner 0
52 0 3.70000000000000E 000 Novice Beginner 1
53 1 3.50000000000000E 000 Beginner Novice 1
54 1 3.50000000000000E 000 Beginner Advanced 1
55 0 3.60000000000000E 000 Beginner Advanced 1
56 0 3.82000000000000E 000 Advanced Advanced 1
57 0 3.71000000000000E 000 Advanced Advanced 1
58 0 3.13000000000000E 000 Advanced Advanced 1
59 0 3.65000000000000E 000 Novice Novice 1
60 0 4.00000000000000E 000 Advanced Novice 1
61 1 4.00000000000000E 000 Advanced Advanced 1
62 0 3.70000000000000E 000 Advanced Advanced 1
63 0 3.83000000000000E 000 Advanced Advanced 1
64 1 3.81000000000000E 000 Advanced Advanced 1
65 1 3.90000000000000E 000 Advanced Advanced 1
66 0 3.87000000000000E 000 Novice Beginner 1
67 1 3.46000000000000E 000 Novice Beginner 0
68 0 1.87000000000000E 000 Advanced Novice 1
69 0 3.96000000000000E 000 Advanced Advanced 1

SQL Call

CREATE MULTISET TABLE glmpredict_admissions_2 AS (
  SELECT * FROM GLMPredict (
    ON (
      SELECT id, CAST(masters AS varchar(10)) AS masters,
        gpa, stats, programming, admitted
      FROM 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_2 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.

glmpredict_admissions_2
id masters gpa stats programming admitted fitted value
50 1 3.95000000000000E 000 Beginner Beginner 0 3.50763408888030E-001
51 1 3.76000000000000E 000 Beginner Beginner 0 3.55708978581653E-001
52 0 3.70000000000000E 000 Novice Beginner 1 7.58306140231079E-001
53 1 3.50000000000000E 000 Beginner Novice 1 5.56012779663342E-001
54 1 3.50000000000000E 000 Beginner Advanced 1 7.69474352959112E-001
55 0 3.60000000000000E 000 Beginner Advanced 1 9.68031141480050E-001
56 0 3.82000000000000E 000 Advanced Advanced 1 9.45772725968165E-001
57 0 3.71000000000000E 000 Advanced Advanced 1 9.46411914806798E-001
58 0 3.13000000000000E 000 Advanced Advanced 1 9.49666186386367E-001
59 0 3.65000000000000E 000 Novice Novice 1 8.74189685344822E-001
60 0 4.00000000000000E 000 Advanced Novice 1 8.65058992199339E-001
61 1 4.00000000000000E 000 Advanced Advanced 1 6.50618727191735E-001
62 0 3.70000000000000E 000 Advanced Advanced 1 9.46469669158547E-001
63 0 3.83000000000000E 000 Advanced Advanced 1 9.45714262806374E-001
64 1 3.81000000000000E 000 Advanced Advanced 1 6.55523357798207E-001
65 1 3.90000000000000E 000 Advanced Advanced 1 6.53204164468356E-001
66 0 3.87000000000000E 000 Novice Beginner 1 7.54738501228429E-001
67 1 3.46000000000000E 000 Novice Beginner 0 2.60034297764359E-001
68 0 1.87000000000000E 000 Advanced Novice 1 8.90965518431337E-001
69 0 3.96000000000000E 000 Advanced Advanced 1 9.44948816395031E-001

Categorizing fitted_value Column

See GLMPredict Example: Logistic Distribution Prediction.