GROWTHMX_q
Updated 2023-11-01 11:24:27.397000
Syntax
SELECT [westclintech].[wct].[GROWTHMX_q] (
<@Matrix_RangeQuery, nvarchar(4000),>
,<@y_ColumnNumber, int,>
,<@new_x, nvarchar(4000),>
,<@Lconst, bit,>)
Description
Use the scalar function GROWTHMX_q to calculate values along an exponential trend. GROWTHMX_q is designed specifically for multiple x-values. If there is only one x-value for each y-value, consider using the aggregate function GROWTH.
GROWTHMX_q calculates the Ordinary Least Squares (OLS) solution for the given x- and y-values. The OLS solution calculates a regression analysis where an exponential curve is fitted. The known y-values are treated as dependent variables and the known x-values are treated as independent variables. For more information on the calculation of these values, see LOGEST_q.
GROWTHMX_q computes a y-value with new x-values supplied as input to the function, using the coefficients calculated in the OLS solution.
In the case where we have one column of x-values and a column of y-values, the OLS solution is:
y=bm^{x}
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:
y=m_n^{x_n}*m_{n-1}^{x_{n-1}}*m_{n-2}^{x_{n-2}} ... m_1^{x_1}*m_0
Where n is the number of x-columns and m0 is b.
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. Unlike a spreadsheet, however, there is no requirement that the columns containing the x-values be contiguous. The column specifications and the table or view that contains the data are passed into the function as strings.
Arguments
@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.
@Matrix_RangeQuery
The SELECT statement, as a string, which, when executed, creates the resultant table of x- and y-values which will be used in the calculation. @MatrixRangeQuery values must evaluate to a type of float or of a type that implicitly converts to float.
@new_x
The new x-values for which you want GROWTHMX 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 one. @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 simpler queries, you can try the GROWTHMX function.
Examples
We create a table and put some data in it.
CREATE TABLE #xy
(
y float,
x1 float,
x2 float,
x3 float
);
INSERT INTO #xy
VALUES
(64.744, -2, -8, 0);
INSERT INTO #xy
VALUES
(115.589, 8, 3, -2);
INSERT INTO #xy
VALUES
(35.349, 1, 4, -7);
INSERT INTO #xy
VALUES
(77.215, -3, -1, 1);
INSERT INTO #xy
VALUES
(28.725, -1, -3, 10);
INSERT INTO #xy
VALUES
(54.679, 5, -4, -7);
INSERT INTO #xy
VALUES
(27.382, -7, 5, 8);
INSERT INTO #xy
VALUES
(49.833, -8, -5, 2);
This is what the data look like.
{"columns":[{"field":"y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"x1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"x2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"x3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"y":"64.744","x1":"-2","x2":"-8","x3":"0"},{"y":"115.589","x1":"8","x2":"3","x3":"-2"},{"y":"35.349","x1":"1","x2":"4","x3":"-7"},{"y":"77.215","x1":"-3","x2":"-1","x3":"1"},{"y":"28.725","x1":"-1","x2":"-3","x3":"10"},{"y":"54.679","x1":"5","x2":"-4","x3":"-7"},{"y":"27.382","x1":"-7","x2":"5","x3":"8"},{"y":"49.833","x1":"-8","x2":"-5","x3":"2"}]}
We have 3 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 GROWTHMX_q, we enter the following SQL.
SELECT wct.GROWTHMX_q('SELECT y,x1,x2,x3 from #xy', 1, '-7,9,-1', 'True') as GROWTH;
The function is dynamically creating a SQL statement to execute @MatrixRangeQuery . This means that we can actually simplify this parameter, since we are selecting all the columns in this particular table, by entering the following:
SELECT wct.GROWTHMX_q('SELECT * from #xy', 1, '-7,9,-1', 'True') as GROWTH;
The second 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 third 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.
{"columns":[{"field":"GROWTH","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"GROWTH":"31.5136692835841"}]}
By looking at the LOGEST function, we can see how GROWTHMX_q came up with this value. If we run the following SQL, we can mimic the GROWTH calculation:
SELECT *,
CASE
WHEN idx = 0 THEN
1
WHEN idx = 1 THEN
-7
WHEN idx = 2 THEN
9
WHEN idx = 3 THEN
-1
END as new_x
FROM wct.LOGEST('#xy', '*', '', NULL, 1, 'True')
WHERE stat_name = 'm';
This returns the following result.
{"columns":[{"field":"stat_name"},{"field":"idx","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"stat_val","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"new_x","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"stat_name":"m","idx":"0","stat_val":"51.5115125618439","new_x":"1"},{"stat_name":"m","idx":"1","stat_val":"1.03997667832838","new_x":"-7"},{"stat_name":"m","idx":"2","stat_val":"0.974365465751811","new_x":"9"},{"stat_name":"m","idx":"3","stat_val":"0.983415556512974","new_x":"-1"}]}
With a slight modification to the SQL and using the XLeratorDB PRODUCT function, we can now reproduce the same result as GROWTHMX_Q.
SELECT wct.PRODUCT(POWER(stat_val, new_x)) as GROWTH
FROM
(
SELECT *,
CASE
WHEN idx = 0 THEN
1
WHEN idx = 1 THEN
-7
WHEN idx = 2 THEN
9
WHEN idx = 3 THEN
-1
END as new_x
FROM wct.LOGEST('#xy', '*', '', NULL, 1, 'True')
WHERE stat_name = 'm'
) n;
This produces the following result.
{"columns":[{"field":"GROWTH","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"GROWTH":"31.5136692835842"}]}
Which is the same result returned by GROWTHMX_q.
In this example, we will add a new table, containing multiple new x-values, demonstrating how to calculate multiple 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.GROWTHMX_q(
'SELECT * FROM #xy',
1,
cast(x0 as varchar) + ',' + cast(x1 as Varchar) + ',' +
cast(x2 as varchar),
'TRUE'
) as y
FROM #new_x;
This produces the following result.
{"columns":[{"field":"r","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"y","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"r":"1","y":"51.3318432932475"},{"r":"2","y":"54.6916619602516"},{"r":"3","y":"65.8564372652546"},{"r":"4","y":"52.2365003841889"},{"r":"5","y":"40.4306148196581"}]}
We also could have entered the following statement, using a SELECT statement to get the new x-values.
SELECT #new_x.r,
wct.GROWTHMX_q(
'SELECT * FROM #xy',
1,
'SELECT x0, x1,x2 from #new_x where r = ' + cast(#new_x.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.GROWTHMX_q('SELECT * from #xy', 1, '10,10,7', 'False') as GROWTH;
This produces the following result.
{"columns":[{"field":"GROWTH","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"GROWTH":"0.0780313868197243"}]}