STEYX

Updated 2023-11-06 19:41:08.020000

Syntax

SELECT [westclintech].[wct].[STEYX] (
  <@known_y, float,> 
 ,<@known_x, float,>)

Description

Use the aggregate function STEYX to return the standard error of the predicted y-value for each x in the regression. The equation for STEYX is:

\sqrt {\frac{1}{(N-2)} \left [ \sum_{j=0}^{N-1} (y_j - \bar{y})^2 - \frac{\left[\sum_{j=0}^{N-1} ( x_j - \bar{x}) (y_j - \bar{y})\right ]^2}{\sum_{j=0}^{N-1} (x_j - \bar{x})^2}\right]}

Arguments

@known_y

the y-values to be used in the STEYX calculation. @Known_y is an expression of type float or of a type that can be implicitly converted to float.

@known_x

the x-values to be used in the STEYX calculation. @Known_x is an expression of type float or of a type that can be implicitly converted to float.

Return Type

float

Remarks

STEYX is an AGGREGATE function and follows the same conventions as all other AGGREGATE functions in SQL Server.

Examples

In this example, we calculate the standard error for a single set of x- and y-values

SELECT wct.STEYX(y, x) as STEYX

FROM

(

    SELECT 0.75,

           1

    UNION ALL

    SELECT 2.5,

           2

    UNION ALL

    SELECT 6.75,

           3

    UNION ALL

    SELECT 10,

           4

) n(x, y);

This produces the following result

{"columns":[{"field":"STEYX","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"STEYX":"0.2368246584594"}]}

In this example, we will populate some temporary table with some historical financial information and then calculate the standard error. First, create the table and put some data in it:

CREATE TABLE #c

(

    SYM NVARCHAR(5),

    YE BIGINT,

    REV FLOAT,

    GPROF FLOAT,

    OPINC FLOAT,

    NETINC FLOAT

);

INSERT INTO #c

VALUES

('YHOO', 2009, 6460.32, 3588.57, 386.69, 597.99);

INSERT INTO #c

VALUES

('YHOO', 2008, 72.5, 4185.14, 12.96, 418.92);

INSERT INTO #c

VALUES

('YHOO', 2007, 6969.27, 4130.52, 695.41, 639.16);

INSERT INTO #c

VALUES

('YHOO', 2006, 6425.68, 3749.96, 940.97, 751.39);

INSERT INTO #c

VALUES

('YHOO', 2005, 5257.67, 3161.47, 1107.73, 1896.23);

INSERT INTO #c

VALUES

('GOOG', 2009, 23650.56, 14806.45, 8312.19, 6520.45);

INSERT INTO #c

VALUES

('GOOG', 2008, 21795.55, 13174.04, 5537.21, 4226.86);

INSERT INTO #c

VALUES

('GOOG', 2007, 16593.99, 9944.9, 54.44, 4203.72);

INSERT INTO #c

VALUES

('GOOG', 2006, 10604.92, 6379.89, 3550, 3077.45);

INSERT INTO #c

VALUES

('GOOG', 2005, 6138.56, 3561.47, 2017.28, 1465.4);

INSERT INTO #c

VALUES

('MSFT', 2010, 62484, 509, 24167, 18760);

INSERT INTO #c

VALUES

('MSFT', 2009, 58437, 46282, 21225, 14569);

INSERT INTO #c

VALUES

('MSFT', 2008, 60420, 48822, 22271, 17681);

INSERT INTO #c

VALUES

('MSFT', 2007, 51122, 40429, 18438, 14065);

INSERT INTO #c

VALUES

('MSFT', 2006, 44282, 36632, 16064, 12599);

INSERT INTO #c

VALUES

('ORCL', 2010, 26820, 21056, 9062, 6135);

INSERT INTO #c

VALUES

('ORCL', 2009, 23252, 18458, 8321, 5593);

INSERT INTO #c

VALUES

('ORCL', 2008, 22430, 17449, 7844, 5521);

INSERT INTO #c

VALUES

('ORCL', 2007, 17996, 13805, 5974, 4274);

INSERT INTO #c

VALUES

('ORCL', 2006, 14380, 11145, 4736, 3381);

INSERT INTO #c

VALUES

('SAP', 2009, 10672, 6980, 2588, 1748);

INSERT INTO #c

VALUES

('SAP', 2008, 11575, 7370, 2701, 1847);

INSERT INTO #c

VALUES

('SAP', 2007, 10256, 6631, 2698, 1906);

INSERT INTO #c

VALUES

('SAP', 2006, 9393, 6064, 2578, 1871);

INSERT INTO #c

VALUES

('SAP', 2005, 8509, 5460, 2337, 1496);

Now, calculate the standard error of the revenue (REV) against the year (YE) for each company (SYM).

SELECT #c.SYM
,wct.STEYX(REV,YE) as STEYX
FROM #c
GROUP BY SYM;

This produces the following result.

{"columns":[{"field":"SYM"},{"field":"STEYX","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SYM":"GOOG","STEYX":"1284.78807451268"},{"SYM":"MSFT","STEYX":"3433.65363716261"},{"SYM":"ORCL","STEYX":"1002.72854418997"},{"SYM":"SAP","STEYX":"663.59214381526"},{"SYM":"YHOO","STEYX":"3205.4286831582"}]}

In this example, we will calculate the standard error of the operating income (OPINC) against the revenue (REV).

SELECT #c.SYM,

       wct.STEYX(OPINC, REV) as STEYX

FROM #c

GROUP BY SYM;

This produces the following result.

{"columns":[{"field":"SYM"},{"field":"STEYX","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SYM":"GOOG","STEYX":"2789.71579712227"},{"SYM":"MSFT","STEYX":"580.20345862298"},{"SYM":"ORCL","STEYX":"253.042178642769"},{"SYM":"SAP","STEYX":"91.5324292146264"},{"SYM":"YHOO","STEYX":"372.475117541028"}]}