TD_MULTIVAR_REGR Function Examples | Teradata Vantage - TD_MULTIVAR_REGR Examples - Teradata Vantage

Database Unbounded Array Framework Time Series Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
ncd1634149624743.ditamap
dita:ditavalPath
ruu1634160136230.ditaval
dita:id
ncd1634149624743
lifecycle
latest

TD_MULTIVAR_REGR Input Table HOUSE_VALUES

TIMECODE CITYID HOUSE_VAL SALARY MORTGAGE
2020-09-01 08:00:00 33 195000.0 72000.0 0.049
2020-09-01 08:00:00 33 120000.0 39000.0 0.03
2020-01-01 08:00:00 33 144000.0 74000.0 0.034
2020-08-01 08:00:00 33 184000.0 49000.0 0.03
2020-05-01 08:00:00 33 320000.0 112000.0 0.017
... ... ... ... ...

Example: TD_MULTIVAR_REGR Call

EXECUTE FUNCTION INTO VOLATILE ART(MULTIVAR_RESULTS)
TD_MULTIVAR_REGR(
   SERIES_SPEC(
      TABLE_NAME(HOUSE_VALUES),
      ROW_AXIS(TIMECODE(TD_TIMECODE)),
      SERIES_ID(CITYID),
      PAYLOAD( FIELDS(HOUSE_VAL, SALARY, MORTGAGE), 
      CONTENT(MULTIVAR_REAL)) ),
   FUNC_PARAMS( 
      VARIABLES_COUNT(3), 
      WEIGHTS(0), 
      FORMULA('Y = B0 + B1*X1 + B2*X2'),
      ALGORITHM('QR'), 
      COEFF_STATS(1), 
      MODEL_STATS(1),
      RESIDUALS(1) ) );

TD_MULTIVAR_REGR Primary Result

Display the primary RETURNS TABLE with this statement:

SELECT * FROM MULTIVAR_RESULTS;
CITYID 33
ROW_I 0
COEFF_NAME B0
COEFF_VALUE 8.18270797024760E 004
STD_ERROR 4.95935306610341E 004
TSTAT_VALUE 1.64995471408871E 000
TSTAT_PROB 1.33350347896138E-001
SIGNIF_RATING 0.1 to 1.0
CONF_INT_LOW -9.08346272834929E 003
CONF_INT_HIGH 1.72737622133301E 005

CITYID 33
ROW_I 1
COEFF_NAME B1
COEFF_VALUE 1.98013039612643E 000
STD_ERROR 4.93261793541858E-001
TSTAT_VALUE 4.01435996473220E 000
TSTAT_PROB 3.04381074980674E-003
SIGNIF_RATING 0.001 to 0.01
CONF_INT_LOW 1.07592582319964E 000
CONF_INT_HIGH 2.88433496905322E 000

CITYID 33
ROW_I 2
COEFF_NAME B2
COEFF_VALUE -1.01032988754990E 006
STD_ERROR 1.06299250300384E 006
TSTAT_VALUE -9.50458149699901E-001
TSTAT_PROB 3.66703526705887E-001
SIGNIF_RATING 0.1 to 1.0
CONF_INT_LOW -2.95891519212286E 006
CONF_INT_HIGH 9.38255417023066E 005

TD_MULTIVAR_REGR Secondary Result

Display the secondary RETURNS TABLE with this function call:

EXECUTE FUNCTION
TD_EXTRACT_RESULTS(ART_SPEC( TABLE_NAME(MULTIVAR_RESULTS), LAYER(ARTFITMETADATA)));
CITYID 33
ROW_I 0
STD_ERROR2 4.54969692841879E 004
STD_ERROR_DF 9
MULTIPLE_R_SQUARED 6.43223516138600E-001
ADJUSTED_R_SQUARED 5.63939853058289E-001
FSTATISTIC 8.11293892270141E 000
EXPLAINED_DF 2
UNEXPLAINED_DF 9
PROB_VALUE 9.67794155011388E-003

TD_MULTIVAR_REGR Tertiary Result

Display the tertiary RETURNS TABLE with this function call:

EXECUTE FUNCTION
TD_EXTRACT_RESULTS( ART_SPEC( TABLE_NAME(MULTIVAR_RESULTS), LAYER(ARTFITRESIDUALS) ));
CID ROW_I X1 X2 ACTUAL_VALUE CALC_VALUE RESIDUAL
----------- ----------- ------------- ------------- ------------- ------------- -------------
33 0 3.90000E 004 3.00000E-002 1.20000E 005 1.21278E 005 -1.27881E 003
33 1 5.00000E 004 4.00000E-002 1.44000E 005 1.24894E 005 1.91058E 004
33 2 7.60000E 004 3.50000E-002 2.20000E 005 2.19313E 005 6.86047E 002
33 3 2.20000E 004 2.90000E-002 8.00000E 004 7.23453E 004 7.65466E 003
33 4 4.90000E 004 1.70000E-002 1.84000E 005 1.90811E 005 -6.81145E 003
33 5 1.12000E 005 3.90000E-002 3.20000E 005 3.17293E 005 2.70696E 003
33 6 2.90000E 004 3.40000E-002 6.60000E 004 7.87352E 004 -1.27352E 004
33 7 7.40000E 004 4.90000E-002 1.44000E 005 1.71221E 005 -2.72212E 004
33 8 7.20000E 004 4.50000E-002 1.95000E 005 1.77106E 005 1.78932E 004