GLMPredict Function Example | Teradata Vantage - 17.05 - GLMPredict Example: Gaussian Distribution Prediction - 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

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
    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