16.20 - GLMPredict Example: Gaussian Distribution Prediction - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ - NewSQL Engine Analytic Functions

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
July 2019
Content Type
Programming Reference
Publication ID
B035-1206-162K
Language
English (United States)

This example evaluates the predictions for new houses, comparing the original price information with root mean square error evaluation (RMSE).

Input

  • Input table: housing_test, as in DecisionForestPredict Example
  • Model: glm_housing_model, output by "GLM Example: Gaussian Distribution Analysis" in Teradata Vantage™ Machine Learning Engine Analytic Function Reference, B700-4003

SQL Call

The canonical link specifies the default family link, which is "identity" for the Gaussian distribution.

DROP TABLE glmpredict_housing;

CREATE MULTISET TABLE glmpredict_housing AS (
  SELECT * FROM GLMPredict (
    ON housing_test PARTITION BY ANY
    ON glm_housing_model AS Model DIMENSION
      ORDER BY attribute, category, predictor, estimate
    USING
    Accumulate ('sn', 'price')
    Family ('GAUSSIAN')
    LinkFunction ('CANONICAL')
  ) AS dt
) WITH DATA;

Output

This query returns the following table:

SELECT * FROM glmpredict_housing ORDER BY 1;
sn price fitted_value
13 27000 3.73458440000000E 004
16 37900 4.36871317500000E 004
25 42000 4.09020280000000E 004
38 67000 7.24876705000000E 004
53 68000 7.92386937000000E 004
104 132000 1.11528007000000E 005
111 43000 3.91028812000000E 004
117 93000 6.69369510000000E 004
132 44500 4.18198865000000E 004
140 43000 4.16117915000000E 004
142 40000 4.43941465000000E 004
157 60000 6.65712643500000E 004
161 63900 6.49009829000000E 004

The fitted_value column gives the predicted house price.

Root Mean Square Error Evaluation

This query returns the root mean square error evaluation (RMSE):
SELECT SQRT(AVG(POWER(glmpredict_housing.price - glmpredict_housing.fitted_value, 2))) AS RMSE FROM glmpredict_housing;
rmse
1.06854695738768E 004