Updated: 15 November 2011

Use 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 Ordinary Least Squares.

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:

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 and m_{0} 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.

SELECT [wctStatistics].[wct].[GROWTHMX_q] (

<@Matrix_RangeQuery, nvarchar(4000),>

,<@y_ColumnNumber, int,>

,<@new_x, nvarchar(4000),>

,<@Lconst, bit,>)

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

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

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

A logical value specifying whether to force the y-intercept value (*m*_{0}) equal to one. *@LConst *must be of the type **bit** or of a type that implicitly converts to **bit**.

float

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

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.

y x1 x2 x3

---------------------- ---------------------- ---------------------- ----------------------

64.744 -2 -8 0

115.589 8 3 -2

35.349 1 4 -7

77.215 -3 -1 1

28.725 -1 -3 10

54.679 5 -4 -7

27.382 -7 5 8

49.833 -8 -5 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 1^{st} 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.

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.

stat_name idx stat_val new_x

---------- ----------- ---------------------- -----------

m 0 51.5115125618439 1

m 1 1.03997667832838 -7

m 2 0.974365465751811 9

m 3 0.983415556512974 -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 wctMath.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.

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.

r y

---------------------- ----------------------

1 51.3318432932475

2 54.6916619602516

3 65.8564372652546

4 52.2365003841889

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

GROWTH

----------------------

0.0780313868197243