Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running STEYX function


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
 
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service