RunningSTEYX
Updated: 31 Oct 2012
Use the RunningSTEYX function to calculate the standard error of the predicted y-value for each x in a regression within a resultant table or partition, without the need for a self-join. The standard error value is calculated from the first row of the resultant table or partition through to the current row. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[RunningSTEYX](
<@Y, float,>
,<@X, float,>
,<@RowNum, int,>
,<@Id, tinyint,>)
GO
Arguments
@Y
the y-value passed into the function. @Y is an expression of type float or of a type that can be implicitly converted to float.
@X
the x-value passed into the function. @X is an expression of type float or of a type that can be implicitly converted to float.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningSTEYX calculation. @Id allows you to specify multiple RunningSTEYX calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
· If @Id is NULL then @Id = 0.
· @RowNum must be in ascending order.
· To calculate the standard error over a window of x- and y-values use the MovingSTEYX function.
· If @RowNum = 1 then RunningSTEYX is NULL.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem
Example
In this example we will calculate the standard error in the relationship between square footage and house prices. We will create a temporary table, #se, populate it with some data and then run the SELECT.
SET NOCOUNT ON
--Create the temporary table
CREATE TABLE #se(
rn int,
id_lot int,
amt_sqft int,
amt_price int,
PRIMARY KEY (rn)
)
--Put some date in the table
INSERT INTO #se VALUES (1,21783,1147,393918)
INSERT INTO #se VALUES (2,94729,1313,470479)
INSERT INTO #se VALUES (3,33028,1433,512474)
INSERT INTO #se VALUES (4,59446,1724,610477)
INSERT INTO #se VALUES (5,97646,1162,388196)
INSERT INTO #se VALUES (6,44823,1813,636916)
INSERT INTO #se VALUES (7,88397,1105,374348)
INSERT INTO #se VALUES (8,13588,1555,559149)
INSERT INTO #se VALUES (9,13891,1775,623900)
INSERT INTO #se VALUES (10,90957,1585,563947)
INSERT INTO #se VALUES (11,44167,1510,529806)
INSERT INTO #se VALUES (12,75533,1628,592533)
INSERT INTO #se VALUES (13,56812,1145,408634)
INSERT INTO #se VALUES (14,12897,1632,589522)
INSERT INTO #se VALUES (15,93826,1850,668852)
INSERT INTO #se VALUES (16,74510,1867,633400)
INSERT INTO #se VALUES (17,17262,1587,552178)
INSERT INTO #se VALUES (18,30929,1809,633141)
INSERT INTO #se VALUES (19,49030,1521,555713)
INSERT INTO #se VALUES (20,33431,1195,434542)
--Calculate STEYX
SELECT rn
,id_lot
,amt_sqft
,amt_price
,wct.RunningSTEYX(amt_price,amt_sqft,ROW_NUMBER() OVER (ORDER BY rn),NULL) as STEYX
FROM #se
--Clean up
DROP TABLE #se
This produces the following result.
rn id_lot amt_sqft amt_price STEYX
----------- ----------- ----------- ----------- ----------------------
1 21783 1147 393918 NULL
2 94729 1313 470479 NULL
3 33028 1433 512474 6299.74599385381
4 59446 1724 610477 8984.82175254739
5 97646 1162 388196 11419.1459357199
6 44823 1813 636916 11111.0625710519
7 88397 1105 374348 10191.0113301963
8 13588 1555 559149 10253.8481777362
9 13891 1775 623900 9920.08003340509
10 90957 1585 563947 9408.84004564312
11 44167 1510 529806 8890.84519292825
12 75533 1628 592533 9797.30564941949
13 56812 1145 408634 10061.4073819087
14 12897 1632 589522 10027.9059814703
15 93826 1850 668852 9773.6809830504
16 74510 1867 633400 12937.5033970538
17 17262 1587 552178 12693.634973068
18 30929 1809 633141 12449.8431391149
19 49030 1521 555713 12949.8208781755
20 33431 1195 434542 13064.740208963