For the purpose of this function, we would re-write the equation as
The value for slope, then, is stored in m0.
For purposes of multi-linear regression, where there are multiple columns of x-values (and still a single column of y-values), the formula for the solution is described by the following equation:
Where n is the number of x-columns.
The function expects the input to be in row-column, or spreadsheet, format, rather than in third-normal form. Thus, the input into the function requires the specification of the column names for the x-values and the y-values, and the specification of a 1-based index identifying the column reference for the y-values, and the specification of new x–values in the same column order as the x-values. The column specifications and the table or view that contains the data are passed into the function as strings.
Syntax
SELECT [wct].[TRENDMX] (
<@TableName, nvarchar(4000),>
,<@ColumnNames, nvarchar(4000),>
,<@GroupedColumnName, nvarchar(4000),>
,<@GroupedColumnValue, sql_variant,>
,<@Y_ColumnNumber, int,>
,<@New_x, nvarchar(max),>
,<@Lconst, bit,>)
Arguments
@TableName
the name, as text, of the table or view that contains the values in the array to be used in the LINEST calculation.
@ColumnNames
the name, as text, of the columns in the table or view specified by @TableName that contains the array values to be used in the LINEST calculation. Data returned from the @ColumnNames must be of the type float or of a type that implicitly converts to float.
@GroupedColumnName
the name, as text, of the column in the table or view specified by @TableName which will be used for grouping the results.
@GroupedColumnValue
the column value to do the grouping on.
@Y_ColumnNumber
the index into the array identifying the column containing the y-values. The index value must be between 1 and n, where n is the number of columns specified in @ColumnNames. @Y_ColumnNumber must be of the type int or of a type that implicitly converts to int.
@New_x
The new x-values for which you want TRENDMX to return a corresponding y-value. @new_x values must evaluate to a type of float or of a type that implicitly converts to float.
@LConst
A logical value specifying whether to force the y-intercept value (m0) equal to zero. @LConst must be of the type bit or of a type that implicitly converts to bit.
Return Type
float
Remarks
· If @Lconst is NULL than @Lconst is set to 'True'.
· If @Lconst is true than the number of rows must be greater than the number of columns.
· If @Lconst is false than the number of rows must be greater than or equal to the number of columns.
· For more complicated queries, you can try the TRENDMX_q function.
Examples
We create a table and put some data in it.
CREATE TABLE #xy (
y float,
x0 float,
x1 float,
x2 float
)
INSERT INTO #xy VALUES (18,2,-4,4)
INSERT INTO #xy VALUES (24,-9,2,9)
INSERT INTO #xy VALUES (16,5,-6,8)
INSERT INTO #xy VALUES (8.5,5,-1,4)
This is what the data look like.
We have 4 columns of x-data and one column of y-data, which is in the first column of the table #xy. To invoke the table-valued function TRENDMX, we enter the following SQL.
SELECT wct.TRENDMX('#xy','y,x0,x1,x2','',NULL,1,'-3,10,6','True') as TREND
Note that the table name and the column names are both bound by single-quotes, so that they are passed into the function as a string. Essentially the function is dynamically creating a SQL statement to SELECT the column names from the #xy table. This means that we can actually simplify the second parameter, since we are selecting all the columns in this particular table, by entering the following:
SELECT wct.TRENDMX('#xy','*','',NULL,1,'-3,10,6','True') as TREND
The third parameter is entered as blank (2 single quotes), since there is no column on which we want to group the results for input into the calculation and the fourth parameter is NULL, since there is no group column name. The fifth parameter, the y-column value, specifies that the 1st column in the resultant table, which becomes the input into the calculation, contains the y-values. The sixth parameter is the new x-vlaues, which is entered as a comma-separated string. The last parameter specified that we want to calculate the y-intercept.
The following result is produced.
By looking at the LINEST function, we can see how TRENDMX came up with this value. If we run the following SQL, we can mimic the TREND calculation:
SELECT *
,CASE
WHEN idx = 0 THEN 1
WHEN idx = 1 THEN -3
WHEN idx = 2 THEN 10
WHEN idx = 3 THEN 6
END as new_x
FROM wct.LINEST('#xy','*','',NULL,1,'True')
WHERE stat_name = 'm'
This returns the following result.
With a slight modification to the SQL, we can then reproduce the same result as that which was returned by TRENDMX.
SELECT SUM(stat_val * new_x) as TREND
FROM (
SELECT *
,CASE
WHEN idx = 0 THEN 1
WHEN idx = 1 THEN -3
WHEN idx = 2 THEN 10
WHEN idx = 3 THEN 6
END as new_x
FROM wct.LINEST('#xy','*','',NULL,1,'True')
WHERE stat_name = 'm'
) n
This produces the following result.
Which is the same result returned by TRENDMX.
In this example, we will add a new table, containing multiple new x-values, demonstrating how to calculate muliple y-values from multiple new x-values in a single SELECT.
CREATE TABLE #new_x (
r float,
x0 float,
x1 float,
x2 float
)
INSERT INTO #new_x VALUES (1,1,1,1)
INSERT INTO #new_x VALUES (2,2,2,-2)
INSERT INTO #new_x VALUES (3,3,-3,-3)
INSERT INTO #new_x VALUES (4,-4,-4,-4)
INSERT INTO #new_x VALUES (5,-5,5,-5)
We can then run the following statement and return a new y-value for each set of new x-values.
SELECT #new_x.r
,wct.TRENDMX('#xy','*','',NULL,1,cast(x0 as varchar)+','+cast(x1 as Varchar)+','+cast(x2 as varchar),'TRUE') as y
FROM #new_x
This produces the following result.
We also could have entered the following statement, using a SELECT statement to get the new x-values.
SELECT #new_x.r
,wct.TRENDMX('#xy','*','',NULL,1,'SELECT x0,x1,x2 FROM #new_x where r = ' + cast(r as varchar),'TRUE') as y
FROM #new_x
Finally, if we wanted to calculate the coefficients without a y-intercept, and supplying some different new x-values, we would modify function call to make the last parameter FALSE, as seen in the following example.
SELECT wct.TRENDMX('#xy','*','',NULL,1,'-9,5,-7','False') as TREND
This produces the following result.
See Also