Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running CORREL function


RunningCORREL

Updated: 31 Oct 2012


Use the RunningCORREL function to calculate the Pearson product moment correlation coefficient through data points in y- and x-values within a resultant table or partition, without the need for a self-join. The correlation coefficient 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].[RunningCORREL](
  <@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 RunningCORREL calculation. @Id allows you to specify multiple RunningCORREL 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 correlation coefficient over a window of x- and y-values use the MovingCORREL function.
·         If @RowNum = 1 then RunningCORREL is NULL.
·         To calculate a single correlation coefficient for a set, use the CORREL 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 correlation coefficient 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 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                 CORREL
----------- ----------- ----------- ----------- ----------------------
          1       21783        1147      393918                   NULL
          2       94729        1313      470479                      1
          3       33028        1433      512474      0.997250449708387
          4       59446        1724      610477      0.996692220392964
          5       97646        1162      388196      0.994211415947064
          6       44823        1813      636916      0.995557722348421
          7       88397        1105      374348      0.996267358109546
          8       13588        1555      559149      0.995765872256083
          9       13891        1775      623900      0.996158306680619
         10       90957        1585      563947      0.996171484656381
         11       44167        1510      529806      0.996164248562469
         12       75533        1628      592533       0.99511276733915
         13       56812        1145      408634      0.994933340316737
         14       12897        1632      589522      0.994769382190739
         15       93826        1850      668852      0.995452352061868
         16       74510        1867      633400      0.992009513311456
         17       17262        1587      552178      0.991773410568987
         18       30929        1809      633141      0.992042626769922
         19       49030        1521      555713      0.990858779380213
         20       33431        1195      434542      0.990792688204407
 
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service