1.1 - 8.10 - NaiveBayesPredict_MLE Example - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

SQL Call

CREATE TABLE nb_iris_predict AS (
  SELECT * FROM NaiveBayesPredict_MLE (
    ON nb_iris_input_test PARTITION BY ANY
    ON nb_iris_model AS model DIMENSION
    USING
    IDColumn ('id')
    NumericInputs ('[1:4]')
    Responses ('virginica', 'setosa', 'versicolor')
    OutputProb('t')
    ) AS dt
) WITH DATA;

Output

This query returns the following table:

SELECT * FROM nb_iris_predict ORDER BY id;

The output provides a prediction for each row in the test data set and shows the log likelihood and corresponding probability values that were used to make the predictions for each category.

 id  prediction loglik_virginica    loglik_setosa        loglik_versicolor   prob_virginica         prob_setosa            prob_versicolor        
 --- ---------- ------------------- -------------------- ------------------- ---------------------- ---------------------- ---------------------- 
   5 setosa     -60.990734253263504   0.9404245089472999  -38.23198294967012 1.2695198379533435E-27                    1.0   9.71940317924901E-18
  10 setosa      -61.58619821439369 -0.17304369612032777  -37.66608359194052  2.131110046508006E-27                    1.0  5.211703059012073E-17
  15 setosa      -64.71695683575356    -3.55476298603102 -42.613273091194195  2.739029501478155E-27                    1.0 1.0891923690024204E-17
  20 setosa      -57.79928557069812   0.5317966964588929 -35.761305765776314 4.6465738923573095E-26     0.9999999999999998 1.7302380940689745E-16
  25 setosa      -55.09391552619634  -3.2370318008146173 -32.117985910900344 3.0119069413102237E-23     0.9999999999997136 2.8652399499869745E-13
  30 setosa     -58.067307546812884  0.10961171820769827  -34.92859901787489   5.42106967410162E-26     0.9999999999999993  6.068738461889238E-16
  35 setosa     -58.198028158940886   0.6602027864226648 -34.933599515507424  2.742805968486149E-26     0.9999999999999997   3.48183406795067E-16
  40 setosa      -58.35388608250933   0.9768407808212478 -35.442558011140925 1.7099866346922702E-26     0.9999999999999999 1.5249516367387455E-16
  45 setosa      -50.38476101165091   -4.369215787004387  -29.05374789421166 1.0368182360203043E-20      0.999999999980961  1.903899051337528E-11
  50 setosa     -59.474535549159874   1.0025797130664609 -36.502602214464886   5.43403661228925E-27                    1.0  5.148805022922067E-17
  55 versicolor  -5.221080105821829   -270.4654904532394 -1.7396368596493992    0.02984486347374556                    0.0     0.9701551365262544
  60 versicolor -11.335646463678362  -174.56549409208046  -2.319252419114942  1.2138837517511092E-4 1.5644258784333062E-75     0.9998786116248249
  65 versicolor -12.649647748539188  -138.43575851289683  -2.189800136664341  2.8663779342411874E-5  6.747138838325088E-60     0.9999713362206575
  70 versicolor -15.236844832532645  -152.47257400437167 -2.3538461309784644   2.540877047013271E-6  6.371823697392144E-66     0.9999974591229529
  75 versicolor  -8.346325661919643  -214.38367320704464 -1.1472748376909605   7.467367534737606E-4  2.467654883660264E-93     0.9992532632465262
  80 versicolor -18.455946846109452  -109.90097602090296  -3.727429862843837   4.013157212723945E-7  7.752015013752797E-47     0.9999995986842788
  85 versicolor  -7.002831886241749  -249.65653395348528 -2.0045561157689042   0.006704323455297395                    0.0     0.9932956765447026
  90 versicolor -12.027992226495456  -177.47037184770375 -1.7453975071096566   3.422244158442146E-5  4.826123690600991E-77     0.9999657775584156
  95 versicolor -10.180243946029357  -198.03717180240537 -1.1056729705329262  1.1452865426266493E-4  2.976370575042397E-86     0.9998854713457372
 100 versicolor -10.131539436020182  -187.29500562368133 -1.0288528197041062   1.113538068919299E-4 1.2752323133135041E-81     0.9998886461931081
 105 virginica  -1.5832164154434905   -540.5635710813433  -14.85964106733729     0.9999982855639519                    0.0   1.714436048134742E-6
 110 virginica   -6.113020624561468   -654.8021108771111  -28.83851492549933      0.999999999864966                    0.0  1.350340292044474E-10
 115 virginica  -3.6463518709972518  -456.64764580224687 -15.329878398979325     0.9999915684923585                    0.0   8.431507641564082E-6
 120 versicolor  -7.736150994583292  -322.90906957999925   -3.53629441822891   0.014776119478681243                    0.0     0.9852238805213187
 125 virginica  -1.8762709809140221   -509.8171593744312 -13.751542793349264     0.9999930396359074                    0.0   6.960364092651829E-6
 130 virginica   -3.369081146829314   -469.8029906143188  -9.138327814224688      0.996887608609285                    0.0   0.003112391390714933
 135 versicolor  -5.814829599870933  -403.67826879918823  -4.516448942741908    0.21443767585554682                    0.0     0.7855623241444533
 140 virginica  -1.4843093764923676  -463.61106264747457 -12.023861145012699     0.9999735321094436                    0.0  2.6467890556455832E-5
 145 virginica   -3.822666544684785   -576.3955856381103  -22.69421795258488     0.9999999936292677                    0.0   6.370732293282236E-9
 150 virginica  -2.5700464022369367  -366.50619806275694  -4.848872882106539      0.907108209897753                    0.0    0.09289179010224705

Prediction Accuracy

The following SQL code calculates and displays the prediction accuracy.

DROP TABLE nb_predict_accuracy;
CREATE MULTISET TABLE nb_predict_accuracy AS (
  SELECT nb_iris_input_test.id, species, prediction
  FROM nb_iris_predict, nb_iris_input_test
  WHERE nb_iris_input_test.id = nb_iris_predict.id
) WITH DATA;
SELECT (
 SELECT count(id) FROM nb_predict_accuracy
  WHERE prediction = species) / (1.00 * ( 
        SELECT count(id) FROM nb_predict_accuracy) )
 AS prediction_accuracy ;
 prediction_accuracy 
 ------------------- 
                0.93

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.