ROC Example: Use Input from Predictor Function | Teradata Vantage - ROC Example: Use Input from Predictor Function

ROC Example: Use Input from Predictor Function | Teradata Vantage - ROC Example: Use Input from Predictor Function - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product

Teradata Vantage

Release Number

9.02

9.01

2.0

1.3

Published

February 2022

Language

English (United States)

Last Update

2022-02-10

dita:mapPath

rnn1580259159235.ditamap

dita:ditavalPath

ybt1582220416951.ditaval

dita:id

B700-4003

lifecycle

Product Category

Teradata Vantage™

Input

glmpredict_admissions, created as follows:

CREATE MULTISET TABLE glmpredict_admissions AS (
    SELECT * FROM GLMPredict_MLE (
    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')
    OutputProb ('t')
    Responses ('1')
  )  AS dt
) WITH DATA;

The preceding call is the same as in GLMPredict_MLE Example: Logistic Distribution Prediction, except for the addition of Responses ('1').

SELECT * FROM glmpredict_admissions;

id  masters                    gpa  stats     programming  admitted            fitted_value   prediction                  prob_1
-----------  ---------------------  --------  -----------  --------  ----------------------  -----------  ----------------------
61  yes      4.00000000000000E 000  Advanced  Advanced            1   6.50620999077464E-001            1   6.50620999077464E-001
51  yes      3.75999999046326E 000  Beginner  Beginner            0   3.55711267427044E-001            0   3.55711267427044E-001
57  no       3.71000003814697E 000  Advanced  Advanced            1   9.46412427155178E-001            1   9.46412427155178E-001
59  no       3.65000009536743E 000  Novice    Novice              1   8.74190793835473E-001            1   8.74190793835473E-001
60  no       4.00000000000000E 000  Advanced  Novice              1   8.65060169870818E-001            1   8.65060169870818E-001
68  no       1.87000000476837E 000  Advanced  Novice              1   8.90966498700309E-001            1   8.90966498700309E-001
55  no       3.59999990463257E 000  Beginner  Advanced            1   9.68031454705773E-001            1   9.68031454705773E-001
53  yes      3.50000000000000E 000  Beginner  Novice              1   5.56015243694066E-001            1   5.56015243694066E-001
58  no       3.13000011444092E 000  Advanced  Advanced            1   9.49666668893439E-001            1   9.49666668893439E-001
66  no       3.86999988555908E 000  Novice    Beginner            1   7.54740372192837E-001            1   7.54740372192837E-001
62  no       3.70000004768372E 000  Advanced  Advanced            1   9.46470180931532E-001            1   9.46470180931532E-001
69  no       3.96000003814697E 000  Advanced  Advanced            1   9.44949341902683E-001            1   9.44949341902683E-001
56  no       3.81999993324280E 000  Advanced  Advanced            1   9.45773244683836E-001            1   9.45773244683836E-001
64  yes      3.80999994277954E 000  Advanced  Advanced            1   6.55525616200381E-001            1   6.55525616200381E-001
54  yes      3.50000000000000E 000  Beginner  Advanced            1   7.69476126601993E-001            1   7.69476126601993E-001
65  yes      3.90000009536743E 000  Advanced  Advanced            1   6.53206426068888E-001            1   6.53206426068888E-001
52  no       3.70000004768372E 000  Novice    Beginner            1   7.58307989347034E-001            1   7.58307989347034E-001
63  no       3.82999992370605E 000  Advanced  Advanced            1   9.45714782104350E-001            1   9.45714782104350E-001
50  yes      3.95000004768372E 000  Beginner  Beginner            0   3.50765681699298E-001            0   3.50765681699298E-001
67  yes      3.46000003814697E 000  Novice    Beginner            0   2.60036217847503E-001            0   2.60036217847503E-001

SQL Call

SELECT * FROM ROC (
  ON glmpredict_admissions AS InputTable
  OUT TABLE ROCTable (roc_out_4)
  USING
  ProbabilityColumn ('prob_1')
  ObservationColumn ('admitted')
  PositiveClass ('1')
  NumThresholds (100)
) AS dt;

Output

Onscreen:

model_id                     auc                    gini
--------  ----------------------  ----------------------
       1   1.00000000000000E 000   1.00000000000000E 000

SELECT * FROM roc_out_4;

model_id               threshold                     tpr                     fpr
--------  ----------------------  ----------------------  ----------------------
       1   0.00000000000000E 000   1.00000000000000E 000   1.00000000000000E 000
       1   1.01010101010101E-002   1.00000000000000E 000   1.00000000000000E 000
       1   2.02020202020202E-002   1.00000000000000E 000   1.00000000000000E 000
       ...
       1   9.09090909090909E-002   1.00000000000000E 000   1.00000000000000E 000
       1   1.01010101010101E-001   1.00000000000000E 000   1.00000000000000E 000
       1   1.11111111111111E-001   1.00000000000000E 000   1.00000000000000E 000
       1   1.21212121212121E-001   1.00000000000000E 000   1.00000000000000E 000
       ...
       1   1.91919191919192E-001   1.00000000000000E 000   1.00000000000000E 000
       1   2.02020202020202E-001   1.00000000000000E 000   1.00000000000000E 000
       1   2.12121212121212E-001   1.00000000000000E 000   1.00000000000000E 000
       1   2.22222222222222E-001   1.00000000000000E 000   1.00000000000000E 000
       ...
       1   2.92929292929293E-001   1.00000000000000E 000   6.66666666666667E-001
       1   3.03030303030303E-001   1.00000000000000E 000   6.66666666666667E-001
       1   3.13131313131313E-001   1.00000000000000E 000   6.66666666666667E-001
       1   3.23232323232323E-001   1.00000000000000E 000   6.66666666666667E-001
       ...
       1   3.93939393939394E-001   1.00000000000000E 000   0.00000000000000E 000
       1   4.04040404040404E-001   1.00000000000000E 000   0.00000000000000E 000
       1   4.14141414141414E-001   1.00000000000000E 000   0.00000000000000E 000
       1   4.24242424242424E-001   1.00000000000000E 000   0.00000000000000E 000
       ...
       1   4.94949494949495E-001   1.00000000000000E 000   0.00000000000000E 000
       1   5.05050505050505E-001   1.00000000000000E 000   0.00000000000000E 000
       1   5.15151515151515E-001   1.00000000000000E 000   0.00000000000000E 000
       1   5.25252525252525E-001   1.00000000000000E 000   0.00000000000000E 000
       ...
       1   5.95959595959596E-001   9.41176470588235E-001   0.00000000000000E 000
       1   6.06060606060606E-001   9.41176470588235E-001   0.00000000000000E 000
       1   6.16161616161616E-001   9.41176470588235E-001   0.00000000000000E 000
       1   6.26262626262626E-001   9.41176470588235E-001   0.00000000000000E 000
       ...
       1   6.96969696969697E-001   7.64705882352941E-001   0.00000000000000E 000
       1   7.07070707070707E-001   7.64705882352941E-001   0.00000000000000E 000
       1   7.17171717171717E-001   7.64705882352941E-001   0.00000000000000E 000
       1   7.27272727272727E-001   7.64705882352941E-001   0.00000000000000E 000
       ...
       1   7.97979797979798E-001   5.88235294117647E-001   0.00000000000000E 000
       1   8.08080808080808E-001   5.88235294117647E-001   0.00000000000000E 000
       1   8.18181818181818E-001   5.88235294117647E-001   0.00000000000000E 000
       1   8.28282828282828E-001   5.88235294117647E-001   0.00000000000000E 000
       ...
       1   8.98989898989899E-001   4.11764705882353E-001   0.00000000000000E 000
       1   9.09090909090909E-001   4.11764705882353E-001   0.00000000000000E 000
       1   9.19191919191919E-001   4.11764705882353E-001   0.00000000000000E 000
       1   9.29292929292929E-001   4.11764705882353E-001   0.00000000000000E 000
       ...
       1   9.89898989898990E-001   0.00000000000000E 000   0.00000000000000E 000
       1   1.00000000000000E 000   0.00000000000000E 000   0.00000000000000E 000

Download a zip file of all examples and a SQL script file that creates their input tables.