Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running COVAR function


RunningCOVAR

Updated: 31 Oct 2012


Use the RunningCOVAR function to calculate the covariance through data points in y- and x-values within a resultant table or partition, without the need for a self-join. The covariance 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].[RunningCOVAR](
  <@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 RunningCOVAR calculation. @Id allows you to specify multiple RunningCOVAR 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 covariance over a window of x- and y-values use the MovingCOVAR function.
·         If @RowNum = 1 then RunningCOVAR is NULL.
·         To calculate a single covariance value for an entire set of data use the COVAR function.
·         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 covariance 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 CORREL
SELECT rn
,id_lot
,amt_sqft
,amt_price
,wct.RunningCORREL(amt_price,amt_sqft,ROW_NUMBER() OVER (ORDER BY rn),NULL) as CORREL
FROM #se
--Clean up
DROP TABLE #se
 
This produces the following result.
         rn      id_lot    amt_sqft   amt_price                  COVAR
----------- ----------- ----------- ----------- ----------------------
          1       21783        1147      393918                      0
          2       94729        1313      470479              3177281.5
          3       33028        1433      512474       5739504.66666667
          4       59446        1724      610477             16416758.5
          5       97646        1162      388196            17344331.96
          6       44823        1813      636916       24728367.1666667
          7       88397        1105      374348       26310103.3673469
          8       13588        1555      559149          24419454.6875
          9       13891        1775      623900        26461392.382716
         10       90957        1585      563947            24510824.82
         11       44167        1510      529806       22348813.4132231
         12       75533        1628      592533           21449604.125
         13       56812        1145      408634       22474025.7751479
         14       12897        1632      589522       21778746.0255102
         15       93826        1850      668852       23925253.9466667
         16       74510        1867      633400        24742460.515625
         17       17262        1587      552178       23356114.4290657
         18       30929        1809      633141       23537179.2222222
         19       49030        1521      555713       22287858.2216066
         20       33431        1195      434542             22907483.2
 
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service