Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server moving CORREL function


MovingCORREL

Updated: 31 Oct 2012


Use the MovingCORRELfunction 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 value is calculated for each value from the first value in the window to the last value in the window. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[MovingCORREL](
  <@Y, float,>
 ,<@X, float,>
 ,<@Offset, int,>
 ,<@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.
@Offset
specifies the window size. @Offset is an expression of type int or of a type that can be implicitly converted to int.
@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 an entire set or partition of x- and y-values use the RunningCORREL function.
·         If @RowNum = 1 then MovingCORREL 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.
--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.MovingCORREL(amt_price,amt_sqft,5,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.995683821294555
          8       13588        1555      559149      0.996427794541849
          9       13891        1775      623900      0.997799841713257
         10       90957        1585      563947      0.997214166156517
         11       44167        1510      529806      0.996966940563728
         12       75533        1628      592533      0.994608422607885
         13       56812        1145      408634      0.994251992678361
         14       12897        1632      589522      0.993876395060519
         15       93826        1850      668852      0.996919052487866
         16       74510        1867      633400      0.984412158236675
         17       17262        1587      552178      0.982927101413885
         18       30929        1809      633141      0.987103002853674
         19       49030        1521      555713      0.944542077284074
         20       33431        1195      434542      0.983842801937084
 
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service