15.00 - MLINREG - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

MLINREG

Purpose  

Returns a predicted value for an expression based on a least squares moving linear regression of the previous width-1 (based on sort_expression) column values.

Type

Teradata-specific function.

Syntax  

where:

 

Syntax element …

Specifies …

value_expression

a numeric literal or column expression for which a predicted value is to be computed.

The expression cannot contain any ordered analytical or aggregate functions.

The data type of the expression must be numeric or a data type that Teradata Database can successfully convert implicitly to numeric.

width

the number of rows to use to compute the function.

width-1 previous rows are used to compute the linear regression and the row value itself is used for calculating the predicted value.

The value is always a positive integer literal greater than 2.

The maximum is 4096.

sort_expression

a column expression that defines the independent variable for calculating the linear regression.

For example, MLINREG(Sales, 6, Fiscal_Year_Month ASC), where Sales is the value_expression, 6 is the width, and Fiscal_Year_Month ASC is the sort_expression.

The data type of the column reference must be numeric or a data type that Teradata Database can successfully convert implicitly to numeric.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Using ANSI-Compliant Window Functions Instead of MLINREG

Using ANSI-compliant window functions instead of MLINREG is strongly encouraged. MLINREG is a Teradata extension to the ANSI SQL:2011 standard, and is retained only for backward compatibility with existing applications.

Result Type and Attributes

The data type, format, and title for MLINREG are as follows:

Data Type: Same as operand x

  • If operand x is character, the format is the default format for FLOAT.
  • If operand x is numeric, date, or interval, the format is the same format as x.
  • For information on the default format of data types and an explanation of the formatting characters in the format, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Default Independent Variable

    MLINREG assumes that the independent variable is described by sort_expression.

    Computing MLINREG When Preceding Rows < width - 1

    When there are fewer than width-1 preceding rows, MLINREG computes the regression using all the preceding rows.

    MLINREG Report Structure

    All rows in the results table except the first two, which are always null, display the predicted value.

    Example  

    Consider the itemID, smonth, and sales columns from sales_table:

       SELECT itemID, smonth, sales 
       FROM fiscal_year_sales_table
       ORDER BY itemID, smonth;
     
       itemID  smonth    sales
       ------  --------  -----
       A              1    100
       A              2    110
       A              3    120
       A              4    130
       A              5    140
       A              6    150
       A              7    170
       A              8    190
       A              9    210
       A             10    230
       A             11    250
       A             12  ?
       B              1     20
       B              2     30
       ...

    Assume that the NULL in the sales column is because in this example the month of December (month 12) is a future date and the value is unknown.

    The following statement uses MLINREG to display the expected sales using past trends for each month for each product using the sales data for the previous six months.

       SELECT itemID, smonth, sales, MLINREG(sales,7,smonth)
       FROM fiscal_year_sales_table;
       GROUP BY itemID;
     
       itemID  smonth    sales  MLinReg(sales,7,smonth)
       ------  --------  -----  -----------------------
       A              1    100  ?
       A              2    110  ?
       A              3    120          120
       A              4    130          130
       A              5    140          140
       A              6    150          150
       A              7    170          160
       A              8    190          177
       A              9    210          198
       A             10    230          222
       A             11    250          247
       A             12  ?              270
       B              1     20  ?
       B              2     30  ?
       ...