5.4.5 - Linear Regression - Teradata Warehouse Miner

In-Database Analytic Functions User Guide

prodname
Teradata Warehouse Miner
vrm_release
5.4.5
created_date
February 2018
category
User Guide
featnum
B035-2306-028K

Purpose

Linear Regression is one of the most fundamental types of predictive modeling algorithms. In linear regression, a dependent numeric variable is expressed in terms of the sum of one or more independent numeric variables, which are each multiplied by a numeric coefficient, usually with a constant term added to the sum of independent variables. It is the coefficients of the independent variables together with a constant term that comprise a linear regression model. Applying these coefficients to the variables (columns) of each observation (row) in a data set (table) is known as scoring, described later in this chapter.

The Linear Regression chapter in Teradata Warehouse Miner User Guide, Volume 3—Analytic Functions, B035-2302, contains a description of the linear regression algorithm included in Teradata Warehouse Miner. The linear regression algorithm is also available as a stand-alone external stored procedure that can be executed directly in the Teradata database, independently of Teradata Warehouse Miner. It is the stand-alone version and its parameters that are described in this document. Some of the key features of this stand-alone version of linear regression are outlined below.
  • The Teradata supplied table operator CALCMATRIX is used to build a table that represents an extended cross-products matrix that is the input to the algorithm
  • One or more group by columns may optionally be specified so that an input matrix is built for each combination of group by column values, and subsequently a separate linear model is built for each matrix.

    To achieve this, the names of the group by columns are passed to CALCMATRIX as parameters so that it includes them as columns in the matrix table it creates.

  • The algorithm is partially scalable because the size of each input matrix depends only on the number of independent variables (columns) and not on the size of the input table. The calculations performed on the client workstation however are not scalable when group by columns are used, because each model is built serially based on each matrix in the matrix table.
  • Teradata Release 14.10 or later is required by this algorithm due to the use of the CALCMATRIX table operator, first available in that release.

To execute the stand-alone version of the linear regression algorithm or to score a model built by this algorithm the td_analyze stored procedure must be installed on the Teradata system, with appropriate permissions granted. Refer to In-Database Analytic Function Setup for instructions on how to install td_analyze.

Syntax

call twm.td_analyze('linear','database=twm_database;tablename=twm_tablename;columns=c1,c2;dependent=c0;groupby=c3,c4;outputdatabase=twm_out_db;outputtablename=twm_out_tbl;constant=true; ');

Required Parameters

columns
The input columns representing independent variables. The columns must reside in the table named with the tablename parameter, residing in the database named with the database parameter.

For example: columns=column1,column2,column3

database
The database containing the input table.
dependent

The name of the column that represents the dependent variable.

tablename
The input table to build a predictive model from.

Optional Parameters

constant
Set to true if the linear model includes a constant term or false otherwise. The default value is true.
groupby
The input columns dividing the input table into partitions, one for each combination of values in the group by columns. For each partition or combination of values a separate linear model is built. The columns must reside in the table named with the tablename parameter. The default case is no group by columns.

For example: groupby=column1,column2,column3

outputdatabase
The database that contains the resulting output table that represents one or more linear models.
If outputdatabase and outputtablename are not both specified, a volatile output table with randomly generated name is created in the logon user database.
outputtablename
The name of the output table representing one or more linear models. A second output table reporting statistical measures is automatically named on the user’s behalf by appending “_rpt” to the end of this name. These two output tables represent a single linear model with coefficients and statistical measures in the absence of group by columns, or if group by columns are specified, there is a model for each combination of group bycolumn values in these output tables.

Note that both of the output tables must first be dropped by the user if outputdatabase and outputtablename are both specified. If outputdatabase and outputtablename are not both specified, volatile output tables with randomly generated names are created in the logon user database, and the two output result sets are returned to the user instead.

overwrite

When overwrite is set to true (default), the output tables are dropped before creating new ones.

Examples

Examples in this section demonstrate the use of Linear Regression, first without and then with group by parameters. To execute the provided examples, the td_analyze function must be installed in a database called twm and the Teradata Warehouse Miner tutorial data must be installed in the twm_source database.

If these examples are executed, do not introduce extra spaces between parameters when copying.

In this example, input columns age, years_with_bank, and nbr_children are used to predict income. No permanent output tables are created, just two result data sets that are returned to the user, one with coefficients and statistics, the other with statistical measures.

call twm.td_analyze('linear','database=twm_source;tablename=twm_customer;columns=age,years_with_bank,nbr_children;dependent=income');

In this example, two permanent output tables are created, one with coefficients and statistics, the other with statistical measures.

call twm.td_analyze('linear','database=twm_source;tablename=twm_customer;columns=age,years_with_bank,nbr_children;dependent=income;outputdatabase=twm_results;outputtablename=twm_linear2');

In this example, group by columns gender and marital_status result in 2x4=8 models being built.

call twm.td_analyze('linear','database=twm_source;tablename=twm_customer;columns=age,years_with_bank,nbr_children;dependent=income;outputdatabase=twm_results;outputtablename=twm_linear3;groupby=gender,marital_status');