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